Every SQL query you write passes through a cost-based optimizer before execution. PostgreSQL's planner evaluates thousands of potential plans and picks the one with the lowest estimated cost. But the cost model is only as good as its statistics, and when those are stale or missing, the planner makes catastrophically wrong decisions.
How the Planner Works
PostgreSQL's planner follows a simple pipeline:
- Parse the SQL into a query tree
- Rewrite using rules and views
- Plan by generating candidate plans and estimating costs
- Execute the cheapest plan
The cost model uses abstract units — not milliseconds or I/O operations. A sequential page read costs 1.0 (the seq_page_cost default), while a random page read costs 4.0 (random_page_cost). The ratio reflects the physical reality that random I/O is ~4x slower than sequential on spinning disks.
-- Default cost parameters in postgresql.conf
seq_page_cost = 1.0 # Cost of reading a page sequentially
random_page_cost = 4.0 # Cost of reading a page randomly
cpu_tuple_cost = 0.01 # Cost of processing each tuple
cpu_index_tuple_cost = 0.005 # Cost of processing each index tuple
cpu_operator_cost = 0.0025 # Cost of processing each operator
effective_cache_size = 4GB # Planner's assumption about available cacheEXPLAIN ANALYZE: The Truth Machine
EXPLAIN ANALYZE actually runs the query and reports actual times. But there's a subtle trap: it only shows the first row's timing for cursors and doesn't account for lock waits.
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.*, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'pending'
AND o.created_at > '2026-01-01';The output reveals:
Nested Loop (cost=0.87..1234.56 rows=100 width=128) (actual time=0.12..12.34 rows=98 loops=1)
Buffers: shared hit=543 read=67
-> Index Scan using idx_orders_status on orders o (cost=0.43..890.12 rows=100 width=64) (actual time=0.08..8.90 rows=98 loops=1)
Index Cond: (status = 'pending')
Filter: (created_at > '2026-01-01')
Rows Removed by Filter: 12
Buffers: shared hit=321 read=45
-> Index Scan using customers_pkey on customers c (cost=0.43..3.44 rows=1 width=64) (actual time=0.03..0.03 rows=1 loops=98)
Index Cond: (id = o.customer_id)
Buffers: shared hit=222 read=22
Planning Time: 0.123 ms
Execution Time: 12.567 msThe key columns to watch:
- rows: Estimated vs actual (large mismatch = bad statistics)
- Buffers: shared hit (cache) vs read (disk I/O)
- actual time: First row to last row (not total time for all rows)
The Cost Model Deep Dive
PostgreSQL's cost estimation for each plan node:
Cost = (I/O cost) + (CPU cost)
= (pages × page_cost) + (tuples × tuple_cost) + (operators × operator_cost)For a sequential scan:
-- Seq Scan cost calculation
-- Pages: 10,000
-- Tuples: 500,000
-- Selectivity: 0.1 (10% of rows match)
-- Cost = pages × seq_page_cost + tuples × selectivity × cpu_tuple_cost
-- Cost = 10,000 × 1.0 + 500,000 × 0.1 × 0.01 = 10,000 + 500 = 10,500
-- Index Scan cost calculation
-- Index pages: 500
-- Matching tuples: 50,000
-- Table pages to fetch: 50,000 (random)
-- Cost = index_pages × random_page_cost + tuples × (cpu_index_tuple_cost + cpu_tuple_cost) + table_pages × random_page_cost
-- Cost = 500 × 4.0 + 50,000 × (0.005 + 0.01) + 50,000 × 4.0
-- Cost = 2,000 + 750 + 200,000 = 202,750When the Planner Gets It Wrong
The planner makes mistakes when:
- Statistics are stale: Data has changed since last ANALYZE
- Correlated columns: Assumes independence between columns
- Complex expressions: Can't estimate selectivity of functions
- Parameter sensitivity: One plan for all parameter values
-- Stale statistics example
CREATE TABLE events (
id serial PRIMARY KEY,
type text,
created_at timestamptz,
payload jsonb
);
INSERT INTO events SELECT
generate_series(1, 1000000),
CASE WHEN random() < 0.01 THEN 'error' ELSE 'info' END,
now() - (random() * interval '365 days'),
'{}';
ANALYZE events;
-- Now insert 100,000 errors (without ANALYZE)
INSERT INTO events SELECT
generate_series(1000001, 1100000),
'error',
now(),
'{}';
-- Planner thinks errors are still 1%, but they're now ~10%
-- This can cause it to choose the wrong index or scan type
EXPLAIN ANALYZE SELECT * FROM events WHERE type = 'error';tradeoff / Plan Stability vs Adaptivity
Most applications benefit from adaptive planning. Force plans only for critical queries where latency consistency matters more than optimal performance.
Forced plans guarantee consistent performance but break when data distributions change. Adaptive planning adapts but may produce inconsistent latency.
Tuning the Planner
-- Adjust cost parameters for SSD storage
SET random_page_cost = 1.1;
SET effective_cache_size = '16GB'; -- Match actual RAM
-- Per-table statistics
ALTER TABLE events SET STATISTICS 1000; -- Increase sample size
ANALYZE events;
-- Materialized views for expensive joins
CREATE MATERIALIZED VIEW order_summary AS
SELECT customer_id, COUNT(*), SUM(total)
FROM orders
GROUP BY customer_id;
-- Partial indexes for common filters
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';Synthesis
The query planner is a cost-based optimizer that relies on accurate statistics and reasonable cost assumptions. EXPLAIN ANALYZE reveals what the planner chose and why, but interpreting the output requires understanding the cost model. Tune random_page_cost for your storage, keep statistics current with ANALYZE, and use covering indexes to eliminate expensive table lookups.