PostgreSQLadvanced

PostgreSQL Query Planning

How the planner chooses a plan, how to read EXPLAIN output, and the estimate errors that cause most bad plans.

3 min readAdvancedUpdated 31 Jul 2026Edit this page

PostgreSQL's planner is cost-based: it enumerates plans, estimates the cost of each from table statistics, and executes the cheapest. Bad plans almost always come from bad estimates, not from a bad planner.

EXPLAIN and EXPLAIN ANALYZE

EXPLAIN shows the plan and its estimates. EXPLAIN ANALYZE executes the query and shows what actually happened.

EXPLAIN ANALYZE runs the statement

On INSERT, UPDATE or DELETE it performs the modification. Wrap it in a transaction you roll back:

BEGIN;
EXPLAIN (ANALYZE, BUFFERS) DELETE FROM events WHERE created_at < '2020-01-01';
ROLLBACK;

Always include BUFFERS; it turns "this is slow" into "this read 400,000 pages from disk".

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT)
SELECT o.id, o.total_cents, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending' AND o.created_at >= now() - interval '7 days'
ORDER BY o.created_at DESC
LIMIT 50;

Limit  (cost=0.86..812.44 rows=50 width=48) (actual time=0.061..2.113 rows=50 loops=1)
  Buffers: shared hit=214 read=9
  ->  Nested Loop  (cost=0.86..48213.02 rows=2968 width=48) (actual time=0.059..2.098 rows=50 loops=1)
        Buffers: shared hit=214 read=9
        ->  Index Scan Backward using orders_pending_idx on orders o
              (cost=0.43..21882.11 rows=2968 width=24) (actual time=0.037..0.402 rows=50 loops=1)
              Index Cond: (created_at >= (now() - '7 days'::interval))
        ->  Index Scan using customers_pkey on customers c
              (cost=0.43..0.89 rows=1 width=32) (actual time=0.028..0.029 rows=1 loops=50)
              Index Cond: (id = o.customer_id)
Planning Time: 0.284 ms
Execution Time: 2.169 ms

Reading it

Read inside out. The most indented nodes execute first.
rows estimated versus actual is the first thing to check. An estimate of 3,000 against an actual of 2,000,000 explains almost every bad plan you will meet.
loops multiplies. A node showing actual time=0.028..0.029 rows=1 loops=50 cost 50 × 0.029 ms in total.
Buffers: shared hit came from cache; read came from storage. High read on a query you expected to be cached points at memory pressure, not at the plan.
Rows Removed by Filter means rows were read and discarded — usually a missing or mis-ordered index.

Node types worth recognising

Node	Means	Concerning when
Seq Scan	Reads the whole table	The table is large and the predicate is selective
Index Scan	Walks the index, fetches rows	Rarely a problem
Index Only Scan	Answered from the index alone	Degrades if the visibility map is stale
Bitmap Heap Scan	Collects rows, then reads in physical order	Fine; a middle ground for medium selectivity
Nested Loop	For each outer row, probe inner	Outer row count is large and estimate was wrong
Hash Join	Builds a hash of one side	Build side spills to disk
Merge Join	Both inputs sorted	An unexpected sort feeds it

A sequential scan is not automatically a fault. On a small table, or when a query touches most rows, it is the correct choice.

Why estimates go wrong

Stale statistics. Run ANALYZE after bulk loads; autovacuum's analyze may not have caught up.
Correlated columns. The planner assumes independence, so WHERE city = 'Paris' AND country = 'France' is estimated as the product of two selectivities. Fix with extended statistics:

CREATE STATISTICS orders_city_country (dependencies, ndistinct)
    ON city, country FROM orders;
ANALYZE orders;

Skewed distributions. Increase the histogram resolution for that column:

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

Expressions the planner cannot see through. WHERE date_trunc('day', created_at) = ... has no statistics; an expression index gives it some.