Your Postgres Index Is Missing INCLUDE

You add an index on the columns in your WHERE clause. The query plan improves, but Postgres still visits the heap for every matching row to fetch additional columns. That heap lookup is a random disk read — expensive on large tables, even with caching.

Since Postgres 11, the INCLUDE clause lets you store extra columns inside a B-tree index without making them sort keys. If every column your query needs lives in that index, Postgres serves the result straight from the index. No heap access at all.

Why this matters

An index-only scan is the fastest read path in PostgreSQL. When the planner finds all requested columns in a single index, it skips the table entirely. On large tables where the heap isn't fully cached in shared buffers, the gap between an index scan and an index-only scan can be dramatic.

Without INCLUDE, you'd have to add those columns as real index keys. That bloats the B-tree, slows writes, and wastes space on ordering you don't need. INCLUDE gives you the covering-index benefit without the penalty.

How it works

A B-tree index stores key columns in sorted order for fast lookups. When you add columns via INCLUDE, Postgres stores them in the leaf nodes but ignores them during tree traversal and comparison. The index stays compact. The extra data rides along for free.

The planner then checks whether all referenced columns appear in one index. If the visibility map confirms those pages are all-visible, it runs an index-only scan.

Where this helps

Read-heavy API endpoints. That endpoint returning email and display_name filtered by org_id? Index on org_id, INCLUDE both columns. Every lookup becomes index-only.

Dashboard aggregation queries. SELECT status, count(*) FROM orders WHERE org_id = 42 GROUP BY status — index on org_id, INCLUDE status. The aggregation reads only the index.

Foreign key lookups during joins. When joining on a FK but also pulling one or two columns from the parent table, INCLUDE those columns in the FK index and eliminate the heap fetch entirely.

Watch out

INCLUDE columns duplicate data into the index. On massive tables, measure the storage cost against the read improvement.

Index-only scans require the visibility map to mark pages as all-visible. If recent inserts or updates haven't been vacuumed yet, Postgres falls back to heap checks. Keep autovacuum tuned aggressively on tables with frequent writes.

You can't put expressions or function results in INCLUDE. If your query filters on UPPER(email), that needs to be a proper expression index key — not an included column.

Try it yourself

-- The query hitting the heap
SELECT email, display_name
FROM users
WHERE org_id = 42;

-- Create a covering index
CREATE INDEX idx_users_org_covering
  ON users (org_id)
  INCLUDE (email, display_name);

-- Verify the planner switches to index-only
EXPLAIN (ANALYZE, BUFFERS)
SELECT email, display_name
FROM users
WHERE org_id = 42;

-- Look for: "Index Only Scan" with "Heap Fetches: 0"

TL;DR

• What changed: Postgres's INCLUDE clause stores extra columns in an index without making them sort keys
• Why it matters: Queries that touch only included columns run as index-only scans — zero heap lookups
• What to try today: Run EXPLAIN ANALYZE on your slowest read, add INCLUDE columns, and watch for "Index Only Scan"