What is a covering index and what does the INCLUDE clause do?
A covering index holds every column a query needs, so PostgreSQL can answer from the index alone with an index-only scan. INCLUDE adds extra payload columns to the index leaf without making them part of the sort key or uniqueness.
Explain it simply
A train timetable at the station lists train number, name and time on one board, so you never need to walk to the enquiry counter. A covering index is that board: the query finds everything in the index and never visits the table.
INCLUDE (PostgreSQL 11 and later) lets you tack on extra columns that are only carried along, not sorted. That keeps the sort key small.
Example
CREATE INDEX orders_cust_idx
ON orders (customer_id)
INCLUDE (total, created_at);
EXPLAIN SELECT total, created_at
FROM orders WHERE customer_id = 42;
-- Index Only Scan using orders_cust_idx on ordersINCLUDE also helps unique indexes: CREATE UNIQUE INDEX ON orders (order_no) INCLUDE (total) enforces uniqueness on order_no alone.
An index-only scan still checks the visibility map. If many pages are not marked all-visible, PostgreSQL has to visit the table anyway, so regular VACUUM matters.
What to say in the interview
"A covering index contains all the columns a query reads, allowing an index-only scan. INCLUDE adds payload columns without making them part of the key, so uniqueness and ordering stay on the key columns. It depends on the visibility map, so vacuum has to keep up."
Common mistakes
- Including many wide columns, which bloats the index and slows writes.