Index-only scans and INCLUDE

SQL, indexing and PostgreSQL Senior Python 3.13+ PostgreSQL 17+ Reviewed By Best Latest Apps

Fill the blank

Make SELECT customer_id, total FROM orders WHERE customer_id = $1 eligible for an index-only scan while keeping customer_id the only search key.

SQL · PostgreSQL 17
CREATE TABLE orders (
  id bigint PRIMARY KEY,
  customer_id int,
  total numeric
);
CREATE INDEX orders_cust ON orders (customer_id) ____;
SELECT indexdef LIKE '%INCLUDE (total)%'
FROM pg_indexes WHERE indexname = 'orders_cust';

Options
Hint 1 of 1

The extra column should be returned, not searched.

Choose an answer, then open Show answer below to check it.

The same descent twice. An Index Scan then reads the table page for the row; with total included in the index, an Index Only Scan answers from the leaf and reads 0 of 8 table pages.

Plan shape checked with EXPLAIN on PostgreSQL 17; page counts are a toy table

Text version

Index on customer_id (Index Scan):

  1. Root: 301–600
  2. Inner: 401–500
  3. Leaf: 434–466
  4. Table: H4

INCLUDE (total) (Index Only Scan):

  1. Root: 301–600
  2. Inner: 401–500
  3. Leaf: 434–466
  4. Table: no page
Pages read
ScenarioPlanIndex pagesTable pages
Index on customer_idIndex Scan31
INCLUDE (total)Index Only Scan30
Show answer

Answer: A INCLUDE (total)

Correct. total rides along in the leaf entries without becoming part of the key.

An index-only scan needs every column the query reads to be in the index. INCLUDE stores total in the leaf entries without adding it to the key, so the visibility map can let PostgreSQL skip the table.

Why not the others?
  • B WHERE total > 0. A partial index filter; it doesn't add total to the entries.
  • C USING hash. Hash indexes can't do index-only scans or carry extra columns.
  • D WITH (fillfactor = 90). A storage setting; total still isn't in the index.

30-second answer

If every column a query needs is in the index, PostgreSQL can answer from the index without reading table rows: an index-only scan. It still checks the visibility map, so it only skips the heap for pages VACUUM has marked all-visible. CREATE INDEX ... (customer_id) INCLUDE (total) adds payload columns to the leaf entries without making them part of the sort key, so they don't affect uniqueness or ordering.

Practise saying it · 30 s. No recording, just a timer

Explain index-only scans.

Say your answer out loud, then check which of these points you covered:

  • All needed columns in the index
  • Visibility map check
  • INCLUDE adds payload, not key
  • Avoid SELECT *

Deep dive

EXPLAIN ANALYZE shows 'Heap Fetches: N'; a large N means VACUUM is behind or the table changes constantly.

INCLUDE columns can't be used for searching, only returned.

Wider indexes cost more space and write work; cover only the hot queries.

A unique index with INCLUDE keeps uniqueness on the key columns only.

Index Scan vs Index Only Scan Ran on PostgreSQL 17.11
SQL · PostgreSQL 17
SET max_parallel_workers_per_gather = 0;
CREATE TABLE orders (
  id int PRIMARY KEY, customer_id int, total numeric
);
INSERT INTO orders
SELECT g, g % 1000, g FROM generate_series(1, 100000) g;
CREATE INDEX orders_cust ON orders (customer_id);
VACUUM ANALYZE orders;
EXPLAIN (COSTS OFF)
SELECT customer_id, total FROM orders WHERE id = 450;
CREATE INDEX orders_cust_total ON orders (customer_id) INCLUDE (total);
VACUUM ANALYZE orders;
EXPLAIN (COSTS OFF)
SELECT customer_id, total FROM orders WHERE customer_id = 7;

Common mistakes

  • Expecting index-only scans on a table that's rewritten constantly.
  • Adding every selected column to INCLUDE for a rarely run report.
  • Using SELECT *, which defeats covering indexes.

Senior take & follow-ups

Define it, name the visibility map caveat, and use INCLUDE for one hot query, not everywhere.

Follow-up questions

Think of your answer first, then open the hint.

  1. Why does the visibility map matter?

    Show hint

    Index entries don't carry visibility; the map says a whole page is visible to everyone.

  2. INCLUDE vs adding the column to the key?

    Show hint

    INCLUDE doesn't change sort order or uniqueness and keeps upper levels smaller.

Related questions

Suggest a fix if something here is wrong or out of date.