Lesson 3.2: Covering Indexes (Index-Only Scans)

The Problem: Heap Fetches

-- Query
SELECT title, category FROM documents WHERE user_id = 123;

-- With regular index on user_id:
-- 1. Index scan finds 100 matching rows
-- 2. 100 random heap page reads to get title, category
-- Time: ~50ms (random I/O is slow)

The Solution: Covering Index

-- Include frequently accessed columns
CREATE INDEX documents_user_cover_idx ON documents(user_id)
INCLUDE (title, category);

-- Now query uses Index-Only Scan:
-- 1. Index scan finds matching rows
-- 2. title, category already in index (no heap access)
-- Time: ~2ms (25x faster)

EXPLAIN output:

EXPLAIN (ANALYZE, BUFFERS)
SELECT title, category FROM documents WHERE user_id = 123;

-- With covering index:
Index Only Scan using documents_user_cover_idx on documents
  Index Cond: (user_id = 123)
  Heap Fetches: 0          ← No heap access!
  Buffers: shared hit=12

When to Use Covering Indexes

Good use cases:

-- 1. Frequent queries with specific column access
CREATE INDEX orders_user_cover_idx ON orders(user_id)
INCLUDE (created_at, total, status);

SELECT created_at, total FROM orders WHERE user_id = 123;

-- 2. Sorting + filtering
CREATE INDEX products_cat_cover_idx ON products(category)
INCLUDE (name, price);

SELECT name, price FROM products
WHERE category = 'electronics'
ORDER BY price DESC
LIMIT 10;

Bad use cases (don't do this):

-- Including too many columns (index becomes huge)
CREATE INDEX bad_idx ON users(email)
INCLUDE (name, address, phone, bio, preferences);  -- Bad!

-- Including large columns
CREATE INDEX bad_vector_idx ON documents(category)
INCLUDE (embedding);  -- 1536 floats per row? Bad!

Rule: Include small, frequently accessed columns only.

Key Takeaways

Covering indexes include extra columns to avoid heap fetches
Index-Only Scans can be 25x faster than regular index scans
Only include small, frequently queried columns
Check EXPLAIN for "Heap Fetches: 0" to verify index-only scans
Tradeoff: Larger index size for faster queries
Most effective for queries that access few columns repeatedly

-- Query SELECT title, category FROM documents WHERE user_id = 123; -- With regular index on user_id: -- 1. Index scan finds 100 matching rows -- 2. 100 random heap page reads to get title, category -- Time: ~50ms (random I/O is slow)

The Solution: Covering Index

-- Include frequently accessed columns CREATE INDEX documents_user_cover_idx ON documents(user_id) INCLUDE (title, category); -- Now query uses Index-Only Scan: -- 1. Index scan finds matching rows -- 2. title, category already in index (no heap access) -- Time: ~2ms (25x faster)

EXPLAIN output:

EXPLAIN (ANALYZE, BUFFERS) SELECT title, category FROM documents WHERE user_id = 123; -- With covering index: Index Only Scan using documents_user_cover_idx on documents Index Cond: (user_id = 123) Heap Fetches: 0 ← No heap access! Buffers: shared hit=12

When to Use Covering Indexes

Good use cases:

-- 1. Frequent queries with specific column access CREATE INDEX orders_user_cover_idx ON orders(user_id) INCLUDE (created_at, total, status); SELECT created_at, total FROM orders WHERE user_id = 123; -- 2. Sorting + filtering CREATE INDEX products_cat_cover_idx ON products(category) INCLUDE (name, price); SELECT name, price FROM products WHERE category = 'electronics' ORDER BY price DESC LIMIT 10;

Bad use cases (don't do this):

-- Including too many columns (index becomes huge) CREATE INDEX bad_idx ON users(email) INCLUDE (name, address, phone, bio, preferences); -- Bad! -- Including large columns CREATE INDEX bad_vector_idx ON documents(category) INCLUDE (embedding); -- 1536 floats per row? Bad!

Rule: Include small, frequently accessed columns only.

Key Takeaways

Covering indexes include extra columns to avoid heap fetches

Index-Only Scans can be 25x faster than regular index scans

Only include small, frequently queried columns

Check EXPLAIN for "Heap Fetches: 0" to verify index-only scans

Tradeoff: Larger index size for faster queries

Most effective for queries that access few columns repeatedly