One Column Added to a SELECT: 1,020 Page Reads Became 166,667

TL;DR An index-only scan reads the index and never touches the table. Add one column to the SELECT list that the index doesn’t carry and the engine has to visit the heap for every matching row, and past a certain row count the planner gives up on the index entirely and scans the table. The fix is to extend the index or shrink the projection; both cost something, and on Postgres the extended index still isn’t enough by itself.

A list-page query that has run for a year:

1

SELECT status, created_at FROM orders WHERE customer_id = 42;

orders has a composite index on (customer_id, status, created_at). Every column the query touches is in it, so the engine walks the index and never opens the table. Then a feature request: show the order total on the list. The diff is one column.

1

SELECT status, created_at, total_cents FROM orders WHERE customer_id = 42;

Here are both, run warm against a 5-million-row orders table (1.3 GB heap, 194 MB index) on PostgreSQL 18. Customer 42 has 266 orders.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

-- before
Index Only Scan using idx_orders_cust_status_created on orders
 (actual time=0.046..0.093 rows=266 loops=1)
 Index Cond: (customer_id = 42)
 Heap Fetches: 0
 Buffers: shared hit=34
Execution Time: 0.134 ms

-- after: one column added
Bitmap Heap Scan on orders (actual time=0.071..0.472 rows=266 loops=1)
 Recheck Cond: (customer_id = 42)
 Heap Blocks: exact=265
 Buffers: shared hit=270
 -> Bitmap Index Scan on idx_orders_cust_status_created
 Index Cond: (customer_id = 42)
Execution Time: 0.519 ms

The plan node changed shape, 34 buffers became 270, and the query is four times slower. Nobody notices. Half a millisecond is invisible on any dashboard, and this is where most write-ups of covering indexes stop, with a slowdown that doesn’t hurt.

Customer 777 is a marketplace seller with 200,239 orders interleaved over the same year. Same two queries:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

-- before
Index Only Scan using idx_orders_cust_status_created on orders
 (actual time=0.052..16.070 rows=200239 loops=1)
 Heap Fetches: 0
 Buffers: shared hit=1020
Execution Time: 22.440 ms

-- after: one column added
Gather (actual time=0.388..160.668 rows=200239 loops=1)
 Workers Planned: 2
 Workers Launched: 2
 Buffers: shared hit=14884 read=151783 written=273
 -> Parallel Seq Scan on orders (actual time=0.363..151.954 rows=66746 loops=3)
 Filter: (customer_id = 777)
 Rows Removed by Filter: 1599920
Execution Time: 167.623 ms

The index is gone from the plan. Three workers read all 166,667 pages of the table and threw away 4.8 million rows to keep 200 thousand. Reads went from 1,020 pages to 166,667, and written=273 means the scan pushed dirty pages out of a 128 MB buffer pool to make room, which is the part the other queries on the box will feel.

The reflexive fix is already forming: add total_cents to the index. That is what shipped, and it’s covered below, but it isn’t free and on Postgres it isn’t sufficient. The other reflex, “only select what you need,” is correct and ignored in practice, because the ORM emits SELECT * unless someone tells it otherwise. What follows is why the plan flips, why Heap Fetches: 0 is a claim about this moment and not about the index, and what each fix costs.

Why the planner walks away from the index

An index entry knows the row’s location in the heap. It doesn’t know total_cents. Once the query needs a column the index doesn’t carry, every matching entry becomes a heap page visit, and the cost of the query stops being “how many index entries match” and becomes “how many distinct heap pages hold those rows.”

For customer 42 that’s 265 pages, so a bitmap scan over the index is still the cheapest route. For customer 777 the arithmetic changes. The 5 million rows sit about 30 to a page, and one in every 25 belongs to customer 777, which puts one of their rows on nearly every page in the table. Whether the engine reaches those pages through the index or by reading the file front to back, it touches 166,667 pages either way, and the sequential read is the cheaper way to do it. With enable_seqscan = off the planner’s fallback was a parallel bitmap heap scan that took 292 ms, touched the same 167 thousand pages, and went lossy because the bitmap outgrew the 4 MB work_mem. The planner’s choice was right. The query is just expensive now, in a way it wasn’t when the index alone could answer it.

MySQL’s optimizer took the other branch on the same data (2 million rows, InnoDB, MySQL 8.4). It stayed on the index and paid per row:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

-- 106 rows, covered
-> Covering index lookup on orders using idx_orders_cust_status_created (customer_id=42)
 (cost=11.2 rows=106) (actual time=0.0779..0.091 rows=106 loops=1)

-- 106 rows, one column added
-> Index lookup on orders using idx_orders_cust_status_created (customer_id=42)
 (cost=90 rows=106) (actual time=0.182..0.339 rows=106 loops=1)

-- 80,115 rows, covered
-> Covering index lookup on orders using idx_orders_cust_status_created (customer_id=777)
 (cost=15515 rows=151198) (actual time=0.141..12.8 rows=80115 loops=1)

