INCLUDE Columns vs Composite Keys #

Both forms produce a covering index, and both let a query be answered without visiting the heap. The difference is what the extra columns can do: key columns are searchable and orderable and are stored throughout the tree; included columns are payload, stored only in the leaves, and cannot narrow anything.

Getting the split right produces a smaller index that covers the same queries. Getting it wrong produces a bloated tree that covers no more. The wider design context is in covering index design.

The Rule #

Ask one question per column: does the query use it to find or order rows, or only to return them?

Two structural consequences follow. Key columns are copied into internal pages to guide descent, so a wide key inflates every level of the tree and can add a level, which costs an extra page read on every lookup. Included columns exist only at the leaf level, so they widen exactly one level.

The third consequence is about constraints: a UNIQUE index enforces uniqueness over its key columns alone, so INCLUDE lets a unique index cover additional columns without changing what it enforces.

Where each kind of column is stored On the left, a composite key stores all three columns in the root, internal and leaf levels, making every level wide. On the right, a two-column key keeps the internal levels narrow while the payload column appears only in the leaf pages. key = (a, b, c) key = (a, b) INCLUDE (c) root: a,b,c internal: a,b,c internal: a,b,c wide at every level · taller tree c is searchable and orderable root: a,b internal: a,b internal: a,b c stored only in leaves · shorter tree c is returnable but not searchable both cover the same SELECT — only one of them can also filter on c

Annotated Evidence #

Take a query that filters on two columns and returns a third:

SELECT customer_id, created_at, total_cents
  FROM orders
 WHERE customer_id = 4471 AND created_at > '2026-06-01';

With INCLUDE, the plan is index-only and the index is compact:

CREATE INDEX orders_cust_created_incl ON orders (customer_id, created_at) INCLUDE (total_cents);

Index Only Scan using orders_cust_created_incl on orders  (actual time=0.03..1.94 rows=8412 loops=1)
  Index Cond: (customer_id = 4471 AND created_at > '2026-06-01')
  Heap Fetches: 0
  Buffers: shared hit=34

Now a different query that also filters on total_cents:

SELECT customer_id FROM orders WHERE customer_id = 4471 AND total_cents > 50000;

Index Only Scan using orders_cust_created_incl on orders  (actual rows=8412 loops=1)
  Index Cond: (customer_id = 4471)
  Filter: (total_cents > 50000)                -- included column: filtered, not searched
  Rows Removed by Filter: 7912

total_cents appears as a Filter, not part of the Index Cond. All 8,412 entries for that customer were read and 7,912 discarded. Move it into the key and it restricts the scan instead:

CREATE INDEX orders_cust_total_idx ON orders (customer_id, total_cents);

Index Only Scan using orders_cust_total_idx on orders  (actual rows=500 loops=1)
  Index Cond: (customer_id = 4471 AND total_cents > 50000)
  Heap Fetches: 0

That is the whole distinction: Index Cond narrows, Filter does not.

Step-by-Step Design Workflow #

  1. Collect the real predicates. Every column that appears in a WHERE, JOIN, or ORDER BY for the queries this index serves belongs in the key set.

  2. Order the key columns by the rules in multicolumn index column ordering — equality predicates first, then the range or ordering column.

  3. Put pure output columns in INCLUDE:

    CREATE INDEX CONCURRENTLY orders_cust_created_incl
  ON orders (customer_id, created_at) INCLUDE (total_cents, status);

  4. Check the cost of the coverage. Wide payloads make leaf pages fat, which means more pages per range scan:

    SELECT pg_size_pretty(pg_relation_size('orders_cust_created_incl'));
SELECT tree_level, avg_leaf_density FROM pgstatindex('orders_cust_created_incl');

  5. Use INCLUDE on unique indexes freely, since uniqueness is enforced on the key only:

    CREATE UNIQUE INDEX CONCURRENTLY orders_pkey_covering
  ON orders (id) INCLUDE (customer_id, status);

  6. Verify the payoff: Index Only Scan with Heap Fetches: 0. If heap fetches remain high, the problem is visibility rather than coverage — see visibility map and index-only scan regressions.

