TopolearnBuffers show how much data a query touches, which is usually the real cost behind slow queries.
EXPLAIN (ANALYZE, BUFFERS) runs the query and shows, per plan node, the estimated vs actual rows, timing, and buffers: how many 8 kB pages were read from PostgreSQL's cache (shared hit) or from the OS/disk (read). From PostgreSQL 18, EXPLAIN ANALYZE includes buffers by default.
How to read a plan:
- Read it inside-out: the most indented nodes run first and feed their parents.
actual time=a..bis in milliseconds per loop; multiply byloopsfor nested nodes.- Compare
rows=estimated vs actual. Off by 10x or more means statistics or a predicate the planner can't estimate. Rows Removed by Filterlarge compared to rows returned suggests a missing or unusable index.- Sorts or hashes that spill show
external merge Disk:orBatches: >1— a signwork_memis too small for that query. - High
readbuffers means a cold cache or simply touching too much data.
Use FORMAT JSON and a visualiser (explain.dalibo.com, pgMustard) for big plans. To catch slow queries in production you can't reproduce, enable the auto_explain module with auto_explain.log_min_duration, which logs plans of slow statements automatically.
Remember ANALYZE executes the statement: wrap UPDATE/DELETE in BEGIN; ... ROLLBACK;.
Example
CREATE TABLE orders (id bigint PRIMARY KEY, customer_id int, total numeric);
INSERT INTO orders SELECT g, g % 5000, g % 97 FROM generate_series(1, 300000) g;
ANALYZE orders;
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42;
-- Gather (actual rows=60)
-- -> Parallel Seq Scan on orders (actual rows=30 loops=2)
-- Rows Removed by Filter: 149970 <- per worker: reads everything
-- Buffers: shared hit=1911
CREATE INDEX orders_customer_idx ON orders (customer_id);
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42;
-- Bitmap Heap Scan on orders (actual rows=60)
-- Buffers: shared hit=60 read=2 <- 62 pages instead of 1911Remember
- BUFFERS shows pages from cache (hit) and from disk (read)
- Big estimate-vs-actual gaps and large Rows Removed by Filter point to the fix
- auto_explain logs plans of slow queries in production
Watch out for
- Timing a query twice and trusting the second, cached run as representative.