-- 80,115 rows, one column added
-> Index lookup on orders using idx_orders_cust_status_created (customer_id=777)
 (cost=86898 rows=151198) (actual time=7.93..471 rows=80115 loops=1)

The tree format names the difference outright: Covering index lookup becomes Index lookup. Each of the 80,115 secondary-index entries carries the primary key, and each one now costs a second descent into the clustered index to fetch total_cents. Thirteen milliseconds became 471. And these are warm numbers. The first time the 106-row uncovered query ran against a cold buffer pool it took 259 ms, because 106 scattered heap pages had to come off disk. The cold number is the one the p99 sees.

Where the ORM comes in ActiveRecord needs .select(:status, :created_at), Django needs .only(...), Prisma needs an explicit select block; without them every one of these frameworks fetches every column, and no index covers SELECT * on a real table. The generated SQL is the contract with the index, not the method call. On the handful of queries that carry the most traffic, the generated statement is worth reading once.

Heap Fetches: 0 is a property of the moment

The Postgres version of this story has a second act that has nothing to do with the SELECT list. A Postgres index carries no visibility information. It cannot tell whether the row an entry points to is visible to the current transaction, so an index-only scan consults the visibility map, a bitmap with one bit per heap page that says “every tuple on this page is visible to everyone.” Pages with the bit set are skipped. Pages without it get a heap fetch to check.

Any write to a page clears its bit. Here is the covered query for customer 42, the one at 0.134 ms with zero heap fetches, after an update to 84 of their orders that touched notes, a column that isn’t in the index and isn’t in the query:

1
2
3
4
5

Index Only Scan using idx_orders_cust_status_created on orders
 (actual time=0.071..0.442 rows=266 loops=1)
 Heap Fetches: 168
 Buffers: shared hit=350
Execution Time: 0.442 ms

The node still says Index Only Scan. It went to the heap 168 times. Nothing about the query or the index changed; a write to an unrelated column on the same pages did it. VACUUM restores the bits (168 fetches dropped to 49 after one pass and to 0 after a second), which is why autovacuum health and index-only scan performance are the same problem on a busy table.

Warning Heap Fetches: 0 in a plan is a statement about the visibility map right now, not about the index. The same query can show zero at 09:00 and thousands at 09:05 after a batch update, with the node name unchanged. When an index-only scan is slower than it should be, read the Heap Fetches line before touching the index definition, and check pg_stat_user_tables for when the table was last vacuumed. The pg_visibility extension will tell you exactly how many pages have lost their bit.

This is also why the “add the column to the index” fix was not the end of the incident. The index that shipped for customer 777 came up at 29.5 ms, an 80 percent recovery, but with Heap Fetches: 4101, because a 100,000-row update to an unrelated range of orders had cleared the bit on 4,101 pages that also happened to hold seller 777’s rows. The next autovacuum cleared it. Coverage got the plan back; vacuum got the performance back.

Extend the index, or shrink the projection

Two fixes hold up, and they fail in different places.

Extending the index puts total_cents in the leaf entries so the query never needs the heap. Postgres has INCLUDE for this since version 11: the column rides along in the leaf pages without becoming part of the B-tree ordering, so it costs nothing on the search path.

1
2
3

CREATE INDEX idx_orders_cust_status_created_incl
 ON orders (customer_id, status, created_at)
 INCLUDE (total_cents);

MySQL has no INCLUDE. The equivalent is to append the column as a regular key column, (customer_id, status, created_at, total_cents), and the optimizer picks the covering form when it’s there; on the 80,115-row case that brought 471 ms back to 10.9 ms. InnoDB secondary indexes already carry the primary key at the leaves, so SELECT id is covered for free on any secondary index, which is worth remembering before adding id to one.

The cost is on the write side, and it’s specific. The Postgres INCLUDE index grew from 194 MB to 237 MB for one four-byte column across 5 million rows. More to the point, total_cents is a column that changes: orders get amended, refunded, partially shipped. Every update to it now has to write a new entry into this index, and on Postgres an update that changes any indexed column can never take the heap-only (HOT) path, so it also writes an entry into every other index on the table. The column that made the list page fast is the column the order-editing path pays for on every save. On a table taking thousands of writes a second, that trade deserves a number before it ships, not after.

Shrinking the projection avoids all of that. The list view keeps the covered query, and the total is fetched on demand for the one order the user expands, or the list-view columns are frozen as a contract and the index matches the contract. This is the cheaper fix when the new column is decorative and the harder one to hold, because the next feature request also wants one more column. Whichever way it goes, naming the index after the query it serves (idx_orders_list_view) and pointing a comment on the query back at the index makes the dependency visible to the next person, or the next assistant, asked to “just add a column to the list page.” Re-checking that a projection is still inside its index is not a step either of them takes unprompted.

For the queries that carry real traffic, the EXPLAIN (ANALYZE, BUFFERS) output belongs in the pull request next to the SQL. The Buffers line is the one to compare: 34 pages to 270 is a shrug, 1,020 to 166,667 is the incident, and neither shows up in a diff that adds one column.