SQL guides

How to read a PostgreSQL EXPLAIN plan

Read PostgreSQL EXPLAIN and EXPLAIN ANALYZE output line by line, from cost and rows to scan and join types, with a worked example of a slow query and its fix.

Published

Cover reading How to read a PostgreSQL EXPLAIN plan, with a line drawing of a query plan drawn as a tree of nodes

EXPLAIN shows the plan PostgreSQL chose for a query: a tree of steps, read from the most indented line outwards, each with an estimated cost and row count. EXPLAIN ANALYZE runs the query and adds what actually happened, so you can see where the time went and where the planner guessed wrong. A slow query usually gives itself away in one of two places: a node that reads far more rows than it returns, or a node whose estimated rows are far from its actual rows.

We make dbPlus, a Mac database client mentioned near the end; the rest of this guide applies to plain psql or any other client. Option names and output below follow PostgreSQL 18, the current release in October 2026.

EXPLAIN, EXPLAIN ANALYZE and the options worth knowing

Plain EXPLAIN plans the query without running it, so it is safe to use even on an UPDATE or DELETE. EXPLAIN ANALYZE executes the statement for real. For a SELECT, the result rows are thrown away, but an UPDATE, DELETE or INSERT changes data as usual. The PostgreSQL documentation recommends wrapping such statements in a transaction:

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

The rollback discards the changes, but the statement still does its work and holds its locks while it runs, so be careful on a busy production table. Our guide to editing production data safely covers the other precautions.

Options go in parentheses:

  • ANALYZE runs the query and reports actual times and row counts.
  • BUFFERS reports how many 8 kB pages each node found in shared memory or had to read. Since PostgreSQL 18 it is included automatically with ANALYZE; on older versions, ask for it.
  • VERBOSE adds output columns and schema-qualified names.
  • SETTINGS lists planner settings that differ from the defaults.
  • FORMAT JSON produces output for tools; TEXT is the default.
  • TIMING OFF keeps row counts but skips per-node timing, which lowers the overhead of reading the clock on queries that process many rows.
  • SERIALIZE, WAL and MEMORY measure output conversion, WAL generated and planner memory.
  • GENERIC_PLAN plans a query that contains $1 placeholders, without values.

PostgreSQL 19, at the release-candidate stage in October 2026, adds an IO option for asynchronous I/O.

How to read one line

Take a plain EXPLAIN of a query on a hypothetical orders table with 1,000,000 rows stored in 8,407 pages:

Seq Scan on orders  (cost=0.00..20907.00 rows=21 width=35)
  Filter: (customer_id = 4210)

cost=0.00..20907.00 is two numbers. The first is the startup cost, spent before the node can return its first row. The second is the total cost to return all rows. Costs are in arbitrary planner units, where reading one page sequentially costs 1.0 by default (seq_page_cost). This total is 8,407 pages at 1.0, plus 1,000,000 rows at cpu_tuple_cost 0.01, plus 1,000,000 filter checks at cpu_operator_cost 0.0025. Costs are for comparing plans, not for predicting milliseconds.

rows=21 is the estimated number of rows the node returns after its filter, not the number it reads. width=35 is the estimated average row size in bytes. A parent node's cost includes the cost of its children.

EXPLAIN ANALYZE adds a second parenthesis:

(actual time=2.802..19.781 rows=7.33 loops=3)

actual time is in milliseconds: time to the first row, then time to the last row. rows is the actual row count and loops is how many times the node ran. Both time and rows are averages per loop, so multiply by loops for totals: this node returned about 22 rows in total. PostgreSQL 18 prints actual rows with two decimals for exactly this reason. The plan ends with Planning Time and Execution Time.

In a line such as Buffers: shared hit=23 read=3, hit counts pages found in PostgreSQL's shared memory and read counts pages fetched from the operating system or disk. temp read and temp written mean a sort or hash spilled to disk.

Scan types

A scan node is how PostgreSQL reads a table.

  • Seq Scan reads every page of the table. It is the right choice for small tables and for queries that need a large share of the rows. On a big table that returns a few rows, it usually means a missing or unusable index.
  • Index Scan walks an index to find matching rows, then fetches each row from the table. Index Scan Backward reads the index in reverse, which serves ORDER BY ... DESC.
  • Index Only Scan answers the query from the index alone, when every column it needs is in the index. Heap Fetches counts rows it still had to check in the table because the visibility map was out of date. A high number there means the table needs a VACUUM.
  • Bitmap Index Scan plus Bitmap Heap Scan is a middle path for medium result sets. The index scan builds a bitmap of matching pages, and the heap scan reads those pages in physical order. Recheck Cond is the condition applied again to rows on those pages. Heap Blocks: lossy=... means the bitmap did not fit in work_mem and stored whole pages instead of single rows.
  • Parallel Seq Scan under a Gather or Gather Merge node splits the table between worker processes. loops then counts the workers plus the leader process.

