PostgreSQL in Practice
PostgreSQL Planner and EXPLAIN
PostgreSQL’s planner searches access paths, join orders, and algorithms using sampled statistics and a configurable cost model. EXPLAIN work is a controlled experiment: locate the first estimate divergence, account for loops, buffers, I/O and spills, change one cause, and remeasure.
- Cardinality estimates dominate downstream choices.
- Costs are comparative estimates, not milliseconds.
- Execution nodes form a demand tree.
- Prepared statements may choose custom or generic plans.
- EXPLAIN ANALYZE executes the statement.
- Buffers and I/O timings need context.
EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS, FORMAT TEXT)
SELECT o.order_id, sum(oi.quantity * oi.unit_price)
FROM commerce.orders AS o
JOIN commerce.order_items AS oi ON oi.order_id = o.order_id
WHERE o.customer_id = $1 AND o.status = 'PAID'
GROUP BY o.order_id;
CREATE STATISTICS orders_customer_status (dependencies, mcv)
ON customer_id, status FROM commerce.orders;
ANALYZE commerce.orders;| Symptom | Inspect | Possible response |
|---|---|---|
| estimate diverges at scan | pg_stats, parameter value, predicate/type | ANALYZE, statistics target, expression/extended stats |
| loops multiply inner work | loops × rows/buffers | join/index/query-shape correction |
| hash batches or sort disk | rows/width, memory and temp I/O | reduce input; bounded session-local work_mem test |
| many heap fetches | visibility map and churn | vacuum health; narrower covering index only if justified |
| good plan, slow runtime | wait events, I/O latency, concurrency | treat saturation/blocking, not planner knobs |