PostgreSQL EXPLAIN: Reading a Query Plan Like Someone Who Has Fixed One

Read an EXPLAIN plan from the inside out, and look first at the gap between estimated and actual row counts. That one habit finds the cause of most slow queries, because when the planner’s estimate is wrong by an order of magnitude, every decision it made downstream was made on bad information.

EXPLAIN output looks like a wall of numbers and node names, and the usual reaction is to scan for the word Seq Scan and add an index. Sometimes that works. More often the sequential scan was the right choice and the real problem is three lines further in, where the planner expected forty rows and got four hundred thousand.

Table of contents

• EXPLAIN versus EXPLAIN ANALYZE
• Read it inside out
• The estimate-versus-actual gap is the main signal
• The loops trap
• What the node types actually mean
• What to change once you have found it
• How this fits the rest of the stack
• FAQ

EXPLAIN versus EXPLAIN ANALYZE

EXPLAIN shows the plan the planner chose, with estimates. It does not run the query. It is instant and safe on anything.

EXPLAIN ANALYZE actually executes the query and adds real timings and real row counts alongside the estimates. This is the one you want, because the estimates alone cannot tell you where the planner was wrong.

One warning that matters: EXPLAIN ANALYZE runs the query, including writes. Running it on an UPDATE or DELETE performs the update or delete. Wrap it in a transaction and roll back if you need to analyse a write:

BEGIN;
EXPLAIN (ANALYZE, BUFFERS) UPDATE orders SET status = 'shipped' WHERE id = 42;
ROLLBACK;

Always add BUFFERS. It reports how many blocks were read from shared cache versus from disk, which is the honest measure of how much work the query did. Timings vary with cache state and machine load; buffer counts do not.

The full form worth using habitually:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS) SELECT ...;

Read it inside out

A plan is a tree printed with indentation. The deepest, most-indented nodes execute first and pass rows up to their parents. Reading top to bottom, the way you read everything else, gets the execution order exactly backwards.

Sort  (cost=717.34..717.59 rows=101 width=488) (actual time=7.761..7.774 rows=100 loops=1)
  Sort Key: t1.fivethous
  Sort Method: quicksort  Memory: 77kB
  ->  Hash Join  (cost=230.47..713.98 rows=101 width=488) (actual time=0.711..7.427 rows=100 loops=1)
        Hash Cond: (t2.unique2 = t1.unique2)
        ->  Seq Scan on tenk2 t2  (cost=0.00..445.00 rows=10000 width=244)
        ->  Hash  (cost=229.20..229.20 rows=101 width=244)
              ->  Bitmap Heap Scan on tenk1 t1  (cost=5.07..229.20 rows=101 width=244)
                    Recheck Cond: (unique1 < 100)
                    ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 width=0)
                          Index Cond: (unique1 < 100)

So: the index scan runs first, finds matching row locations, the heap scan fetches those rows, they are hashed, the other table is scanned and probed against the hash, and finally the result is sorted.

The numbers on each line mean:

• cost=A..B — estimated startup cost and estimated total cost, in arbitrary planner units. Useful for comparing nodes, meaningless as an absolute.
• rows=N — estimated rows this node will emit.
• width=N — estimated average row size in bytes.
• actual time=A..B — real milliseconds to first row and to last row, per loop.
• rows=N in the actual section — real rows emitted, per loop.
• loops=N — how many times this node ran.

Costs are not milliseconds and cannot be converted into them. They exist so the planner can compare alternatives. Ignore them once you have ANALYZE timings.

The estimate-versus-actual gap is the main signal

This is the single most useful thing in the output and the thing most guides bury.

Compare the estimated rows to the actual rows on each node. When they are close, the planner had good information. When they differ by a factor of a hundred or more, the planner chose its strategy based on a false premise, and everything above that node in the tree is likely to be wrong as a consequence.

A planner expecting 10 rows will happily choose a nested loop, because looping ten times is cheap. If the reality is 100,000 rows, that nested loop runs 100,000 times and the query takes minutes. The nested loop is not the bug; the estimate is.

Common reasons the estimate is wrong:

• Statistics are stale. ANALYZE has not run since the data changed substantially.
• The default statistics target is too low for a column with an unusual distribution.
• Correlated columns. The planner assumes conditions are independent, so filtering on city and postcode together produces a wildly low estimate because it multiplies two selectivities that are in fact the same condition.
• A function or expression in the WHERE clause that the planner cannot see through.
• Recently bulk-loaded data that autovacuum has not caught up with.

The first fix to try is always the cheapest: run ANALYZE on the table and look again.

The loops trap

A node showing loops greater than 1 reports its actual time and rows per loop, not in total.

->  Index Scan using orders_customer_id_idx on orders
      (cost=0.29..8.31 rows=1 width=64)
      (actual time=0.021..0.023 rows=1 loops=50000)

That reads as 0.023 milliseconds, which looks excellent. It ran fifty thousand times, so the real contribution is roughly 1.15 seconds, and it is very likely the slowest thing in the query.

