Covering Indexes (INCLUDE)

~12 min · indexes, covering

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

Index-only scans

If every column your query needs is in the index, PostgreSQL can answer the query without touching the table at all — an index-only scan. This is the fastest possible query plan: half the I/O, much better cache behaviour.

The INCLUDE clause

CREATE INDEX ... ON t (a) INCLUDE (b, c) creates an index keyed on a but with b and c stored alongside (not part of the key, just along for the ride). Queries that filter on a and select b/c become index-only scans without bloating the key with extra columns.

VACUUM and visibility maps

Index-only scans require PostgreSQL to know all rows it'll return are visible — managed via the visibility map. After heavy updates, run VACUUM (or trust autovacuum) to keep the visibility map current; otherwise the planner falls back to checking the heap.

Code

Covering index with INCLUDE·sql

CREATE INDEX orders_customer_idx
ON orders (customer_id) INCLUDE (placed_at, total);

-- Index-only scan possible: every selected column is in the index
EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, placed_at, total
FROM   orders
WHERE  customer_id = 7;
-- Look for: 'Index Only Scan' and 'Heap Fetches: 0'.

Without INCLUDE — same effect via composite·sql

-- Functionally similar, but key is wider and unique semantics differ
CREATE INDEX orders_cust_placed_total_idx
ON orders (customer_id, placed_at, total);

External links

PostgreSQL CREATE INDEX INCLUDE

Exercise

Take a query that selects 3-4 columns and filters on one. Add a covering index with INCLUDE. Confirm EXPLAIN shows 'Index Only Scan' and 'Heap Fetches: 0' (run VACUUM on the table first if needed).