Demystifying EXPLAIN (ANALYZE, BUFFERS): How to Diagnose Shared Hit Starvation

When troubleshooting query latency in high-traffic PostgreSQL environments, relying solely on overall execution time from standard logs frequently obscures the underlying root cause. Two queries with identical 250ms runtimes may represent fundamentally different resource profiles: one is bound by CPU cycles calculating hash aggregations in memory, while the other is stalling on physical disk reads due to buffer pool eviction.

Why Execution Time is an Incomplete Metric

Standard query execution timers measure wall-clock latency, which fluctuates based on concurrent CPU scheduling, network socket transmission delays, and operating system I/O queues. To understand deterministic query cost, database engineers must look directly at the memory cache interactions through EXPLAIN (ANALYZE, BUFFERS).

When enabled, the BUFFERS parameter prints detailed memory page accounting across three key dimensions:

• Shared Hit: Blocks retrieved directly from PostgreSQL's shared_buffers pool without touching the kernel page cache or physical storage.
• Shared Read: Blocks that missed shared_buffers and required an operating system read syscall, potentially causing NVMe disk I/O waits.
• Shared Dirtied & Written: Pages modified during transaction execution that triggered background writer or checkpoint disk flushes.

Diagnosing Cache Thrashing

If a query reads 120,000 blocks with 99% Shared Read on a table that is accessed multiple times per second, the buffer pool is being systematically thrashed. This not only degrades the query in question but aggressively evicts hot index pages for unrelated analytical transactions across the entire instance.

Remediating this pattern requires verifying whether a composite index can satisfy the filter predicate, or whether the query is triggering an unintended sequential scan due to mismatched datatype coercion in the WHERE clause.