Query tuning cookbook

A big table scanned end-to-end to satisfy a condition that throws almost every row away. Usually a missing index — sometimes a predicate the existing index can't use.

The plan

Seq Scan on big  (cost=0.00..17029.00 rows=18348 width=0)
                 (actual time=62..113 rows=80000 loops=1)
  Filter: ((cat = 7) AND (val = 999999))
  Rows Removed by Filter: 500000
  Buffers: shared hit=8329

The analyzer flags it as Seq Scan on big · 113 ms (95% of plan) and Filter on big discards 500000 rows, 80000 kept.

The fix

Add an index that lines up with the filter. With one equality predicate (cat = 7) and one high-selectivity one (val = 999999), a composite index on the equality columns wins:

CREATE INDEX CONCURRENTLY big_cat_val_idx ON big (cat, val);
ANALYZE big;  -- so the planner actually picks it up

For inequality / range predicates, put equality columns first and the range column last — see Index Scan for the why. If only one predicate is hot, a partial index (… WHERE cat = 7) is even smaller and faster.

A Sort node that finished as an external merge — much slower than an in-memory quicksort.

The plan

Sort  (actual time=… rows=580000 loops=1)
  Sort Key: payload
  Sort Method: external merge  Disk: 34824kB
  Buffers: temp read=21087 written=22237

The analyzer flags Sort spilled to disk · 34824 kB. The disk size is not the work_mem you need: an in-memory sort needs roughly 2–3× the on-disk size because of the SortTuple pointer array and per-tuple overhead.

The fix

-- Lift it for just this query (work_mem is per-sort, per-connection)
SET LOCAL work_mem = '128MB';
SELECT … ;

Even better — provide the order via an index so the sort disappears entirely:

CREATE INDEX CONCURRENTLY big_payload_idx ON big (payload);
-- now ORDER BY payload streams in order; no Sort node at all

For very large sorts, raising work_memto absurd values isn't the answer — see work_mem and Sort.

A Hash Join whose build side didn't fit in work_mem, so PostgreSQL partitioned it across many batches — each batch written to and read from temp files.

The plan

Hash Join  (actual rows=500000 loops=1)
  Hash Cond: (t.big_id = b.id)
  ->  Seq Scan on big2 t  (…)
  ->  Hash  (rows=580000 loops=1)
        Buckets: 65536  Batches: 32  Memory Usage: 4096kB

The analyzer flags Hash build spilled across 32 batches.

The fix

SET LOCAL work_mem = '256MB';
-- or specifically for hashes:
SET LOCAL hash_mem_multiplier = 4.0;

hash_mem_multiplier lets you give hashes more memory than sorts without raising the global work_mem for every operation. See hash_mem_multiplier.

Also check the build side: if the planner mis-estimated row counts and hashed the bigger relation, ANALYZE often flips it to the smaller side and the spill goes away on its own.

The plan says Index Only Scan but Heap Fetches is high — meaning the visibility map is stale and the scan had to read the heap to confirm visibility for most rows.

The plan

Index Only Scan using idxonly_v on idxonly
  (cost=0.42..8331.68 rows=198663 width=0)
  (actual time=0.02..39.7 rows=150000 loops=1)
  Heap Fetches: 170500

The analyzer flags Index Only Scan on idxonly did 170500 heap fetches.

The fix

VACUUM (ANALYZE) idxonly;       -- right now
-- and prevent it coming back:
ALTER TABLE idxonly SET (autovacuum_vacuum_scale_factor = 0.02);
-- on insert-heavy tables (PG 13+):
ALTER TABLE idxonly SET (autovacuum_vacuum_insert_scale_factor = 0.05);

A vacuum re-sets the visibility map so subsequent index-only scans actually stay in the index. For append-mostly tables (logs, events, time-series), lowering autovacuum_vacuum_insert_scale_factor keeps the map fresh without waiting for a giant batch of deletes.

A Nested Loop whose inner side is re-executed for every outer row, usually because the planner under-estimated the outer side.

The plan

Nested Loop  (actual rows=2388 loops=1)
  Join Filter: ((‘name’ || …) = d.name)
  ->  Seq Scan on big2 t  (rows=2388 loops=1)
        Filter: (amt < 5)
  ->  Materialize  (actual rows=1000 loops=2388)

The analyzer flags Nested Loop rescans Materialize 2388 times and Bad row estimate on big2 · estimate under-shoots actual by 80×.

The fix

Fix the estimate first, then the planner usually picks a Hash Join on its own:

ANALYZE big2;
-- if multiple columns correlate, give the planner that knowledge:
CREATE STATISTICS big2_amt_big_id_stats (dependencies, ndistinct)
  ON amt, big_id FROM big2;
ANALYZE big2;

If a Nested Loop genuinely is the right plan (small outer + indexed inner) but the inner is rescanned with repeating keys, PostgreSQL 14+ may put a Memoize node in between — that's the planner doing the right thing and the high hit rate is the win.

A Bitmap Heap Scan whose bitmap didn't fit in work_mem, so it's storing block-level entries instead of per-tuple ones — and re-checking every row in each matching block.

The plan

Bitmap Heap Scan on big  (actual time=… rows=… loops=1)
  Recheck Cond: (val < 5000)
  Rows Removed by Index Recheck: 1240221
  Heap Blocks: exact=662 lossy=6724

The analyzer flags Lossy bitmap on big · 6724 lossy blocks (662 exact).

The fix

SET LOCAL work_mem = '128MB';

A bigger work_mem lets PostgreSQL hold an exact per-tuple bitmap, removing the per-row recheck. If the bitmap is huge because the predicate is loose, a more selective index (or rewriting the predicate to a tighter form) is the alternative. See Bitmap Heap Scan.