Reading a plan
A plan is a tree of operations; the root returns the result and each node pulls rows from its children. EXPLAIN alone shows the planner’s estimates (cost units and expected rows); EXPLAIN ANALYZE actually runs the query and adds real times and row counts, which is what makes a plan readable. BUFFERS adds how many 8 kB pages came from cache (hit) or disk (read). The usual way through a plan: find the node with the largest exclusive time, then look at why it is there: a wrong row estimate above it, a missing index, or a memory setting.
The numbers on a node
| Field | Meaning |
|---|---|
| cost=0.29..8.31 | Planner’s estimate in arbitrary units: cost to return the first row .. cost to return all rows. Only comparable within one plan. |
| rows= | Estimated rows per loop. The actual counterpart is also per loop; multiply by loops for the total. |
| actual time=0.05..12.3 | Milliseconds to the first row .. last row, per loop, including children. The tool subtracts the children to show each node’s own time. |
| loops= | How many times the node ran. The inner side of a nested loop runs once per outer row; below a Gather it equals the number of workers. |
| Buffers: shared hit=.. read=.. | Pages found in shared_buffers vs read from the OS/disk. Reads are where I/O time goes; track_io_timing shows it as I/O Timings. |
| Rows Removed by Filter | Rows the node read and then threw away: the clearest sign that an index is missing. |
The five usual problems
- Wrong estimates. If a node expects 10 rows and gets 100,000, the planner chose a nested loop or a sort that only made sense for 10. Run
ANALYZE; for predicates on correlated columns create extended statistics; for CTEs and functions the planner has no idea and defaults to guesses. - Sequential scan with a selective filter. Fine on small tables. On large ones the fix is an index on the filtered columns, ideally matching the WHERE and the ORDER BY.
- Spills. Sort external merge, Hash Batches > 1, HashAggregate on disk, lossy bitmaps: the operation did not fit in
work_mem. Raising it for the session or the query is cheap; raising it globally multiplies by connections. - Nested loop over a scan. The inner side runs once per outer row; if it is a Seq Scan or a Materialize of a big set, time explodes. The inner side needs an index, or the planner needs to be allowed a hash join.
- Index-only scans that are not. Heap Fetches close to the row count means the visibility map is stale: VACUUM the table.
Getting plans
-- PostgreSQL: the most useful form
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, FORMAT JSON) SELECT …;
-- for writes, inside a transaction you roll back
BEGIN; EXPLAIN (ANALYZE, BUFFERS) UPDATE …; ROLLBACK;
-- log slow plans automatically: auto_explain
LOAD 'auto_explain'; SET auto_explain.log_min_duration = '200ms'; SET auto_explain.log_analyze = on;
-- MySQL 8.0.18+
EXPLAIN ANALYZE SELECT …; -- tree with actual times
EXPLAIN FORMAT=JSON SELECT …; -- estimates with cost detailsQuestions
Why do the node times not add up to the total?
Each node’s actual time includes its children, so the tool shows the exclusive time (node minus children). Parallel workers run at the same time, so a node under Gather with loops=3 is counted once, not three times; inner sides of nested loops are multiplied by loops. Planning time and trigger time are listed separately.
What is a good plan?
One where the estimates are within a factor of ten of the actuals, no node spills to disk, the most expensive node is the one you expect (the big table you really have to read), and buffers read from disk are small compared with hits. The findings list is empty for such a plan.
Does it support partitioned tables and parallel plans?
Yes: Append / Merge Append over partitions, Gather and Gather Merge with Workers Planned/Launched, Partial and Finalize aggregates. Per-worker breakdowns (VERBOSE) are parsed but shown as part of the node.
Can I paste a plan from pgAdmin, DataGrip or psql with table borders?
Yes. Lines are cleaned of psql’s box characters, the QUERY PLAN header and the “(n rows)” footer. JSON copied from a GUI as a single quoted string should be unquoted first.
How does the MySQL support compare?
MySQL 8’s EXPLAIN ANALYZE tree has the same shape as a PostgreSQL text plan and is fully supported; EXPLAIN FORMAT=JSON (estimates only) is mapped onto the same tree: access types become scan kinds, nested_loop becomes a join node, and filesort / temporary table flags become findings. MariaDB’s ANALYZE FORMAT=JSON is not supported yet.