# When should I switch to a covering index with INCLUDE to get an index-only scan?

> Add `INCLUDE` only when the query is hot and `EXPLAIN (ANALYZE, BUFFERS)` shows heap access dominating, then tighten autovacuum until `Heap Fetches: 0`.

- Asked: 2026-09-02
- Answered: 2026-09-08
- Asked by: Pelin
- Tags: postgresql, veritabani, performans
- Source: https://www.muhammetsafak.com.tr/en/just-ask/switch-covering-index-include-get-index-only-scan/
- Language: en-US
- Author: Muhammet Şafak

---
**Question:** I have a frequently-run lookup in PostgreSQL: I filter on `WHERE customer_id = $1` and read the `status` and `total` columns. I already have a B-tree index on `customer_id` and it does find the rows.

The problem is that in the EXPLAIN output I still see a heap access: after the index locates the row, it goes to the table (heap) to fetch `status` and `total`. When should I move to a covering index with `INCLUDE` to fetch these two extra columns without hitting the heap—and will I actually get an index-only scan if I do?


Short answer: switch when the query is genuinely hot (high frequency, low-latency expectation) and the heap access is a measured cost.

## Short answer

But one caveat: a covering index alone does not guarantee an index-only scan — that depends on the table's visibility-map state.

## Why

1. **INCLUDE stores columns in leaf pages only.** `INCLUDE (status, total)` does not add these columns to the B-tree's sort key; they carry no comparison cost but sit there to satisfy the query without a heap visit. That's exactly your goal.

2. **Index-only scans depend on the visibility map.** Postgres skips the heap only if the page is marked "all-visible". On a write-heavy table that map goes stale between vacuums; then, even with a covering index, EXPLAIN shows `Heap Fetches: N`. So sometimes the real lever isn't the index — it's tightening autovacuum.

   ```sql
   CREATE INDEX idx_orders_customer
     ON orders (customer_id)
     INCLUDE (status, total);

   EXPLAIN (ANALYZE, BUFFERS)
   SELECT status, total FROM orders WHERE customer_id = $1;
   -- Want: "Index Only Scan ... Heap Fetches: 0"
   ```

3. **It's a write/space tradeoff.** Wider leaf pages = bigger index, more WAL, slower writes, more to keep in cache. That cost is only worth it for genuinely hot reads; don't slap a covering index on every query.

4. **INCLUDE columns are for projection only.** Don't forget this: columns added via `INCLUDE` can't be used in the `WHERE` filter or `ORDER BY`; they're there purely to satisfy the `SELECT` output. If you'll also filter on that column, it has to go into the key (possibly a [composite index](/en/just-ask/choosing-the-right-composite-index-order-in-postgresql/)); if you only need to return it, INCLUDE is the right place.

## What to do

1. **Prefer INCLUDE over adding to the key.** Putting `status`, `total` into the index key can bloat internal nodes and imposes an ordering you don't need. INCLUDE keeps the tree lean and only widens the leaf — which is all you need to cover the query.

2. **Verify with EXPLAIN (ANALYZE, BUFFERS).** The job isn't done until you see "Index Only Scan" and "Heap Fetches: 0". If heap fetches stay high, the fix may be `VACUUM` (or lowering autovacuum thresholds), not more index. Track whether the index is actually used via `pg_stat_user_indexes`; a covering index nobody touches is just write overhead.

**Bottom line:** personally I'd first confirm with `EXPLAIN (ANALYZE, BUFFERS)` that the heap access really is the dominant cost, then rebuild the `customer_id` index with `INCLUDE (status, total)`, and tighten autovacuum for that table until I see `Heap Fetches: 0`. If the index bloats over time, tidy it up with `REINDEX CONCURRENTLY`. In short: if these two columns really are the read-only payload of a hot query, a covering index pays off; if not, adding an index without measuring usually just slows down the write path.

## Related Reading

- [Should I use a partial index on a queue table where I only ever scan 'pending' rows?](https://www.muhammetsafak.com.tr/en/just-ask/should-i-use-a-partial-index-on-a-queue-table-where/) — Just Ask
- [Which PgBouncer pooling mode should I pick — Session, Transaction, or Statement?](https://www.muhammetsafak.com.tr/en/just-ask/pgbouncer-pooling-modes-and-prepared-statements/) — Just Ask
- [How do I choose the right composite index column order in PostgreSQL?](https://www.muhammetsafak.com.tr/en/just-ask/choosing-the-right-composite-index-order-in-postgresql/) — Just Ask