Been using EXPLAIN ANALYZE for years. Today I finally paid attention to BUFFERS:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM events WHERE id = 42;

Output includes:

Buffers: shared hit=4 read=2

hit is blocks served from shared buffers (Postgres’s cache). read is blocks read from disk (or OS cache — Postgres can’t tell). For the same query run twice in a row:

  • First: some read=N, meaning it had to fetch from disk.
  • Second: read=0, meaning the buffers warmed up.

If you’re trying to benchmark a query fairly, run it twice and use the second result. Otherwise your first run is measuring “Postgres loading data from disk” plus “query execution.”

Also useful: written counts (for hint bits being persisted) and temp counts (if the query spilled to temp files). A query with temp read=100MB is doing more sorting/hashing than work_mem allows. Fix: bump work_mem for that session, or rewrite the query.

Turning on track_io_timing = on in postgresql.conf adds actual I/O time to the output:

Buffers: shared hit=4 read=2 I/O Timings: shared/local read=0.5ms

Small quality of life, big impact on how I read plans.