The Buffer Count

Stop obsessing over milliseconds. If you’re tuning a query based on execution time alone, you’re essentially gambling on whether your cloud provider's noisy neighbor is currently hammering the CPU or if the OS cache felt like being generous that second.

Execution time is a liar. It’s a flightly, non-deterministic metric influenced by everything from disk latency to thermal throttling. If you want to actually know how hard your database is working, you have to look at the Buffer Count.

The Illusion of "Fast"

We’ve all been there. You run a query, it takes 800ms. You run it again immediately, and it takes 12ms. You pat yourself on the back, thinking the database "warmed up," and move on.

But the workload hasn't changed. The database still had to do the same amount of logical work to find your rows; the only difference is that the second time, the data was sitting in the shared_buffers (Postgres's internal RAM cache) instead of on a "slow" SSD.

When you look at time, you're measuring the hardware's mood. When you look at buffers, you're measuring the query's efficiency.

The Magic Command: EXPLAIN (ANALYZE, BUFFERS)

Most developers stop at EXPLAIN ANALYZE. That’s a mistake. You need to invite the BUFFERS flag to the party.

-- The standard way (don't stop here)
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'human@example.com';

-- The "I actually want to fix this" way
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE email = 'human@example.com';

When you add BUFFERS, Postgres spits out a few extra lines that tell you exactly how many 8KB pages (blocks) it had to touch to give you your result.

Decoding the Output

Let’s look at what a real-world buffer output looks like:

Seq Scan on users  (cost=0.00..35.50 rows=1 width=102) (actual time=0.042..0.045 rows=1 loops=1)
  Filter: (email = 'human@example.com'::text)
  Rows Removed by Filter: 2499
  Buffers: shared hit=31
Planning:
  Buffers: shared hit=4
Execution time: 0.098 ms

See that Buffers: shared hit=31? That is the ground truth.
* Shared Hit: The data was already in the Postgres buffer cache.
* Shared Read: Postgres had to go to the OS or disk to get it.
* Shared Dirtied/Written: You’re making the database do work to keep the data consistent on disk.

In Postgres, data is stored in 8KB blocks. So, shared hit=31 means the database touched roughly 248KB of data.

Why This is the Only Metric That Matters

Imagine you’re reviewing a PR. Developer A shows a query taking 5ms. Developer B shows a query taking 50ms. You might think A is the winner.

But look at the buffers:
* Query A: shared hit=5000 (40MB of data processed)
* Query B: shared hit=10 (80KB of data processed)

Query A is a disaster waiting to happen. It feels fast right now because it’s entirely in cache. But as soon as your dataset grows or the cache gets evicted, that 5,000-block scan is going to turn into 5,000 disk I/O operations. Query B is objectively more efficient because it’s doing less work to get the same answer.

The rule is simple: Fewer buffers = Less CPU = Less I/O = Better Query.

Spotting the "Silent Killer" Index Scan

Sometimes an index scan looks healthy, but the buffer count reveals it's actually dragging the system down. This happens often with unclustered indexes or bloated tables.

EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM orders WHERE status = 'pending';

Output:

Index Scan using idx_orders_status on orders (cost=0.42..150.22 rows=100 width=50)
  Index Cond: (status = 'pending'::text)
  Buffers: shared hit=1200

Wait, 1,200 hits for 100 rows? That’s 12 pages per row. This tells me my index might be fragmented, or my table is so bloated that Postgres is jumping all over the disk to find the actual row data (the "heap") after finding the entries in the index. If I just looked at the 0.5ms execution time, I’d never know this query is a resource hog.

Measuring the Impact of an Optimization

Let’s say I add a covering index (an index that includes the columns I’m selecting) to stop the database from having to look at the heap.

CREATE INDEX idx_orders_status_covering ON orders(status) INCLUDE (order_id, created_at);

EXPLAIN (ANALYZE, BUFFERS) 
SELECT order_id, created_at FROM orders WHERE status = 'pending';

The result:

Index Only Scan using idx_orders_status_covering on orders ...
  Buffers: shared hit=4

From 1,200 to 4. That is a massive win. Even if the execution time only dropped by a few milliseconds, you’ve just freed up a huge amount of I/O bandwidth for the rest of your application.

Pro-Tip: The "Track I/O Timing" Setting

If you really want to see how much the disk is hurting you, turn on track_io_timing. It’s usually off by default because it adds a tiny bit of overhead, but it's invaluable for debugging.

SET track_io_timing = on;
EXPLAIN (ANALYZE, BUFFERS) SELECT ...

This adds I/O Timings: read=... write=... to your output. If your shared reads are high and your I/O timing is through the roof, you don't have a query problem—you have a memory problem (your shared_buffers are too small) or a slow disk problem.

Summary

Next time you’re tuning Postgres:
1. Ignore the clock. It’s a fickle friend.
2. Run `EXPLAIN (ANALYZE, BUFFERS)`.
3. Count the blocks. Aim for the lowest shared hit + shared read possible.
4. Remember the math. Each unit is 8KB.

A query that touches 10 blocks will always be more scalable than a query that touches 1,000 blocks, regardless of what the "Actual Time" says in your dev environment. Focus on the work, not the clock.