---
title: "SQL Query → Disk Read"
description: "From a query to bytes on disk: parser, plan, B+ tree index, and the page cache."
source: https://vizlearn.app/capstones/projects/sql-to-disk
---

# SQL Query → Disk Read

SQL query execution turns a requested result into a plan, page requests, and returned rows. An index narrows the pages to visit; the database buffer pool determines whether those visits need storage reads. In this model, the same query returns Mira after three cold page reads, then returns Mira again with zero additional reads.

The useful distinction is between asking for a page and fetching it from storage. You can ask for the same page twice while fetching it only once. A buffer pool keeps page copies in memory so later requests can reuse them. It does not keep a ready-made answer to every SQL statement.

## How a SQL Query Reaches Storage

The query is `SELECT name FROM users WHERE id = 42;`. Parsing identifies the projection (`name`), relation (`users`), and predicate (`id = 42`). Binding resolves those names against the table schema. The illustrated planner then hands an Index Scan plan to the executor. A real planner compares estimated costs and may prefer a sequential scan, especially for a small table. This example supplies the index plan so you can follow its page dependencies. [PostgreSQL's query-path overview](https://www.postgresql.org/docs/current/query-path.html) describes the parser, rewrite, planner, and executor boundaries.

The secondary index stores each `id` together with a row locator: a heap-page address and a slot within that page. It does not store `name`. Finding key 42 therefore identifies where to look next; it has not yet produced the requested value. The executor must retrieve the heap tuple and project its name. This distinction follows the [separation between secondary indexes and heap storage](https://www.postgresql.org/docs/current/indexes-index-only-scans.html).

Every page access passes through the database buffer pool, including accesses to the index root and leaf. A **buffer hit** supplies an existing memory copy. A **buffer miss** requests a page from storage, then installs the returned copy in a free buffer frame. The source page remains in storage. Only after the page is available can the executor inspect its contents.

The animation calls the lower box “Storage.” A database read request does not prove that a physical disk or SSD was accessed: an operating-system cache may satisfy it. PostgreSQL's [I/O-statistics documentation](https://www.postgresql.org/docs/current/monitoring-stats.html) makes that distinction explicit. The displayed counts are modeled page fetches below the database buffer layer, not measurements from a database server.

## SQL Query → Disk Read, Step by Step

The default **Run twice** scenario starts with an empty four-frame buffer. It prepares one plan, executes it, and then executes that same plan again while retaining the buffer pages. The data stays fixed throughout.

| Heap page | Slot 1 | Slot 2 |
| --- | --- | --- |
| H0 | `7 · Ada` | `63 · Gus` |
| H1 | `12 · Bo` | `40 · Eli` |
| H2 | `23 · Cy` | `91 · Hana` |
| H3 | `35 · Dee` | `42 · Mira` |

The root page R has separator 40. Keys below 40 go to L0; keys equal to or above 40 go to L1. L1 contains keys 40, 42, 63, and 91 with their heap locators. The physical order of heap rows differs from the index order.

1. **Prepare the query.** Parse and bind `SELECT name FROM users WHERE id = 42;`. The illustrated Index Scan needs the id index and a heap fetch because name is absent from the index payload.
2. **Fetch root R.** The first page request misses the empty buffer. Storage supplies the whole root page, and a buffer frame retains it. The read count becomes 1. Comparing 42 with separator 40 selects L1.
3. **Fetch leaf L1.** This page also misses. After the second storage read and buffer fill, the executor finds `42 → H3 / slot 2`. The name is still unknown to the executor.
4. **Fetch heap page H3.** The third request misses. Storage transfers the page containing both `35 · Dee` and `42 · Mira`. The locator selects slot 2; projecting name returns the one-row result `Mira`. The first receipt records 3 requests, 0 hits, and 3 reads.
5. **Execute the plan again.** Reset the result and per-execution counters, retaining R, L1, and H3. Each of the same three requests now hits a resident frame. The executor still compares the root separator, searches the leaf, follows the locator, and projects the tuple. It returns `Mira` with 3 requests, 3 hits, and 0 reads.
6. **Compare receipts.** Query, plan, and result agree. Buffer residency is the changed condition. The two runs together performed 6 page requests and 3 storage reads; the second run's own read count is zero.

## Why a Warm SQL Query Can Avoid Storage Reads

For a completed execution in this model:

`page requests = buffer hits + storage reads`

The equation counts requests, not just distinct page identities. While a miss is waiting for its read, the final equality need not hold yet. The read is counted once in the storage-transfer beat; installing that page does not count it again.

| Scenario, using its suggested target | Page requests | Buffer hits | Storage reads | Result |
| --- | ---: | ---: | ---: | --- |
| Run twice, 42: first execution | 3 | 0 | 3 | Mira |
| Run twice, 42: repeat | 3 | 3 | 0 | Mira |
| Warm buffer, 42 | 3 | 3 | 0 | Mira |
| No index, 42 | 4 | 0 | 4 | Mira |
| Missing key, 43 | 2 | 0 | 2 | zero rows |

Warm buffer declares its preloaded pages as initial state. Their earlier loading cost is outside the current execution's receipt. Clear those pages before the repeat and its storage reads return to three. Keeping the query text unchanged does not itself preserve the pages.

An index changes the access path; caching changes the cost of satisfying each access. In a taller tree, an indexed lookup visits a page at each level, then a heap page for a matching tuple. A sequential scan visits every heap page. Which of those visits requires storage depends on residency. Exact page counts also omit costs such as CPU work and access locality, so fewer reads alone do not establish a universal latency ranking. The focused [Indexing lesson](https://vizlearn.app/cs/databases/indexing) treats access-path tradeoffs in more depth.

## Edge Cases

- **Missing key:** key 43 belongs in L1's range but is absent. Reading R and L1 establishes that absence. No locator is produced, so no heap-page request follows. An empty result is successful query execution.
- **Separator equality:** key 40 takes the right branch because the partition is `< 40` versus `≥ 40`. Keys 7 and 91 exercise the fixture's first and last stored keys.
- **No index:** all four heap pages are scanned, even when an early page contains a match. Try target 7: the match appears on H0, but the scan continues through H3.
- **Warm missing key:** only R and the candidate leaf need to be resident. Both requests hit, and the result is empty with no storage reads.
- **Buffer capacity:** this fixture needs at most four resident pages, so it never evicts one. A larger working set can lose the warm-repeat advantage through eviction; [page replacement](https://vizlearn.app/cs/os/page-replacement) covers that policy.
- **Target input:** the interface accepts integers 0–99. This is a visualization bound, not a SQL or database limit. The fixture has eight rows; all other valid targets return zero rows.

## Common Mistakes

**Treating a buffer miss as “row not found.”** The miss answers whether a page is resident. The executor has to inspect that page before it can decide whether a key or tuple qualifies.

**Returning an index locator as the projected column.** `H3 / slot 2` tells the executor where to fetch Mira's tuple. A covering index could carry name, but that is a different index payload; it is not the index shown here.

**Counting one read per row.** The storage transfer brings a whole page. H3 arrives with both rows even though only one supplies the result.

**Assuming zero storage reads means zero work.** The repeat still performs the plan's page requests and tuple processing. The buffer pool saves the storage fetches.

**Calling the read counter physical device I/O.** The model ends at the storage interface. Device access, operating-system caching, and measured time require additional evidence from the real system.

## A Note on Simplification

This is an explainer, not a substitute for formal database study or analysis of a production query. It uses a tiny secondary B+ tree, an unclustered heap, fixed visible rows, one equality predicate, and four buffer frames. It omits catalog/planning I/O, index metapages, changing MVCC visibility, locks, concurrent queries, eviction, writes, WAL, prefetch, OS/device caches, and real cost estimation. Page capacities and row slots are illustrative. The counts belong to this fixture; PostgreSQL and other engines may do additional work or choose another plan.