← Notes

Always pass BUFFERS to EXPLAIN ANALYZE

· 1 min read
PostgreSQL

EXPLAIN ANALYZE alone tells you how long a query took. BUFFERS tells you why:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM invoices WHERE customer_id = 42;

Read the buffer lines:

  • shared hit — pages served from Postgres’ own cache. Cheap.
  • shared read — pages that had to come from the OS or disk. This is your real cost.
  • temp read/written — a sort or hash spilled to disk. Usually means work_mem is too small for this plan.

A query with a “good” plan that still reads 200k buffers isn’t fast, it’s just efficiently doing too much work. Buffers are the number I look at first.