Covering indexes (INCLUDE) and Bitmap Heap Scans — how do you eliminate heap fetches in 2025?

Q: Covering indexes (INCLUDE) and Bitmap Heap Scans — how do you eliminate heap fetches in 2025?

**Target Audience:** Developers who already know [what a covering index is](/interview-question/what-is-a-covering-index) and now face a real PostgreSQL plan with a Bitmap Heap Scan they want gone. **Difficulty:** Medium **Engines:** PostgreSQL 16.15 for every plan here (100000 generated rows per example, run in a throwaway database), and SQLite 3.45.0 through sql.js 1.10.2 for the runnable example. Version facts for PostgreSQL 11 and 18 are cited from the release notes. > **How to read this doc:** Each idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every plan shown was produced by running it; the **Verified** section quotes each example output in full. ## 1. Why This Even Matters — A Story First A warehouse picker gets a list of 5000 parcels to collect. A clever picker first sorts the list by aisle, then walks the aisles once in order instead of zig-zagging. That is much better than one trip

The problem

Question presented to candidate: "Your query uses an index but still shows a Bitmap Heap Scan reading thousands of table pages. How would you use a covering index with INCLUDE to get rid of those heap fetches, and what has to be true for PostgreSQL to actually skip the table?"

What a strong answer should cover:

What a Bitmap Heap Scan is: a Bitmap Index Scan collects matching row locations, sorts them by page, then the heap is read page by page; it sits between an Index Scan and a Seq Scan, can combine indexes with BitmapAnd / BitmapOr, and goes lossy (page-level, with Rows Removed by Index Recheck) when the bitmap exceeds work_mem
Why it can be slow: a few percent of rows scattered across the table can touch nearly every page, so the "index" plan reads most of the table anyway
The fix: a covering index such as CREATE INDEX ... (score) INCLUDE (id) (PostgreSQL 11+) so the planner can choose an Index Only Scan; INCLUDE columns ride in the leaf entries, are not searchable or sorted, and do not change what a UNIQUE index enforces
The catch: Index Only Scans depend on the visibility map; until VACUUM marks pages all-visible the planner may not even choose one, and recently updated pages show up as Heap Fetches; tune autovacuum on hot tables
Costs: INCLUDE indexes are never deduplicated and are bigger, updates of an included column cannot be HOT; check Heap Fetches and Buffers with EXPLAIN (ANALYZE, BUFFERS) before and after

Clarifying questions expected:

Which columns does the query select, filter and sort on, and how many rows does it return?
How write-heavy is the table, and is autovacuum keeping up (when was it last vacuumed)?
Which PostgreSQL version (INCLUDE needs 11+)?

Code / implementation expected: Yes — the plan before, the INCLUDE index, the plan after with Heap Fetches, and how VACUUM changes it.

Schema used by the examples:

covering-index
bitmap-scan
vacuum

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Developers who already know what a covering index is and now face a real PostgreSQL plan with a Bitmap Heap Scan they want gone. Difficulty: Medium Engines: PostgreSQL 16.15 for every plan here (100000 generated rows per example, run in a throwaway database), and SQLite 3.45.0 through sql.js 1.10.2 for the runnable example. Version facts for PostgreSQL 11 and 18 are cited from the release notes.

How to read this doc: Each idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every plan shown was produced by running it; the Verified section quotes each example output in full.

1. Why This Even Matters — A Story First

A warehouse picker gets a list of 5000 parcels to collect. A clever picker first sorts the list by

The solution is hidden so you can try first

6 min read · 12 sections

Run the code

Edit the query and run it against the tables for this question.

SQLSQLite has no INCLUDE: append the selected column to the key instead

PostgreSQL: 5% of the rows, but the Bitmap Heap Scan touches almost every page

-- 100000 generated orders; score is scattered across the whole table
CREATE TABLE big_orders WITH (autovacuum_enabled = false) AS
SELECT g AS id,
       g % 5000 AS customer_id,
       (g * 7919) % 100000 AS score,
       repeat('x', 80) AS note
FROM generate_series(1, 100000) AS g;
CREATE INDEX big_orders_score_idx ON big_orders (score);
VACUUM ANALYZE big_orders;

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id, score FROM big_orders WHERE score < 5000;

PostgreSQL: an INCLUDE index plus VACUUM gives an Index Only Scan with 0 heap fetches

-- 100000 generated orders; score is scattered across the whole table
CREATE TABLE big_orders WITH (autovacuum_enabled = false) AS
SELECT g AS id,
       g % 5000 AS customer_id,
       (g * 7919) % 100000 AS score,
       repeat('x', 80) AS note
FROM generate_series(1, 100000) AS g;
CREATE INDEX big_orders_score_idx ON big_orders (score);
CREATE INDEX big_orders_score_incl ON big_orders (score) INCLUDE (id);
VACUUM ANALYZE big_orders;

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id, score FROM big_orders WHERE score < 5000;

PostgreSQL: updates clear visibility map bits and heap fetches come back

-- 100000 generated orders; score is scattered across the whole table
CREATE TABLE big_orders WITH (autovacuum_enabled = false) AS
SELECT g AS id,
       g % 5000 AS customer_id,
       (g * 7919) % 100000 AS score,
       repeat('x', 80) AS note
FROM generate_series(1, 100000) AS g;
CREATE INDEX big_orders_score_idx ON big_orders (score);
CREATE INDEX big_orders_score_incl ON big_orders (score) INCLUDE (id);
VACUUM ANALYZE big_orders;

-- touch 500 of the matching rows (note is not indexed)
UPDATE big_orders SET note = 'changed' WHERE score < 5000 AND id % 10 = 0;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id, score FROM big_orders WHERE score < 5000;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.