0Pricing
SQL Academy · Lesson

Index-Only Scans and Visibility Map

Unlock index-only scans by covering queries and keeping the visibility map up-to-date.

Index-Only Scans and Visibility Map is a free SQL Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What Is an Index-Only Scan?

Normally, an index lookup returns row IDs (TIDs), and the table must be visited to read the actual row. Index-Only Scan answers the query using the index alone — no heap visit. Hugely faster.

Conditions

Index-Only Scan requires:

  • All SELECTed columns must be in the index
  • The row's page must be marked "all visible" in the visibility map

Visibility Map

A bitmap per table page: a bit set means "all rows on this page are visible to all transactions". VACUUM maintains it. Without it, PostgreSQL must visit the heap to check visibility.

Make a Query Index-Only

Cover all needed columns:

-- Query:
SELECT id, email FROM users WHERE id = 42;

-- Without an index on (id, email), only an Index Scan that visits the heap is possible.
-- With this:
CREATE INDEX users_id_email_idx ON users (id, email);
-- Or better:
CREATE INDEX users_id_email_idx ON users (id) INCLUDE (email);
-- The query can be index-only.

INCLUDE

Covering index with non-key columns (PG 11+). The column is stored in leaves but not used for ordering — no impact on insert cost:

CREATE INDEX users_id_idx ON users(id) INCLUDE (email, full_name);

SELECT id, email, full_name FROM users WHERE id = 42;
-- Index-only scan if visibility map allows.

Why It Might Still Visit Heap

After heavy writes, the visibility map may be stale. Run VACUUM (without FULL) to refresh it.

VACUUM (VERBOSE) users;
-- "scanned X pages, X of which are visible"
-- More visible pages = more Index-Only Scans possible.

Verifying Index-Only Scans

EXPLAIN reveals it:

EXPLAIN ANALYZE
SELECT id, email FROM users WHERE id = 42;
--  Index Only Scan using users_id_email_idx on users
--    Index Cond: (id = 42)
--    Heap Fetches: 0       ← key number

Heap Fetches Tell You

"Heap Fetches: 0" — perfect, all data from index. "Heap Fetches: N" — N rows needed a heap visit (visibility map bit not set). After autovacuum runs, Heap Fetches usually drops.

When To Use INCLUDE vs Composite

  • Use INCLUDE for columns you only SELECT (not filter / sort by)
  • Use composite (key) when you also filter or sort by the column

INCLUDE keeps the index narrower and writes faster.

Index Bloat Hurts Index-Only

Bloated indexes have more pages, so even index-only scans read more. Keep indexes vacuumed and re-indexed when bloat gets high.

Don't Cover Everything

Adding too many columns to an index makes writes slow and the index huge. Cover only the hot read paths.

Materialized Views as a Cover

If you want extreme read speed for a specific report, a materialised view + index is essentially a "covering index" with arbitrary computed columns.

Recap

Index-Only Scan = fastest read path.

  • All selected columns must be in the index
  • INCLUDE adds non-key columns cheaply
  • Visibility map must say "all visible" — VACUUM keeps it fresh
  • EXPLAIN: Heap Fetches: 0 is the goal

Quick Check

You see Heap Fetches: 100,000 in EXPLAIN despite an Index Only Scan node. What's the typical fix?

Frequently asked questions

Is the “Index-Only Scans and Visibility Map” lesson free?

Yes — the full text of “Index-Only Scans and Visibility Map” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Index-Only Scans and Visibility Map”?

Unlock index-only scans by covering queries and keeping the visibility map up-to-date. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Index-Only Scans and Visibility Map” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. MVCC and Bloat Causes
  2. VACUUM, autovacuum, vacuum_cost_delay
  3. ANALYZE and pg_statistic
  4. Index-Only Scans and Visibility Map
← Back to SQL Academy