Buffer Hit Versus Disk Read: Why the Same Query Is Fast the Second Time
Every page a query needs is either already in the buffer pool, which is a hit, or has to be fetched from storage, which is a miss. A hit costs roughly a microsecond and a miss roughly a hundred, so two runs of the identical query can differ by a factor of fifty with nothing changed but what happened to be cached. PostgreSQL 18 prints buffer counts with EXPLAIN ANALYZE by default, so you can see hits and reads directly, and knowing the difference stops you from declaring a query fixed when all you did was warm the cache.
Questions this Concept answers
Why can two runs of the same unchanged query differ in time by a factor of fifty?
J
jeremy
Video
How Shared Buffers Cache Data Pages in PostgreSQL
Shared buffers are a fixed-size region of shared memory that PostgreSQL allocates at server start so that backend processes read and write data pages in RAM instead of going to files on disk for ever…