Your Index Only Scan Is Lying: Covering Indexes and the Visibility Map

#postgres #database #performance #sql

Covering Indexes Explained: Why "Index Only Scan" Still Hits the Heap

You ran EXPLAIN ANALYZE. You saw Index Only Scan in the output. You assumed the query never touched the table. Case closed, right?

Then you looked one line lower. Heap Fetches: 4,827. Your "index only" scan hit the heap almost five thousand times. Not so index-only after all.

This trips up a lot of developers. The plan node says one thing, the runtime metric says another. I won't rehash what an index is or how B+ trees work (I've touched on index fundamentals before). This post is about the covering trick and the gotcha that makes it unreliable on write-heavy tables.

⚡ What covering means and the fetch it skips

A covering index is one that contains every column a query needs. SELECT columns, WHERE columns, ORDER BY columns. All of them. When the engine can answer entirely from the index, it skips the table (the heap).

That heap visit is expensive. Random I/O. The index gives you a pointer to a row, then the engine jumps to that heap page, pulls the row, extracts the column. Ten thousand rows means ten thousand random fetches.

A covering index kills that step. Everything lives in the index's leaf pages already.

In Postgres 11+, you get the INCLUDE clause for this:

-- Query: SELECT amount FROM orders WHERE customer_id = 42 AND status = 'shipped';
CREATE INDEX idx_orders_cover ON orders (customer_id, status) INCLUDE (amount);

customer_id and status are key columns. They live in both internal and leaf pages, they're searchable, and they determine sort order. Column order still matters here. The leftmost prefix rule applies to keys exactly as it does for any composite index.

amount is a payload column. It's stored only in leaf pages. You can't filter on it. You can't sort by it. It's just along for the ride so the engine doesn't have to go back to the heap. Think of it as a stowaway.

InnoDB doesn't have INCLUDE. You'd tack amount on as a trailing key column. But InnoDB gives you something free: every secondary index already carries the primary key columns in its leaf entries. PK columns are always "covered" without you doing anything.

🔑 The visibility map problem

Here's the part most people miss. Postgres can't actually guarantee a pure index-only scan even with a perfect covering index.

Why? MVCC. Every row has visibility rules: which transactions can see it, whether it's been deleted but not yet vacuumed. That info lives on the heap page, not in the index. So Postgres needs a way to answer "is this tuple visible to my transaction?" without going to the heap.

The answer is the visibility map. A bitmap with one bit per heap page. When VACUUM confirms every tuple on a page is visible to all current transactions, it sets that page's all-visible bit. During an index-only scan, Postgres checks the VM bit:

Bit set → return data from the index. No heap access.
Bit not set → fetch the heap page anyway to check visibility.

And that second case is your Heap Fetches number. Every recently-modified page that VACUUM hasn't caught yet forces a heap visit. On a write-heavy table where pages are constantly dirtied, those bits get cleared faster than VACUUM can set them. Your covering index is technically correct but practically useless.

Index Only Scan using idx_orders_cover on orders
  Index Cond: (customer_id = 42 AND status = 'shipped')
  Heap Fetches: 42
  Buffers: shared hit=15
  Planning Time: 0.08 ms
  Execution Time: 1.2 ms

(illustrative numbers)

Heap Fetches: 0 means you got a true index-only scan. Any positive number means degraded pages forced heap access. The diagnostic is simple: run EXPLAIN ANALYZE, check Heap Fetches. If it's high relative to rows returned, VACUUM isn't keeping up.

But MySQL doesn't have this problem. InnoDB stores row versions inline using undo logs, and secondary indexes point to the clustered index via the PK. No separate visibility check needed. When MySQL's EXPLAIN shows Using index in the Extra column, it really does mean "table not accessed."

Don't confuse that with Using index condition. Different thing entirely:

+----+------+---------------+---------+------+-------------+
| id | type | key           | key_len | rows | Extra       |
+----+------+---------------+---------+------+-------------+
|  1 | ref  | idx_status_amt| 5       |  120 | Using index |
+----+------+---------------+---------+------+-------------+

(illustrative)

Using index = covering, query answered from index alone. Using index condition = Index Condition Pushdown (ICP), where a filter is pushed to the storage engine to evaluate against the index, but the table is still fetched for non-indexed columns. Similar names, different behaviour.

📌 Key takeaways

A covering index holds every column the query needs, skipping heap fetches entirely. In Postgres, INCLUDE lets you add payload columns that aren't searchable or sortable.
Index Only Scan in the plan doesn't guarantee zero table access. Check Heap Fetches in EXPLAIN ANALYZE. That's the real metric. High numbers mean VACUUM hasn't caught up with writes.
In InnoDB, secondary indexes carry the PK for free. Using index means covering; Using index condition means ICP. They aren't the same.
Wide covering indexes cost you: bigger pages, more memory pressure, slower writes. Worth it for hot read paths on stable tables. Not worth it when the visibility map can't stay ahead.

If you're tuning a slow read query and EXPLAIN already shows an index scan, adding one or two columns via INCLUDE is often the cheapest win. But only if your table is vacuumed regularly. Otherwise you're paying index maintenance cost for a benefit you never get.

Somewhat related — API gateways solve a similar "one extra hop" problem at the network layer, where caching at the edge saves the round-trip to the origin the same way a covering index saves the trip to the heap.