Follow a Read Through the Engine

The client sends 32 bytes of text. The lexer turns it into tokens, the parser into a tree, and the binder resolves names against the catalog: users is a relation, id is its first column (integer, NOT NULL, primary key), and users_pkey is a unique B+ tree on it. The catalog lookups are themselves reads of catalog pages, which are the hottest pages in the system and never miss in practice. The planner then compares a sequential scan — every page of users, at 1,200 pages roughly 1,200 sequential reads — with an index scan: height of users_pkey (3) plus one heap page, four random reads. For an equality predicate on a unique key the index wins by two orders of magnitude, and the plan is Index Scan using users_pkey on users (cost=0.43..8.45 rows=1).

Notice what the plan does not say: whether those four pages are in memory. The cost model assumes some fraction of pages are cached (effective_cache_size) but the plan is the same either way. The plan describes work; the pool decides its price.

The executor asks the buffer manager for page 1 of users_pkey: the root. In it, ~200 separator keys; a binary search over them (about 8 comparisons) picks the child whose range contains 42. That child is an internal page — the same shape one level down — and its binary search picks a leaf. The leaf holds (key → tuple id) pairs in key order; the entry for 42 says (1271, 14): heap page 1,271, slot 14. Three page requests, three binary searches, and the index is done. It has not read the row and it does not know whether the row is visible; it has produced an address.

Each request is the same call: ReadBuffer(users_pkey, N). The buffer manager hashes (relation, block) into the page table; a hit pins the frame and returns it in about a microsecond; a miss picks a victim by clock-sweep, evicts it (writing it if dirty), reads 8 KB from the file, and returns the frame after roughly a hundred microseconds on an SSD. The root and internal pages are shared by every lookup on the table and are essentially never evicted; the leaf is one of ~6,000 for a 1.2-million-row table and is resident only if some recent lookup landed on it.

The buffer pool is not a stage the query passes once; it is the door every page request goes through. The executor code that walks the tree does not know whether page 388 came from RAM or from the SSD — it asked, and it got a pinned frame. This separation is what keeps the executor simple and what makes latency a property of the running system rather than of the plan. It also means the pool's replacement decisions from a minute ago — a scan that flooded it, a checkpoint that cleaned it — are visible in this query's latency.

The interactive above lets you set the pool state: cold (nothing resident), typical (index upper levels resident), warm (everything resident). Same SQL, same plan, same four pages; only the hit/miss column changes, and with it the latency by two orders of magnitude.

The tuple id names heap page 1,271. The buffer manager fetches it — the page least likely to be resident, since heap pages are spread over the whole table and only the recently touched ones are in the pool. The executor reads entry 14 of the page's slot directory, which gives the byte offset of the tuple within the page (see Slotted Pages). At that offset sits the tuple header: transaction ids that created and (possibly) deleted this version, and flag bits. Visibility is decided here — was the creating transaction committed before my snapshot, and is there no committed deletion? — and only then are the columns decoded using the catalog's column layout, honouring alignment padding and the NULL bitmap.

Then the pins are released. The heap page and the leaf page become evictable again; the root stays pinned by nobody yet survives, because its usage count is at the maximum and the clock hand never catches it at zero. The row is sent to the client. Nine stages, four page requests, one row.

Add it up with round numbers: ~20 µs of CPU for parse, plan and executor setup; ~1 µs per pool hit; ~100 µs per SSD miss. Warm cache: 4 hits, ~24 µs. Typical: 2 hits and 2 misses, ~220 µs. Cold: 4 misses, ~420 µs. On a network round trip of 500 µs the warm case is invisible to the client and the cold case doubles the request time; on a spinning disk (10 ms per miss) the cold case is 40 ms and the warm case is still 24 µs — a ratio of 1,600. Every intuition about "the database is fast" is an intuition about the warm case.

The lesson generalises. A join that touches 10,000 rows via an index is 10,000 heap page requests; whether they hit is the difference between a 30 ms query and a 1 s query. The planner estimates this with random_page_cost and effective_cache_size; the plan's BUFFERS output measures it after the fact.

EXPLAIN (ANALYZE, BUFFERS) prints, for each plan node, Buffers: shared hit=H read=R — H page requests satisfied from shared_buffers, R that had to be read (from the OS cache or the disk; PostgreSQL cannot distinguish, which is the double-buffering blind spot). A point lookup that reports shared hit=3 read=1 is the "typical" row above with only the heap page missing. dirtied and written appear when the query itself had to write pages. Run the same query twice and watch read fall to 0: that is the pool warming, and it is why the second run of any benchmark is faster.

Index-only scans add one more page: the visibility map, which lets the executor skip the heap page when the whole heap page is known to be all-visible. If the table has been updated since the last VACUUM, the bit is clear, the heap page is fetched anyway, and the plan shows Heap Fetches: 1 — an index-only scan that was not.