Performance Internals: Why the Slow Node Is Slow

The practical lessons stop at the plan node. The internals lessons continue downward until the cost is a count of 8 KB pages and a fraction of them that missed the buffer pool. Three chains cover almost everything: a slow query descends through the plan to pages on storage; a fast index query descends through the tree to the buffer pool; a COMMIT descends through the log to a dirty page and its recovery. Each arrow below is one lesson.

The reason this works is that every layer only ever asks the next one for pages. The executor asks the buffer pool for page 4,217; the buffer pool asks the file for it if it is not resident; the file asks the SSD. A query cannot be slow in a way that does not show up as page reads, CPU per row, or a fixed wait (fsync, lock). That is the whole diagnostic method: find which of the three grew.

Six branches, in the order you should check them. Page reads: the query returns 20 rows but touches 2,000 pages, because rows are wide, dead tuples pad the pages, or the rows are scattered — Buffers: far above rows returned; see Slotted Pages and UPDATE, DELETE and Dead Tuples. Buffer misses: the same pages, but no longer resident — shared read= high, hit ratio falling as the data grows; see The Buffer Pool and Buffer Replacement: LRU, Clock and Scan Resistance. Scan choice: Seq Scan with Rows Removed by Filter: 9,998,120 — either no usable index or a bad estimate; see Sequential Scan, Page by Page, The Planner: Enumerating Ways to Answer, Cost-Based Optimization.

Index behaviour: the index is used and still slow because each match is a random heap page, or unused because it is a hash index and the predicate is a range — Heap Fetches, idx_scan = 0; see B+ Tree Internals: Pages, Splits, Merges and Hash Index Internals. Join algorithm: a nested loop with loops=50000, or a hash join with Batches: 8; see Join Algorithms: Nested Loop, Hash, Merge. Sorting: Sort Method: external merge Disk: 184320kB — the sort spilled; provide the order from an index or raise work_mem; see Inside the Query Engine.

Index maintenance: every index is one more leaf page dirtied per row; random keys make that page a miss and then a flush. Inserting 900 rows of 128 bytes with a UUID index can write 450 pages — 3.6 MB for 115 KB of data, a write amplification above 30×. Metric: index count, idx_tup_insert; lesson B+ Tree Internals: Pages, Splits, Merges, Follow a Write Through the Engine. WAL: COMMIT is an fsync, ~200 µs on an SSD and 5–10 ms on a network volume; group commit amortises it across concurrent committers; checkpoints spike it. Metric: wal_sync_time, checkpoints_req; lesson Write-Ahead Logging, Crash Recovery.

Contention: writes to the same row serialise on its lock; a hot counter row is a queue, and under MVCC it also grows a version chain that VACUUM must trim. Metric: wait_event_type = Lock, deadlocks, n_dead_tup; lesson The Lock Manager, MVCC Internals: Version Chains and Snapshots. Compaction (LSM engines): writes are cheap because the work is deferred; when compaction falls behind, L0 piles up, reads probe every file, and the engine stalls writers. Metric: pending compaction bytes, L0 file count, stall counters; lesson Compaction: The Merge That Pays for Cheap Writes, Write, Read and Space Amplification. Storage: random 8 KB writes and fsyncs are paid at the device’s latency, sequential writes at its bandwidth; metric: iostat await, pg_test_fsync; lesson How Is Database Data Physically Stored?, LSM Trees: Why Some Engines Favour Writes.

A lookup by order_id on a 60 GB table with 32 GB of buffer pool: the B+ tree has height 4, the three internal levels are resident, the leaf usually is, and the heap page often is. Buffers: shared hit=5 read=0, 0.3 ms. The table grows to 130 GB. The tree is still height 4 — 200⁴ addresses 1.6 billion rows — but the leaf and the heap page are now resident perhaps half the time. Buffers: shared hit=3 read=2, 0.3 ms + 2 × 100 µs. Under a 200-row IN (…) list: 400 misses, 40 ms. Under a 5,000-row nested loop: a second.

Nothing in the plan changed. The metric that moved is the hit ratio, and the mechanisms are The Buffer Pool (which pages stay) and B+ Tree Internals: Pages, Splits, Merges (which pages the lookup needs). Fixes in order of cost: a covering index so the heap page is not read at all; clustering so consecutive orders share heap pages; partitioning old orders away so the hot leaves are a smaller set; RAM.

A service inserts events with a random UUID primary key and two secondary indexes, 2,000 rows per second. Each row dirties one heap page (resident: inserts append) and three index leaf pages (random: each is resident with probability equal to the hit ratio). At a 70% hit ratio, 0.9 leaf pages per row are flushed before another row lands on them — 1,800 random 8 KB writes per second plus the heap flushes plus the WAL. The disk sees 15 MB/s of writes for 256 KB/s of data: write amplification ≈ 60×. As the indexes grow past the pool the hit ratio falls and the number climbs, which is what “inserts slow down as the day goes on” looks like from the inside.

The internals fix is to change the shape of the writes: a time-ordered key (ULID, uuidv7, a sequence) makes every insert hit the same right-most leaf, which stays resident and absorbs hundreds of rows per flush; an LSM engine (LSM Trees: Why Some Engines Favour Writes) removes the dirty page entirely and pays compaction instead — the playground in The Database Internals Playground lets you watch the amplification move between the two.

InnoDB tables are clustered on the primary key, so a primary-key lookup is one B+ tree descent with the row in the leaf — no separate heap page — while a secondary-index lookup is two descents (secondary → primary key → clustered tree). A random UUID primary key is therefore worse than in PostgreSQL: it scatters the rows themselves, not just an index. The buffer pool metric is Innodb_buffer_pool_reads (misses) against Innodb_buffer_pool_read_requests; the redo log fsync count is Innodb_os_log_fsyncs, tuned with innodb_flush_log_at_trx_commit; lock waits are Innodb_row_lock_waits and Innodb_row_lock_time_avg; the change buffer hides some secondary-index maintenance by deferring it.