Database Query Execution Plans

EXPLAIN prints a query's chosen plan as an indented tree — but execution runs the opposite direction from how you read it. The most deeply indented nodes (scans of actual tables) execute first; their output feeds upward into join and aggregate nodes above them, which feed further upward, until the topmost node produces the final result set. Read the indentation as a call stack, not a sequence of steps.

Each node reports cost as startup_cost..total_cost — an estimate of relative work in arbitrary planner units, not milliseconds — plus estimated rows (how many rows the planner expects that node to return) and width (estimated average row size in bytes). Run EXPLAIN ANALYZE instead of plain EXPLAIN and the query actually executes, adding actual time in real milliseconds, actual rows, and loops (how many times a node ran — relevant for a node nested inside another that executes it once per outer row).

Seq Scan reads every row in a table — correct and often fastest when a query needs a large fraction of the table, since sequential I/O beats the random I/O of chasing an index. Index Scan uses an index to find matching rows, then fetches each one from the table heap — efficient for high-selectivity queries (a small fraction of the table), expensive if it forces many scattered heap fetches. Index Only Scan answers entirely from the index itself, skipping the heap fetch — only possible when every requested column is in the index and Postgres's visibility map confirms the relevant pages have no pending vacuum work. Bitmap Heap Scan (paired with Bitmap Index Scan) builds a bitmap of matching pages from the index, then visits those pages in physical order — a middle ground for medium-selectivity queries where a plain Index Scan's random I/O would be worse than this batched approach.

For combining two row sets, Nested Loop scans the inner side once per row of the outer side — cheap when the outer set is small and the inner side has a fast index lookup, disastrous if the outer set is large. Hash Join builds an in-memory hash table from one side and probes it with the other — strong for large, unsorted inputs. Merge Join requires both inputs already sorted on the join key and merges them in one pass — efficient when that sort order already exists (often from an index), wasteful if the database has to sort both sides first just to use it.

The planner chooses a plan based entirely on its estimates — cost, rows, and width — computed from table statistics gathered by ANALYZE (run automatically by autovacuum, though it can lag on rapidly changing tables). When a node's actual row count differs wildly from its estimate, everything the planner decided above that node in the tree was decided on bad information — a join node expecting 10 rows from below it might pick a Nested Loop that becomes catastrophic when 50,000 rows actually show up.

A large gap has a short list of usual causes: stale statistics (the table changed significantly since the last ANALYZE), correlated columns (the planner assumes columns are independent by default, so WHERE region = 'us' AND warehouse = 'us-east-1' may badly misjudge combined selectivity — CREATE STATISTICS can teach it the correlation), or a non-sargable predicate (wrapping an indexed column in a function, like WHERE lower(email) = $1 against a plain index on email, defeats the index unless a matching expression index exists).

Trigger. A team adds CREATE INDEX idx_orders_status ON orders(status) expecting WHERE status = 'refunded' to get fast. Symptom. EXPLAIN still shows Seq Scan on orders, not Index Scan using idx_orders_status — the index exists, is valid, and is simply not being used. Root mechanism. Statistics show refunded orders are 22% of the table — well past the selectivity point where a Seq Scan's sequential I/O legitimately beats an Index Scan's random heap fetches for that many matching rows. The planner isn't broken; it correctly priced both options and Seq Scan actually won.

Recovery: confirm this with EXPLAIN ANALYZE — if actual rows at the Seq Scan node roughly matches the estimate, the planner's choice was informed and correct, and the fix is not "force the index" but reconsidering the query itself (a partial index on just the rare status values, or restructuring the workload to not need this on the hot path). Verification: test SET enable_seqscan = off in a throwaway session and compare the forced Index Scan's actual time against the original Seq Scan's — often the Seq Scan is genuinely faster, proving the index was never the fix this query needed. Prevention: before adding an index for a specific value, check that value's actual selectivity in the table first — an index on a common value is dead weight, not a missing optimization.