A few years back I was tuning the same reporting query on two systems during a migration rehearsal. The query aggregated open orders by status and total, hitting a 38-million-row orders table. On MySQL it took about 900 milliseconds because it scanned a status index and then chased every row back into the clustered primary key. I added a two-column covering index, and the runtime dropped to 35 milliseconds. EXPLAIN said "Using index", the table was never touched, and I moved on.
A week later I built the equivalent index on the PostgreSQL copy of the same table and expected the same result. EXPLAIN ANALYZE did show an Index Only Scan, which looked right at first glance. But it also showed "Heap Fetches: 38,412", one fetch for nearly every row the query returned, and the query ran in 620 milliseconds instead of the 40 or so I was expecting. The index was correct. The plan type was correct. The table access was still happening, silently, underneath a plan node that literally has the word "only" in its name.
The fix was not a different index. Someone ran VACUUM on the table, it took about ninety seconds, and the next EXPLAIN ANALYZE showed Heap Fetches: 4 and a runtime of 41 milliseconds. This article is the full explanation of that ninety seconds: why InnoDB covering indexes need no such step, what the PostgreSQL visibility map actually does, how to design covering indexes on each engine, and how to watch the counters that tell you the trick is still working.
Why did the covering index fix MySQL but not PostgreSQL?
The short answer: both engines can serve a query entirely from an index, but they disagree about where row visibility information lives, and that disagreement decides whether reading the index is enough. A row in a multiversion database is only usable if the scanning transaction is allowed to see it. MySQL's InnoDB and PostgreSQL both have to answer that visibility question for every row; they just store the evidence in different places.
PostgreSQL stores the visibility evidence, the inserting and deleting transaction IDs, in the header of each heap tuple. The index entry for a row carries no such information. So when PostgreSQL walks a covering index, it knows where the row is but not whether your snapshot can see it. Unless something else vouches for the page, it must visit the heap tuple to check. That visit is the heap fetch, and it is exactly the table access the covering index was supposed to eliminate.
InnoDB faces the same question and answers it without a maintenance step, for reasons that fall out of its storage layout.
How do InnoDB secondary indexes make covering scans cheap?
InnoDB does not have a heap at all: the table itself is a B-tree keyed by the primary key, with all the row data in its leaves. Every secondary index entry stores the indexed columns plus the row's primary key value, not a physical address. That design has two consequences every MySQL operator internalizes. First, a lookup through a secondary index that is not covering costs two B-tree descents: one down the secondary index to find the primary key value, one down the clustered index to fetch the full row. Second, a covering index collapses that to one descent, because everything the query needs is already sitting in the secondary index leaf.
Here is the index that fixed the orders query, and the plan confirmation:
-- MySQL 8.0
CREATE INDEX idx_orders_status_total
ON orders (status, total);
EXPLAIN
SELECT status, SUM(total)
FROM orders
WHERE status = 'open'
GROUP BY status;
-- Extra column shows: Using index
-- meaning the query is answered from the index alone."Using index" in the Extra column is MySQL's covering-index tell, and unlike PostgreSQL's Index Only Scan it is a promise without fine print. The reason InnoDB can keep that promise is that it does not delegate visibility to a maintenance process. InnoDB checks visibility through undo logs and read views at read time, and it tracks the newest transaction that touched each secondary index page; when that transaction is older than every active read view, the whole page can be served without per-row visibility work. The mechanism is always on, always current, and requires nobody to run anything.
The honest cost sheet for InnoDB's design: every secondary index is fatter because it stores the primary key, so a wide primary key inflates every index on the table, and non-covering lookups pay the double descent forever. Covering indexes are the standard escape hatch, which is why MySQL shops build them reflexively.
What does the visibility map do in a PostgreSQL index-only scan?
The visibility map is PostgreSQL's answer to the same problem, implemented as one bit per heap page that says "every row on this page is visible to every transaction." When an index-only scan needs rows from a heap page, it checks that page's bit first. If the bit is set, the heap fetch is skipped entirely and the answer comes from the index alone. If the bit is not set, PostgreSQL fetches the heap tuple and checks its visibility the slow way, once per row.
The catch is who sets the bit. Pages are marked all-visible by VACUUM, and only by VACUUM. A page full of freshly inserted or freshly updated rows does not have the bit, no matter how obviously visible those rows are. Our PostgreSQL orders table had just absorbed an overnight backfill that touched around four million rows, and autovacuum had not yet worked through the table. Nearly every page the query needed was missing its all-visible bit, hence 38,412 heap fetches under an Index Only Scan.
This is the sequence that fixed it, and the diagnostic shape to remember:
-- PostgreSQL 15
CREATE INDEX idx_orders_status_total
ON orders (status, total);
EXPLAIN (ANALYZE, BUFFERS)
SELECT status, SUM(total)
FROM orders
WHERE status = 'open'
GROUP BY status;
-- Index Only Scan using idx_orders_status_total on orders
-- Heap Fetches: 38412 <-- the covering index is not covering yet
VACUUM orders;
-- same query afterwards:
-- Index Only Scan using idx_orders_status_total on orders
-- Heap Fetches: 4Two operational notes before you tune anything. First, ANALYZE does not set all-visible bits; if your autovacuum is tuned to analyze eagerly but vacuum lazily, index-only scans suffer. Second, HOT updates, the optimization where an update that touches no indexed column keeps the new version on the same page, help bloat and index maintenance but do nothing for the visibility map. You still need vacuum to visit the page. The deeper mechanics, including how the planner weighs all-visible page counts when choosing an index-only scan at all, are covered in our piece on index-only scans and heap fetches.
Should you use INCLUDE columns or just extend the index key?
Extend the key in MySQL, use INCLUDE in PostgreSQL when the column is cargo rather than a search key, and know why the distinction exists. MySQL has no INCLUDE clause: a covering index is just an index whose key happens to contain every column the query reads, so you append columns to the key and accept that they participate in ordering and comparisons. That is usually harmless, but it widens the key and, on a unique index, changes what uniqueness means.
PostgreSQL separates the two roles. The key columns are what the B-tree orders and searches; INCLUDE columns ride along in the leaf pages as payload, unordered, unsearched, and ignored by uniqueness checks. For the orders query, where only status is ever filtered and total is pure payload, the honest PostgreSQL design is:
-- PostgreSQL 11 and later
CREATE INDEX idx_orders_status_inc_total
ON orders (status) INCLUDE (total);
-- MySQL 8.0 has no INCLUDE; the key carries everything:
CREATE INDEX idx_orders_status_total
ON orders (status, total);The practical differences are modest but real. INCLUDE keeps the key narrow, which preserves the index's selectivity characteristics and keeps unique constraints honest: a UNIQUE index with INCLUDE enforces uniqueness on the key alone, which is exactly what you want when you are adding payload for coverage. Putting everything in the key on PostgreSQL works too, and plenty of production systems do it, but it misstates the index's purpose and can quietly change unique semantics during a migration from MySQL, where extending the key was the only option. When porting a MySQL covering index to PostgreSQL, ask which columns were filters and which were passengers, and split them accordingly.
How do you catch heap-fetch regressions before users do?
Watch two things: the heap fetch counts in EXPLAIN ANALYZE for your index-only queries, and the update pressure on the tables those queries depend on. Heap fetches are per-execution evidence, and the most reliable way to collect it is to run EXPLAIN (ANALYZE, BUFFERS) on your hot covering-index queries after large data changes, deploys, and backfills, and treat "Heap Fetches" climbing from near zero as a regression even when the plan type is unchanged. The plan name will not warn you; only the counter will.
For anticipatory monitoring, pg_stat_user_tables tells you which tables are accumulating exactly the kind of churn that erases all-visible bits:
SELECT relname,
n_tup_upd,
n_tup_ins,
n_dead_tup,
last_vacuum,
last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_tup_upd DESC
LIMIT 15;A table near the top of that list whose last vacuum predates a big update window is a table whose index-only scans are probably paying heap fetches right now. If the same tables stay on the list week after week, the fix is usually autovacuum tuning for those tables rather than more manual vacuuming: lower the scale factor so vacuum visits them sooner, and let the visibility map catch up on the database's schedule instead of yours. Manual VACUUM after bulk loads and backfills remains good practice regardless.
On the MySQL side the equivalent vigilance is simpler because there is no visibility map to decay. What you verify instead is that the optimizer still chooses the covering index: watch rows examined versus rows returned per query family in performance_schema digests, and re-check EXPLAIN after schema changes, because a widened query or a changed column set silently turns "Using index" back into two-descent lookups. The covering trick is stable on MySQL but fragile to query drift; on PostgreSQL it is stable to query drift but fragile to write churn. Your monitoring should reflect which failure mode you actually own.
How MonPG keeps index-only scans honest on PostgreSQL
The uncomfortable part of this story is that nothing failed loudly. The query was on its optimal plan, no error was logged, and the only symptom was latency several times worse than it should have been, caused by a counter buried in a plan most people never re-read after the index works once. This is the class of problem that continuous evidence beats intuition on. MonPG's PostgreSQL monitoring tracks per-table vacuum activity, dead tuple accumulation, and update rates alongside query performance history, so the connection between "this table has not been vacuumed since the backfill" and "this query got slow on Tuesday" is one dashboard instead of one lucky afternoon of EXPLAIN ANALYZE.
MonPG monitors PostgreSQL today, and MySQL support is on the roadmap; when it lands, the counters this article leaned on are the ones it will surface: rows examined versus rows returned from performance_schema digests, so you can see at a glance when a workload that should be index-covered has drifted back to touching the clustered table. Whichever engine you run, the covering index is one of the highest-leverage tuning moves available, and each engine prices it differently: MySQL charges you up front in wider keys and double descents, PostgreSQL charges you in ongoing vacuum discipline. Knowing which bill you are paying is most of the battle.