Query Optimization¶

How the database decides how to run a query¶

SQL says what result is wanted; the query planner (optimizer) decides how: which indexes to use, which join algorithm, in which order to join tables, whether to sort or hash, whether to use parallel workers. For a query joining five tables there are thousands of possible plans whose run times differ by orders of magnitude. The planner picks one by estimating the cost of candidates, and its estimates are only as good as its statistics and cost model. Most "slow query" investigations come down to finding where an estimate was wrong.

Measurements below: PostgreSQL 16.2, the 2-million-row orders table and 100,000-row customers table from Index Types.

Reading EXPLAIN (ANALYZE, BUFFERS)¶

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 4242;

Bitmap Heap Scan on orders  (cost=4.65..118.07 rows=29 width=99)
                            (actual time=0.031..0.075 rows=29 loops=1)
  Recheck Cond: (customer_id = 4242)
  Heap Blocks: exact=29
  Buffers: shared hit=14 read=18
  ->  Bitmap Index Scan on orders_customer  (cost=0.00..4.64 rows=29 width=0)
                                            (actual time=0.020..0.020 rows=29 loops=1)
        Index Cond: (customer_id = 4242)
        Buffers: shared read=3
Planning Time: 0.074 ms
Execution Time: 0.085 ms

Plans are trees; execution pulls rows from the leaves up. Read them inside out. Plain EXPLAIN (without ANALYZE) only shows estimates and never runs the query; EXPLAIN ANALYZE runs it (careful with UPDATE or DELETE — wrap in a transaction and roll back).

The single most useful check: compare estimated rows with actual rows at each node. A gap of 10x or more is where the planner went wrong; everything above that node was planned on false premises.

The cost model¶

PostgreSQL's cost is a weighted sum of estimated work:

random_page_cost = 4 encodes an assumption from spinning disks with caching: random reads are 4x as expensive as sequential ones. On SSDs, and especially when the working set is in memory, that is wrong.

Measured: the planner choosing a slower plan¶

SELECT c.country, sum(o.amount)
FROM orders o JOIN customers c ON c.id = o.customer_id
GROUP BY c.country;              -- joins all 2M orders

Forcing each join algorithm in turn (median of 3 warm runs, parallelism off):

The planner ranked the plans exactly backwards. Its model charged the merge join and nested loop for random index page reads at 4x the sequential price; in reality, all data was already cached in memory, where a random page read costs about the same as a sequential one. On SSD-backed servers whose hot data fits in memory, lowering random_page_cost to 1.1–1.5 and setting effective_cache_size to the real cache size makes the model match reality. This is the general lesson: the planner optimizes its model, not your hardware.

Statistics¶

Estimates come from statistics gathered by ANALYZE (run by autovacuum after enough rows change) and stored in pg_statistic (readable via pg_stats). Per column:

ANALYZE samples 30,000 rows per table by default (default_statistics_target = 100 × 300). For skewed columns, raise it per column: ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 1000.

Where estimates go wrong¶

1. Correlated columns. The planner assumes conditions are independent and multiplies their selectivities. Measured, on a table where zip is fully determined by city (1,000 cities, 1M rows):

WHERE city = 'city42' AND zip = 'zip42'
  estimated rows=1      actual rows=1000          (1/1000 × 1/1000 × 1M = 1)

CREATE STATISTICS addr_dep (dependencies) ON city, zip FROM addr; ANALYZE addr;
  estimated rows=995    actual rows=1000

A 1000x underestimate at a leaf typically turns into a nested loop that the planner thought would run once and actually runs a thousand times. Extended statistics (dependencies, ndistinct, mcv) fix it for column groups you declare.

2. Stale statistics after bulk loads: run ANALYZE right after loading.

3. Functions and expressions without statistics: WHERE lower(email) = ... uses a default selectivity guess (0.5% for equality) unless an expression index exists, whose statistics ANALYZE then collects.

4. Joins of joins. Each join's row estimate multiplies the errors of its inputs; by the fourth join, being 100x off is common. This is the fundamental limit of cost-based optimization.

5. Parameterized queries. A prepared statement can use a generic plan (planned once without knowing the parameter values). PostgreSQL plans the first five executions with the actual values ("custom plans"), then switches to the generic plan if its estimated cost is not much worse. For a skewed column, the generic plan is right for common values and wrong for rare ones (or vice versa). plan_cache_mode = force_custom_plan fixes such cases at the cost of planning every execution.

Join order and planning time¶

For n joined tables, the number of join orders grows factorially. PostgreSQL searches them exhaustively with dynamic programming (the System R approach: best plan for every subset of tables, built up from pairs) up to geqo_threshold (12) tables; beyond that it switches to a genetic search that may miss the best order. join_collapse_limit and from_collapse_limit (8) limit how far explicit JOIN syntax and subqueries are reordered. Queries with very many joins can spend more time planning than executing — Planning Time in EXPLAIN ANALYZE shows it.

Common plan problems and fixes¶

work_mem is allocated per sort or hash node, per query, per parallel worker — a complex query can use many times its value. Raise it per session for analytics rather than globally.

Query-level patterns¶

N+1 queries¶

An application loads N rows, then issues one more query per row: 1 query for 100 orders + 100 queries for their customers = 101 round trips. Each round trip costs network latency plus per-query overhead (~0.1–1 ms even for trivial queries), so 100 small queries take longer than one join, regardless of indexes. ORMs produce this through lazy loading (fix: eager loading — Preload in GORM, select_related in Django); GraphQL resolvers through per-field fetching (fix: the DataLoader pattern batching IDs into one WHERE id IN (...)). Detect it by counting queries per request.

Pagination¶

OFFSET pagination costs O(offset + limit), and rows inserted between page loads shift every later page (duplicates or skipped rows). Keyset (cursor) pagination remembers the last row's sort key and seeks past it with an index — constant cost per page and stable under inserts. Use a unique tie-breaker (id) in the key so rows with equal timestamps are neither duplicated nor skipped. The trade-off: no jumping to an arbitrary page number.

Select only needed columns¶

SELECT * reads wide columns (large text/JSONB stored out of line in TOAST tables, fetched only when selected), transfers them over the network, and prevents index-only scans.

Denormalization and precomputation¶

For read-heavy data, storing derived values (a customer_name copy on orders, a counter column, a materialized view refreshed periodically) removes joins and aggregations from the hot path. The cost is keeping copies in sync on every write; it is a deliberate trade, not a default.

A workflow¶

1. Find the expensive queries: pg_stat_statements (total time, calls, mean time per normalized query), slow query log (log_min_duration_statement), or auto_explain to log plans of slow queries automatically.
2. Run EXPLAIN (ANALYZE, BUFFERS) on the exact query with realistic parameters.
3. Find the lowest node where estimated and actual rows diverge, or where most time or buffers go.
4. Fix the cause: statistics, an index, the query shape, a setting.
5. Re-run and compare; keep the before/after plans.

Common pitfall¶

Trusting plain EXPLAIN output — or the planner's choice itself — as a measurement. EXPLAIN shows predictions from a cost model and statistics that may be stale or structurally wrong: measured above, the planner's estimate ranked three join plans in exactly the reverse of their real speed, and a correlated-column estimate was 1,000x too low. Only EXPLAIN ANALYZE shows what actually happened, and the gap between estimated and actual rows is the thing to read first. Before adding an index or rewriting a query, check whether the planner simply misestimated — fixing the estimate often fixes the plan.