The Slow Query Log

Reading EXPLAIN (ANALYZE, BUFFERS) without fooling yourself

Most people run EXPLAIN ANALYZE, look at the top-level actual time, and stop there. That number tells you a query was slow. It doesn't tell you why, and on a warm production box it can actively mislead you, because it's measuring a cache state that won't exist the next time the query runs with different parameters or after a checkpoint flushes the buffer pool. The fix is cheap: always add BUFFERS, and learn to read it.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > now() - interval '7 days'
  AND o.status = 'paid';

You'll get a block like this attached to each node:

Buffers: shared hit=812 read=4113 dirtied=2

What the three numbers actually mean

shared hit is a page Postgres found already sitting in shared_buffers — no syscall, no disk, just a memory lookup and a pin/unpin. shared read is a page it had to pull in via read(), which on most cloud instances means a round trip through the OS page cache (fast, but not free) or, if the OS cache missed too, an actual disk/EBS/network-storage read (slow, and the number you should actually be scared of). EXPLAIN can't tell these two apart for you — a read that landed in OS cache and a read that hit cold storage both show up as read. If you need to split them, you have to correlate with pg_stat_io (PG16+) or watch iostat while the query runs.

dirtied counts pages the executor had to mark dirty — typically from hint-bit setting on first read of a row, not from your query doing writes. On a freshly-loaded or freshly-vacuumed table this will be near zero. On a table that's had a lot of recent inserts without a vacuum pass, you'll see dirtied climb on plain SELECTs, which surprises people who assume a read-only query can't dirty pages.

Mistake 1: optimizing the warm-cache number

Run the same query twice and the second EXPLAIN ANALYZE will usually show far more hit and far less read, because the first run pulled everything into shared_buffers. If you tune against the second run, you're tuning for a cache state that only exists because you just ran the query — which won't hold for a table that's actually under memory pressure from everything else in your buffer pool. Always look at the first cold run, or better, use pg_stat_statements or auto_explain with log_min_duration in production to capture plans as they actually occur under real contention.

Mistake 2: blaming the wrong node

Buffers are reported per-node, and a nested loop with a bad inner scan can hide a huge read count inside a node that looks small in the top-level summary. Read the plan bottom-up, not top-down: find the node with the highest read count, not the node with the highest row count. A Seq Scan that reads 3 buffers isn't your problem even if it has a scary-looking row estimate; an Index Scan buried three levels down that reads 400,000 buffers because of a bad correlation between the index and physical table order is.

Mistake 3: ignoring loops

Inside a nested loop, Postgres reports actual time and Buffers for the inner node per execution, but the row count display and some tooling (older pgAdmin versions, some plan visualizers) can make it easy to misread this as a total. Check the loops=N value and, if you're reading the numbers by hand, multiply the per-loop buffers by N to get the real total. This is the single most common reason people conclude "that index scan is cheap" about a scan that's actually running 50,000 times.

->  Index Scan using customers_pkey on customers c
      (actual time=0.004..0.006 rows=1 loops=48213)
      Buffers: shared hit=2
      -- real cost: 48213 × 2 = 96,426 buffer hits, not 2

Rule of thumb: if shared read on the slowest node is more than a few hundred and it's not a one-off cold-cache run, that's where your index or your working-set size needs attention — not the operator type, not the join order. Buffers point at what to fix; everything else in the plan just explains how Postgres chose to do it.

A workflow that actually works

  1. Run EXPLAIN (ANALYZE, BUFFERS) on a connection that hasn't touched the table this session. shared_buffers is server-wide shared memory, not per-connection state, so opening a new connection does not clear it — a page another session pulled in five minutes ago still shows as hit for you. To genuinely test the cold-cache case, either restart the Postgres server (clears shared_buffers, and usually the OS page cache too if it's a container/VM restart), or use pg_prewarm's companion pattern in reverse: pick a table or index that's large enough, or cold enough in practice, that it's actually been evicted by other traffic — don't rely on a fresh psql session to give you a fresh cache.
  2. Find the node with the highest shared read, accounting for loops.
  3. Check whether that node's access pattern (index scan vs. seq scan, bitmap heap scan) matches the selectivity you'd expect. A bitmap heap scan with a huge Heap Blocks: exact count usually means your index is selective on paper but the matching rows are scattered across the table — a candidate for CLUSTER or a covering index, not a different query shape.
  4. Re-run after the fix with a genuinely cold cache, not just a new session, before declaring victory.

None of this requires exotic tooling. It requires reading the line most people skip.