What does EXPLAIN (ANALYZE, BUFFERS) add and what do shared hit and read mean?
BUFFERS reports how many 8 kB pages each plan node touched. shared hit means the page was already in PostgreSQL's cache, shared read means it came from the operating system or disk. It shows I/O cost that timing alone hides.
Explain it simply
Timing tells you a query took 400 ms. It does not say why. BUFFERS says how much data it touched. Think of two students who both took an hour on an exam: one had every book on the desk (cache hits), the other walked to the library for each fact (reads).
Each buffer is one 8 kB page. shared hit=500 means 500 pages found in shared_buffers. shared read=9000 means 9000 pages had to be fetched, which is slow on a cold cache.
Example
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM orders WHERE created_at > now() - interval '7 days';
-- Seq Scan on orders (actual time=0.02..310.5 rows=52000 loops=1)
-- Buffers: shared hit=120 read=18450
-- Planning Time: 0.2 ms
-- Execution Time: 312.9 msRun it twice. If the second run shows hits instead of reads and runs much faster, the first run was slow because of a cold cache, not a bad plan. Other fields: dirtied and written appear when a query modifies pages.
What to say in the interview
"BUFFERS shows page counts per node: shared hit is cache, shared read is I/O. I compare runs to separate a cold cache from a bad plan, and I look for nodes reading far more pages than rows they return."
Common mistakes
- Judging a query from one cold-cache run.