This catches people constantly, because the small number is right there and the multiplication is not. Whenever you see a high loop count, multiply before judging.

A high loop count is also the signature of a nested loop join driven by a bad estimate, which brings you back to the previous section. Fixing the estimate usually replaces the nested loop with a hash join and the fifty thousand index lookups become one scan.

What the node types actually mean

• Seq Scan — read the whole table. Correct and fastest when you need a large fraction of the rows. A problem only when you needed a few of them.
• Index Scan — walk the index and fetch matching rows from the table. Good for small result sets, worse than a sequential scan once you are fetching a large share, because each fetch is a random read.
• Index Only Scan — the index contained everything needed, so the table was never touched. The best case, and the reason covering indexes exist.
• Bitmap Index Scan then Bitmap Heap Scan — collect matching locations first, then fetch them in physical order. Postgres chooses this in the middle ground where there are too many rows for an index scan and too few for a sequential one.
• Nested Loop — for each row on one side, look up matches on the other. Excellent for small inputs, disastrous for large ones.
• Hash Join — build a hash of the smaller side, probe with the larger. The usual choice for joining substantial sets.
• Merge Join — both inputs sorted, walked in step. Efficient when the sort is free because an index provided it.
• Sort — watch the Sort Method line. quicksort with a memory figure is in-RAM; external merge with a disk figure means it spilled, and raising work_mem may help.

A Seq Scan is not a defect. On a table of a few thousand rows it is faster than any index, and the planner knows that. Adding an index to make the plan look tidier and then finding the query unchanged is a common way to spend an afternoon.

What to change once you have found it

The plan tells you where the time went. Turning that into a fix comes down to a short list.

Run ANALYZE. Free, instant, and fixes the largest category of bad plans.

Raise the statistics target on a column with a skewed distribution, then re-analyse:

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

Create extended statistics for correlated columns, which is the specific answer to the city-and-postcode problem:

CREATE STATISTICS orders_geo (dependencies) ON city, postcode FROM orders;
ANALYZE orders;

Add an index that matches the filter, ordering the columns by the equality conditions first and the range condition last. Consider INCLUDE columns to enable an index-only scan.

Rewrite the query. A function applied to a column in a WHERE clause prevents index use unless there is a matching expression index — WHERE lower(email) = … needs an index on lower(email). A correlated subquery in a SELECT list runs once per row and is usually better as a join.

Raise work_mem for a session doing a large sort or hash that is spilling to disk. Set it per session rather than globally, because it is allocated per operation and a high global value on a busy server is a memory-exhaustion risk.

And check that the query needs all the rows it asks for. The fastest optimisation is frequently a LIMIT or a narrower column list that somebody never added.

How this fits the rest of the stack

Reading a plan is a small skill with a large return: inside out, watch the estimate-versus-actual gap, multiply by loops, and only then look at the node types. Most of what it finds is fixed by ANALYZE, better statistics, or one index that matches how the query actually filters. Managed Postgres on RunxBuild comes with backups, connection limits, and private networking so the instance itself is not the thing you are tuning at midnight, and the RunxBuild hosting calculator shows the database next to the service that queries it.

Useful related references:

• PostgreSQL 18: How to Upgrade Without Downtime
• SQLite vs PostgreSQL: Which One Your Project Actually Needs
• PostgreSQL Show Tables: \dt, pg_catalog, and information_schema
• Databases on RunxBuild

FAQ

What is the difference between EXPLAIN and EXPLAIN ANALYZE?

EXPLAIN shows the planner’s chosen plan with estimates and does not run the query. EXPLAIN ANALYZE executes it and adds real timings and row counts alongside the estimates, which is what lets you see where the planner was wrong. Be careful with writes — EXPLAIN ANALYZE on an UPDATE performs it, so wrap it in a transaction and roll back.

How do I read a PostgreSQL query plan?

Inside out. The most indented nodes execute first and feed their parents. Compare estimated rows to actual rows on each node — a large gap means the planner made its choices on bad information. Multiply any node’s time by its loops count before judging it, and add BUFFERS for a cache-independent measure of the work done.

Why is PostgreSQL not using my index?

Usually because a sequential scan is genuinely cheaper for the number of rows involved, which is often the right call on smaller tables. Other causes are a function applied to the column in the WHERE clause, a type mismatch preventing the index from matching, or stale statistics leaving the planner with a wrong row estimate.

What does loops mean in EXPLAIN ANALYZE output?

How many times that node executed. The actual time and row counts shown are per loop, not totals, so a node reporting 0.02ms with loops=50000 contributed about a second. High loop counts usually indicate a nested loop join chosen on a bad row estimate, and fixing the estimate replaces it with a hash join.

What does Rows Removed by Filter tell me?

How many rows the node read and then discarded. A large number means work was wasted fetching rows that did not qualify, which usually points to a missing or poorly ordered index — the filter is being applied after the fetch rather than being used to avoid it.