Covering Index & Index-Only Scans
AdvancedA covering index contains every column a query needs, allowing the database to satisfy the query entirely from the index without touching the main table heap.
Overview
An index-only scan happens when the query's SELECT, WHERE, ORDER BY, and GROUP BY columns are all present in the index. No heap page fetch is required — the database reads only index pages, which are smaller, cached more effectively, and ordered. PostgreSQL supports the INCLUDE clause to add non-key columns to the leaf nodes without affecting the sort order. MySQL achieves covering indexes by listing all needed columns in the composite index. The trade-off is write overhead: every INSERT/UPDATE/DELETE must also update the index, and wider indexes consume more disk and memory.
Heap Fetch vs Index-Only Scan
Without a covering index the optimizer performs an index scan to get row pointers then does random I/O to fetch the actual heap pages. A covering index removes the heap fetch entirely.
-- Table: users(id, name, email, city, created_at)
-- Query: paginate active users by created_at, return id + name + email
-- Non-covering index — index scan + heap fetch for name and email
CREATE INDEX idx_users_active_created ON users (created_at DESC) WHERE status = 'active';
EXPLAIN SELECT id, name, email
FROM users
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 20;
-- Plan: Index Scan (partial index) → then Heap Fetch for name, email
-- Covering index using INCLUDE (PostgreSQL): non-key columns in leaf
CREATE INDEX idx_users_covering ON users (created_at DESC)
INCLUDE (id, name, email)
WHERE status = 'active';
EXPLAIN SELECT id, name, email
FROM users
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 20;
-- Plan: Index Only Scan ← no heap fetch neededCovering Index for Paginated User List
Pagination queries that ORDER BY and LIMIT benefit greatly from covering indexes. The index provides pre-sorted rows and all projected columns, making deep pages cheap.
-- Paginated user list ordered by created_at (keyset pagination)
-- Covering index for the query below
CREATE INDEX idx_users_page ON users (created_at DESC, id DESC)
INCLUDE (name, email, city);
-- Keyset pagination (cursor-based, avoids OFFSET cost)
SELECT id, name, email, city, created_at
FROM users
WHERE (created_at, id) < ('2024-06-01', 5000) -- cursor from previous page
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- The above query performs an Index Only Scan using idx_users_pageWrite Overhead Trade-off
Wider covering indexes improve read performance but every write must maintain all indexes. Benchmark write throughput on high-write tables before adding wide covering indexes.
-- On a table with many covering indexes, an INSERT touches all of them:
INSERT INTO users (name, email, city, created_at, status)
VALUES ('Alice', 'alice@example.com', 'Mumbai', NOW(), 'active');
-- Maintains: primary key B-tree + idx_users_page + idx_users_covering
-- Each additional index adds ~20-50% write overhead per index.
-- Guideline: covering index ideal when:
-- 1. Query is read-heavy and runs frequently (dashboard, pagination)
-- 2. The table has moderate write volume
-- 3. The INCLUDE columns are NOT part of the sort key (pure leaf payload)
-- Avoid covering indexes when:
-- 1. Table has thousands of inserts/second
-- 2. The index would duplicate a large portion of the tableKey Points to Remember
- 1An index-only scan satisfies all of SELECT, WHERE, and ORDER BY from the index without fetching heap pages.
- 2PostgreSQL INCLUDE clause adds non-key columns to leaf nodes; they do not affect sort order but are visible to index-only scans.
- 3MySQL achieves covering indexes by including all needed columns directly in the composite index key.
- 4Index-only scans are more likely when visibility map shows all heap pages are all-visible (VACUUM keeps this up to date).
- 5The write trade-off: each additional index column adds maintenance cost on every INSERT/UPDATE/DELETE to that column.
- 6Cursor-based pagination with a covering index on (created_at DESC, id DESC) INCLUDE (...) avoids the expensive OFFSET deep-page problem.
Interview Questions
Sign in to ask AriaWhat is a covering index and when would you add one?
What is the difference between a regular composite index and one that uses INCLUDE?
How does a covering index help with paginated queries?
What is the write-overhead trade-off when adding covering indexes on a high-write table?
Ask Aria about Covering Index & Index-Only Scans
Your personal AI tutor — ask anything about this concept
Revision Status
Personal Notes
Sign in to save personal notes for this topic.
Discussion
Sign in to join the discussion.