← Back to Postgres EXPLAIN Analyzer
A plan is a tree. Postgres runs the leaves first and feeds rows upward, so read it from the most indented line outward and ask one question at each node: how much time was spent here, and why?
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
Plain EXPLAIN shows estimates only. ANALYZE executes the statement, so wrap writes in BEGIN ... ROLLBACK. BUFFERS adds I/O per node.
A node's actual time includes its children, and it is a per-loop figure. Self time is time × loops minus the children's. A node that reads 0.15 ms but runs loops=4800 is 720 ms. The analyzer does this arithmetic for every node and lists the top three.
A scan that reads 100,000 rows and keeps 5 is doing 99,995 rows of wasted work. On a Seq Scan the fix is usually an index on the filtered columns, equality columns first and range columns last. On an Index Scan it means the index covers only part of the predicate.
Compare rows= in the cost bracket with rows= in the actual bracket. A gap of 10× or more means the planner chose join order and method on bad information. Run ANALYZE, and use CREATE STATISTICS for correlated columns.
Sort Method: external merge Disk: 4096kB and Batches: 8 on a Hash node both mean the work did not fit in work_mem. Raise it for the one query with SET LOCAL work_mem, rather than globally.
A Nested Loop whose inner side is a Seq Scan with thousands of loops re-reads the table once per outer row. Index the join column or let the planner pick a hash join.
psql -At -c "EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ..." | node cli.mjs analyze -
Either no usable index exists for the filter, the planner expects to read most of the table anyway, or its row estimate is wrong. A Seq Scan with a large 'Rows Removed by Filter' is the sign of a missing index; a Seq Scan that keeps most rows is usually correct.
The planner's statistics are stale or cannot model the predicate, usually correlated columns. Run ANALYZE, then consider CREATE STATISTICS on the columns used together. Wrong estimates are the root cause of most bad join choices.
Raise work_mem for that query with SET LOCAL, or add an index that already returns rows in the ORDER BY order. Sort Method: external merge Disk means it spilled.