SQL: an index can answer a query without touching the table

-- needs id and status, but reads the table for status
CREATE INDEX idx_a ON orders (customer_id);
SELECT id, status FROM orders WHERE customer_id = 7;

-- covering: everything the query needs is in the index
CREATE INDEX idx_b ON orders (customer_id) INCLUDE (id, status);

-- portable form - just add the columns to the key
CREATE INDEX idx_c ON orders (customer_id, status, id);

idx_a -> Index Scan          (heap fetches: 1204)
idx_b -> Index Only Scan     (heap fetches: 0)

-- INCLUDE columns are stored in the leaf but not sorted on, so they do not
-- make the index deeper. They cannot be used for filtering or ordering.
--
-- In PostgreSQL an Index Only Scan still checks the visibility map, so heap
-- fetches only drop to zero after a VACUUM.