Here is an index-only scan from the same example table, with nothing fetched from the table itself:

Index Only Scan using orders_customer_created_idx on orders  (cost=0.42..4.79 rows=21 width=8) (actual time=0.018..0.019 rows=22.00 loops=1)
  Index Cond: (customer_id = 4210)
  Heap Fetches: 0
  Index Searches: 1
  Buffers: shared hit=7

Index Searches, new in PostgreSQL 18, counts how many times the index was descended, summed over all loops.

Join types

  • Nested Loop runs its inner side once for every row from its outer side. It is fast when the outer side is small and the inner side can use an index. Its inner node shows loops equal to the outer row count, which makes a bad nested loop easy to spot.
  • Hash Join reads one input into an in-memory hash table (the Hash node underneath) and probes it with each row of the other input. It suits large, unsorted inputs. Batches above 1 on the Hash node means the table did not fit in work_mem and spilled to disk.
  • Merge Join walks two inputs that are both sorted on the join key, from an index or an explicit Sort. It suits large inputs that are already in order.

Memoize above an inner index scan caches inner results for repeated outer values, so a nested loop with many duplicate keys costs less.

Plan nodes at a glance

Node What it does Worth a closer look when
Seq Scan Reads every page of the table A big table returns only a few rows
Index Scan Walks an index, then fetches each matching row It sits under a Nested Loop with a high loops count
Index Only Scan Answers from the index alone Heap Fetches is high, so the table needs a VACUUM
Bitmap Heap Scan Reads the pages a bitmap index scan found, in physical order Heap Blocks: lossy shows the bitmap did not fit in work_mem
Nested Loop Runs the inner side once for every outer row The outer side is large or the inner side has no index
Hash Join Builds a hash table from one input and probes it with the other Batches above 1 on the Hash node means it spilled to disk
Merge Join Walks two inputs sorted on the join key A large explicit Sort feeds it

Estimated rows versus actual rows

The planner chooses scan and join types from its row estimates, and those come from statistics that ANALYZE collects. When estimates and reality differ by ten times or more, look there first. This excerpt comes from the example tables after a bulk UPDATE set 50,000 orders to a new status, before statistics were refreshed:

Nested Loop  (cost=1000.29..15299.08 rows=1 width=9) (actual time=26.655..87.910 rows=50000.00 loops=1)
  ->  Gather  (cost=1000.00..15290.78 rows=1 width=14) (actual time=25.782..27.610 rows=50000.00 loops=1)
        ->  Parallel Seq Scan on orders o  (cost=0.00..14290.68 rows=1 width=14) (actual time=21.330..23.021 rows=16666.67 loops=3)
              Filter: (status = 'refunded'::text)
  ->  Index Scan using customers_pkey on customers c  (cost=0.29..8.31 rows=1 width=11) (actual time=0.001..0.001 rows=1.00 loops=50000)
        Index Cond: (id = o.customer_id)
        Index Searches: 50000

The planner expected one refunded order and chose a nested loop. It got 50,000, so the inner index scan ran 50,000 times. After ANALYZE orders; the same query switched to a hash join, and execution time in this test fell from about 100 ms to about 35 ms. On larger tables the gap grows.

Autovacuum runs ANALYZE in the background, but after a bulk load or a large update, run it yourself. If one column has an uneven distribution, collect more detail with ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000; followed by ANALYZE. When two columns are correlated, such as city and postcode, the planner multiplies their selectivities and underestimates; CREATE STATISTICS fixes that.

Worked example: one customer's latest orders

