Databases

Reading a PostgreSQL query plan without panic

A constellation of colored nodes joined by thin lines

A slow query is a question, and EXPLAIN ANALYZE is the answer written in a slightly unfriendly dialect. Once you learn to read it from the inside out, it becomes the most useful tool in your database kit.

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.region = 'north'
  AND o.created_at > now() - interval '30 days';

Start at the deepest node

Plans are trees. The most indented line runs first and feeds its parent. For each node, compare the estimated rows with the actual rows. A difference of ten times or more usually means stale statistics or a predicate the planner cannot see through.

The three numbers that matter

  1. actual time: the second value is when the node finished, in milliseconds.
  2. rows versus loops: multiply them to get the real row count.
  3. Buffers: shared hits are cheap, reads went to disk.
A constellation of colored nodes joined by thin lines
A plan is a tree: read it from the leaves upward.

Most slow queries are not missing an index. They are missing an accurate estimate.

When estimates are wrong, run ANALYZE on the tables involved, consider extended statistics for correlated columns, and only then reach for a new index.