Capability comparison: key column versus INCLUDE column Five capabilities compared across key columns and include columns: narrowing the scan, supplying ordering, enforcing uniqueness, covering the select list, and where the column is stored. capability key column INCLUDE column narrows the scan (Index Cond) yes no — becomes a Filter supplies ORDER BY yes no participates in UNIQUE yes no — and that is the point covers the select list yes yes stored in internal pages yes no — leaves only

Before and After #

-- BEFORE: total_cents in INCLUDE, but the query filters on it
Index Only Scan  Index Cond: (customer_id = 4471)
                 Filter: (total_cents > 50000)   Rows Removed by Filter: 7912
Buffers: shared hit=34

-- AFTER: total_cents promoted into the key
Index Only Scan  Index Cond: (customer_id = 4471 AND total_cents > 50000)
                 (no Filter line)
Buffers: shared hit=6

The metric is Rows Removed by Filter falling to zero and buffer reads dropping with it — the scan now stops at the rows it needs instead of reading and discarding.

Common Pitfalls #

Putting a filtered column in INCLUDE. It will be checked, not searched. Diagnostic signal: the column appearing in Filter with a large Rows Removed by Filter. Fix: move it into the key.

Padding INCLUDE with wide columns. Fat leaf pages mean more pages per range scan, undoing part of the benefit. Diagnostic signal: a covering index several times larger than the key-only version with only marginally fewer heap fetches. Fix: include only what the hot queries actually return.

Adding a column to a unique key to cover it. That changes what the constraint enforces. Diagnostic signal: a UNIQUE (id, status) where uniqueness on id alone was intended. Fix: UNIQUE (id) INCLUDE (status).

Expecting coverage to survive without vacuum. Index-only scans still need all-visible heap pages. Diagnostic signal: Heap Fetches climbing on a perfectly covering index. Fix: vacuum frequency, not index design.

Covering a query that selects *. A covering index must contain every column the query returns, so SELECT * effectively demands the whole row in the index. Diagnostic signal: an index-only plan that reverts to a plain index scan the moment someone adds a column to the select list. Fix: name the columns the code actually needs, and accept that a covering index is a contract with a specific query shape.

Forgetting the write cost. Every included column must be updated in the index whenever it changes in the heap, and an update touching an included column cannot be a heap-only tuple update. Diagnostic signal: write throughput dropping after a wide covering index is added to a hot table. Fix: include only columns that are stable, or accept the trade knowingly.

Adding the same column to both the key and INCLUDE. It is stored twice for no benefit and PostgreSQL will reject an outright duplicate. Diagnostic signal: an index definition listing a column in both clauses. Fix: keep it in the key, where it is both searchable and returnable.

Index Cond narrows, Filter does not The upper fragment shows a predicate applied as an index condition, reading five hundred entries. The lower fragment shows the same predicate as a filter, reading eight thousand entries and discarding most of them. key column Index Cond: (customer_id = 4471 AND total_cents > 50000) → 500 index entries read INCLUDE Index Cond: (customer_id = 4471) · Filter: (total_cents > 50000) → 8,412 entries read, 7,912 discarded

Frequently Asked Questions #

What can an INCLUDE column not do? It cannot be searched, ordered by, or contribute to an Index Cond. It is payload stored in the leaf entries, present to satisfy the select list of an index-only scan.

Why is INCLUDE smaller than adding the column to the key? Key columns are stored in internal pages as well as leaves to guide tree descent, so a wide key inflates every level and can add a level. An included column widens only the leaf level.

Can a unique index have INCLUDE columns? Yes, and it is one of the best uses of the feature. Uniqueness applies to the key columns only, so you can cover extra columns without altering what the constraint enforces.