This example uses test data: orders has 1,000,000 rows and no index on customer_id yet. The application asks for a customer's 20 most recent orders:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total, created_at
FROM orders
WHERE customer_id = 4210
ORDER BY created_at DESC
LIMIT 20;

 Limit  (cost=14615.50..14617.60 rows=18 width=22) (actual time=22.900..24.133 rows=20.00 loops=1)
   Buffers: shared hit=8521
   ->  Gather Merge  (cost=14615.50..14617.60 rows=18 width=22) (actual time=22.898..24.130 rows=20.00 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=8521
         ->  Sort  (cost=13615.48..13615.50 rows=9 width=22) (actual time=19.860..19.861 rows=7.00 loops=3)
               Sort Key: created_at DESC
               Sort Method: quicksort  Memory: 25kB
               Buffers: shared hit=8521
               Worker 0:  Sort Method: quicksort  Memory: 25kB
               Worker 1:  Sort Method: quicksort  Memory: 25kB
               ->  Parallel Seq Scan on orders  (cost=0.00..13615.33 rows=9 width=22) (actual time=2.802..19.781 rows=7.33 loops=3)
                     Filter: (customer_id = 4210)
                     Rows Removed by Filter: 333326
                     Buffers: shared hit=8407
 Planning:
   Buffers: shared hit=86
 Planning Time: 0.342 ms
 Execution Time: 24.161 ms

Start at the deepest node, the Parallel Seq Scan. Three processes (loops=3) each returned about 7.33 rows, 22 in total, and each threw away 333,326 rows. To find 22 rows, PostgreSQL read all 8,407 pages of the table. The estimates are fine here (9 per process against 7.33), so statistics are not the problem; the access path is. The Sort above it costs almost nothing, and Gather Merge combines the three sorted streams. 24 ms looks harmless until the query runs on every page view.

An index that matches both the filter and the sort order fixes it. On a live table, build it with CONCURRENTLY so writes are not blocked:

CREATE INDEX CONCURRENTLY orders_customer_created_idx
    ON orders (customer_id, created_at);

 Limit  (cost=0.42..84.58 rows=20 width=22) (actual time=0.027..0.179 rows=20.00 loops=1)
   Buffers: shared hit=26
   ->  Index Scan Backward using orders_customer_created_idx on orders  (cost=0.42..88.79 rows=21 width=22) (actual time=0.025..0.176 rows=20.00 loops=1)
         Index Cond: (customer_id = 4210)
         Index Searches: 1
         Buffers: shared hit=26
 Planning:
   Buffers: shared hit=110
 Planning Time: 0.384 ms
 Execution Time: 0.196 ms

The index returns rows for customer 4210 already ordered by created_at, read backwards for DESC, so there is no sort and the Limit stops after 20 rows. Pages touched fell from 8,521 to 26 and execution time from 24 ms to 0.2 ms. The equality column comes first in the index and the sort column second; the other order could not serve this query nearly as well.

Common fixes

  • Add an index on the columns in a selective filter or join condition, with equality columns before range and sort columns.
  • Make conditions index-friendly. WHERE created_at::date = '2026-10-20' cannot use a plain index on created_at; write it as a range from '2026-10-20' to '2026-10-21'. For WHERE lower(email) = ..., create an index on lower(email).
  • Run ANALYZE after bulk loads and large updates, and use extended statistics for correlated columns.
  • When a node shows Sort Method: external merge Disk: ... or a Hash with several Batches, try a larger work_mem for that session, for example SET work_mem = '64MB';.
  • When an Index Only Scan shows many Heap Fetches, run VACUUM on the table or tune autovacuum for it.
  • Fetch fewer rows: add a LIMIT the index can serve, and select only the columns you need so an index-only scan becomes possible.

For queries that are only slow sometimes, the auto_explain module logs the plans of statements slower than a threshold you set.

Reading plans in a GUI client

A text plan gets hard to follow once it is 40 lines deep. Graphical plan viewers draw the same tree as boxes and highlight the expensive nodes. dbPlus, our native Mac client for PostgreSQL, MySQL, MariaDB, SQLite and SQL Server, shows the query plan as a tree and points out the slow steps. dbPlus is coming to the Mac App Store; it is free, with dbPlus Premium at USD 19 a year. For other options, see our comparison of PostgreSQL GUI clients for Mac. If you are moving data between engines, our guide to copying a table from MySQL to PostgreSQL ends with the ANALYZE step that keeps these estimates honest.

FAQ

Does EXPLAIN ANALYZE run the query?

Yes. It executes the statement and discards only the rows a SELECT would return. Wrap INSERT, UPDATE, DELETE and MERGE in BEGIN and ROLLBACK to discard their changes.

Why does the cost not match the execution time?

Cost is measured in planner units based on page reads and per-row CPU work, not in milliseconds. It exists so the planner can compare alternative plans for the same query. Use actual time and Execution Time for real timing.

What does loops mean in EXPLAIN ANALYZE?

It is the number of times the node ran. The node's actual time and rows are averages per run, so multiply them by loops for totals. The inner side of a nested loop and the processes of a parallel scan are the usual sources of loops above 1.

Why does PostgreSQL ignore my index?

The most common reasons are that the query returns a large share of the table (a sequential scan is then cheaper), the condition wraps the column in a function or cast, the statistics are stale, or the index column order does not match the query. Compare estimated and actual rows first, then check the condition.

Should I use FORMAT JSON?

Use it when you want to feed the plan to a visualizer or a script. For reading by eye, the default text format is shorter.

All articles