Why This Matters
Covering indexes include all columns needed by a query, enabling index-only scans that skip the table entirely. Without them, Postgres must perform an extra "heap fetch" to get columns not in the index.
This is especially valuable when:
- Selecting only a few columns from a wide table
- Running aggregate queries (
COUNT,SUM) - Tables have high I/O cost (cold data, remote storage)
The Problem
Incorrect - Index scan + heap fetch
CREATE INDEX users_email_idx ON users (email);
-- Must fetch name and created_at from table heap
SELECT email, name, created_at
FROM users
WHERE email = '[email protected]';The Solution
Correct - Index-only scan with INCLUDE
-- Include non-searchable columns in the index
CREATE INDEX users_email_idx ON users (email)
INCLUDE (name, created_at);
-- All columns served from index, no table access needed
SELECT email, name, created_at
FROM users
WHERE email = '[email protected]';INCLUDE vs adding to index: INCLUDE columns are stored in the index but not used for searching. This keeps the index smaller and faster than adding them as regular index columns.
Another common pattern - including columns you SELECT but don't filter on:
-- Searching by status, but also need customer_id and total
CREATE INDEX orders_status_idx ON orders (status)
INCLUDE (customer_id, total);
SELECT status, customer_id, total
FROM orders
WHERE status = 'shipped';Check Your Project Automatically
Connect your Supabase project to identify opportunities for covering indexes.
Start Supabase AuditCommon Mistakes
- Including too many columns: Large
INCLUDElists make indexes bigger and slower to update. Only include columns from your most frequent queries. - Visibility map not updated: Index-only scans only work when the visibility map is current. Run
VACUUMregularly. - Expecting it to work for SELECT *: Covering indexes only help when you select specific columns that are in the index.