Stop Running EXPLAIN ANALYZE Without BUFFERS

When debugging a slow query in PostgreSQL, almost every developer defaults to running EXPLAIN ANALYZE. We look at the bottom line: Execution Time: 1.42 ms. The response looks blazing fast on our local machine or staging environment, so we close the ticket and ship the code to production. A few days later, under heavy traffic, database CPU spikes to 100%, disk read IOPS hit their ceiling, and that exact query grinds the entire system to a halt.

The reason is simple: raw execution time in a quiet environment is often a lie created by PostgreSQL's memory cache. If you want to know how a query will actually behave under real production load, you should always run EXPLAIN (ANALYZE, BUFFERS).

The Warm Cache Trap

PostgreSQL uses an internal memory area known as shared_buffers. When a query needs a table or index page, the database checks if that 8 KB page already lives in memory. If it does, fetching it requires only a few CPU cycles and in-memory pointer lookups—taking mere microseconds. If it does not, PostgreSQL must request the block from the operating system page cache or perform a physical disk read, which is orders of magnitude slower.

When you test a query repeatedly during development, you are almost always testing against a warm cache. A query that scans 50,000 distinct data pages might execute in 15 ms simply because all 50,000 blocks happen to be sitting in memory. In production, where hundreds of competing queries constantly evict cold pages, that same query suddenly has to read thousands of blocks from disk, and that 15 ms query turns into a 6,000 ms bottleneck.

Execution time measures environmental latency, whereas buffer usage measures the fundamental mechanical work your query forces the engine to perform.

Decoding the Buffer Output

Enabling BUFFERS adds a crucial line underneath each execution node in the plan tree:

EXPLAIN (ANALYZE, BUFFERS) 
SELECT o.id, o.total_amount, c.email
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at >= '2026-01-01'
  AND o.status = 'completed';

The resulting plan reveals exactly what PostgreSQL touched:

->  Hash Join  (cost=1420.00..5890.10 rows=4120 width=45) 
               (actual time=0.820..4.120 rows=4150 loops=1)
      Buffers: shared hit=18200 read=450

Here is what those metrics mean in practice:

• shared hit: The number of 8 KB blocks found directly in PostgreSQL's shared_buffers. Multiply this number by 8 KB to see how much memory your query churned through. For example, 18,200 hits equals ~145 MB of memory scanned.
• shared read: The number of 8 KB blocks that were not in shared buffers and had to be fetched from OS cache or disk storage. High read counts indicate cold cache vulnerabilities or excessive sequential scans.
• shared dirtied: The number of blocks modified by the query that will eventually need to be flushed to disk by checkpointing or background writers.
• shared written: The number of dirty blocks this specific backend had to write to disk immediately.
• temp read / temp write: Data spilled to temporary disk files because the operation exceeded the allocated work_mem (frequent during large sorts or hash joins).

The Hidden Nested Loop Disaster

A classic trap that BUFFERS exposes is the runaway Nested Loop. Consider a query that joins two tables using an index. In development, the query plan might look harmless:

->  Nested Loop (actual time=0.040..3.100 rows=500 loops=1)
      Buffers: shared hit=45200
      ->  Seq Scan on promotions (actual time=0.010..0.020 rows=500 loops=1)
      ->  Index Scan using idx_orders_promo on orders (actual time=0.004..0.005 rows=1 loops=500)
            Buffers: shared hit=45180

Without BUFFERS, you see actual time=3.100 ms and think your query is perfectly healthy. With BUFFERS, you immediately notice shared hit=45200 (~361 MB) just to return 500 rows!

The inner index scan executed 500 times (loops=500), traversing several B-Tree branch and leaf pages on every single loop. While your local RAM handled those 45,000 lookups in 3 milliseconds, running this query concurrently across fifty web workers in production will saturate memory bandwidth and trash your buffer cache, evicting critical pages for other services.

How to Use Buffer Metrics in Daily Workflow

To build queries that scale reliably under real production constraints, adopt these three habits:

1. Treat buffer count as the primary cost function: Execution time fluctuates based on machine load and disk caching. Total buffer count (hit + read) remains virtually constant for a given dataset and plan. Optimize to minimize total blocks touched.
2. Look for Index-Only Scan opportunities: If an Index Scan generates high buffer hits because it constantly visits table heap pages, check whether you can add columns to the index with the INCLUDE clause to achieve an Index Only Scan.
3. Watch out for temp buffers: If temp read or temp written appear in your plan, your query is spilling to disk because of an undersized work_mem. Adjusting work_mem for that specific session or adding filtering can eliminate severe I/O penalties.

Raw execution time tells you how fast a query ran under ideal laboratory conditions. Buffer statistics tell you the physical cost it imposes on your database hardware. Default to EXPLAIN (ANALYZE, BUFFERS) on every investigation, and you will catch scalability bottlenecks long before your users do.

Sources

1. postgresql.org