Animated Architecture Patterns

Covering Index

Answer a query from the index alone: include the columns it selects, so the table is never read, and the scan is index-only.

Also known as index-only scan, INCLUDE columns

Acme app: recent orders PostgreSQL 18.6 SELECT id, status, total_thb SELECT id, status, total_thb FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20 EXPLAIN (ANALYZE, BUFFERS) Index Scan Backward Index Cond: (customer_id = 42) Buffers: shared hit=23 3 index pages20 heap pagesAll 20 entries sit on one leafpage, but each points to its rowon a different heap page: 20random reads when not cached. EXPLAIN (ANALYZE, BUFFERS) Index Only Scan Backward Index Cond: (customer_id = 42) Heap Fetches: 0Buffers: shared hit=4 3 index pages1 visibility map page id is included too: indexentries don't carry the primarykey. Without it the plan stayedan Index Scan: 23 pages. UPDATE ordersSET total_thb = total_thb - 500WHERE id IN (495179, 493709); Index Only Scan Backward Heap Fetches: 4Buffers: shared hit=8 VACUUM orders; Heap Fetches: 0Buffers: shared hit=4Heap Fetches counts the entriesit had to check in the heap. WHAT IT COSTS 990 status updates, pending → paidHOT updates80 → 0status sits in an index now 10,000 new orders, after a checkpointWAL written15 MB → 26 MB Between vacuums, changed pagescost Heap Fetches again.Autovacuum waits for 50 + 20%of the rows: 100,050 dead rowsin orders, by default. index orders_customer_id_created_at_idx1,927 pages · 15 MBON orders (customer_id, created_at) index orders_customer_covering_idx4,121 pages · 32 MB(customer_id, created_at) INCLUDE (id, status, total_thb) internal 3 root 210leaf 58leaf page 58 · 261 entries of 24 byteskey (customer_id, created_at) → heap tidBackward scan: newest entry first; an entry is key + tid root 209leaf 122leaf page 122 · 122 entries of 56 byteskeyidstatustotal_thbheap tidVM Each leaf entry now carries id, status and total_thb New entries (purple); the scan marked the old ones dead times: 2026, UTC⋮ 10 older entries of customer 42 4208-19 21:19465759delivered16730.67(2554,10) 4208-26 22:33471274delivered21369.53(4976,2) 4208-27 16:44471860delivered9372.75(1097,61) 4208-29 20:44473551delivered46395.20(820,82) 4209-01 14:17475685delivered14759.24(2189,75) 4209-01 14:40475698delivered22279.37(1695,29) 4209-08 14:44481174delivered15876.87(3826,75) 4209-14 04:00485504delivered12002.32(4384,29) 4209-24 15:54493709delivered21046.51(4806,99) 4209-26 12:58495179delivered51179.03(2392,2) 432024-12-31(32,24) 4209-24 15:54493709delivered20546.51(5057,91)4209-26 12:58495179delivered50679.03(5057,92) SIZE ON DISK pg_relation_size orders heap 5,058 pages · 40 MB orders_pkey (id) 1,374 pages · 11 MB orders_customer_id_created_at_idx 1,927 pages · 15 MB orders_customer_covering_idx 4,121 pages · 32 MB one leaf entry: 24 bytes → 56 bytes; per leaf page: 261 → 122INCLUDE columns are not in the root or internal pagesThe 3 columns as key columns also give 32 MB here.INCLUDE keeps a UNIQUE index unique on its key aloneand accepts types with no B-tree operator class. heap orders: 5,058 pages of 8 kB 40 MB visibility map, all-visible bits not read here heap page 2392 · 8 kB · 98 rowslpidcustomerstatustotal_thb121621074864delivered23546.07249517942delivered51179.03317351640972delivered18915.56417804361985cancelled35700.63⋮ 94 more rows, other customersOne row needed, one whole page read.The 20 rows sit on 20 pages spreadacross the whole table. Heap Fetches: 0 · not readThe scan reads 1 visibility map pageinstead: every page bit is set, so eachentry is answered from the index alone.VACUUM sets a bit when all rows on thepage are visible to everyone; any changeto the page clears it.INCLUDE columns are payload only• not searchable: WHERE status = …is a Filter, not an Index Cond• not sortable: ORDER BY total_thbadds a Sort• kept in leaf entries only• B-tree since PG 11, GiST 12, SP-GiST 14 heap page 5057, the last · 92 rowslpidcustomerstatustotal_thb8948658264140delivered43175.579021062992788delivered12651.189149370942delivered20546.519249517942delivered50679.03old versions on 2392 and 4806: deadVACUUM set page 5057 all-visible again.With only 2 dead rows it skipped theindex pass: 2392 and 4806 keep a deadstub until a later vacuum, and the scanskips their entries, marked dead. OTHER ENGINES MySQL 8.4 · InnoDBa secondary index entry4209-26 12:58495179← PKEvery entry carries the primary key, soSELECT id is covered. There is noINCLUDE: add status, total_thb as keycolumns. EXPLAIN shows Using index. The clustered index is the table, so alookup by id lands on the row itself. SQL ServerCREATE INDEX … (customer_id, created_at) INCLUDE (status, total_thb)Included columns sit at the leaf levelonly; the clustering key rides along.A column the index lacks costs oneKey Lookup per row.

Step 1 · One heap page per row. With an index on (customer_id, created_at), PostgreSQL 18.6 walks three index pages (root, internal, leaf) to customer 42's newest entries, all on leaf page 58. An entry holds only the key and the row's address (its tid), and the 20 rows sit on 20 different pages of the 40 MB heap: EXPLAIN (ANALYZE, BUFFERS) reports shared hit=23, 3 index pages and 20 heap pages for 20 rows. With a cold cache, that is 20 random reads.

Step 2 · INCLUDE: index-only scan. The covering index (customer_id, created_at) INCLUDE (id, status, total_thb) stores the selected columns in every leaf entry (56 bytes instead of 24), but not in the root and internal pages. The plan becomes an Index Only Scan with Heap Fetches: 0 and shared hit=4: 3 index pages and 1 visibility map page. Included columns can't guide the search or the sort, and id has to be included too, because a PostgreSQL index entry doesn't carry the primary key.

Step 3 · The visibility map decides. Index entries hold no visibility information, so for each entry the scan checks its heap page's all-visible bit and visits the heap only when the bit is clear. After an UPDATE of 2 of customer 42's orders, the new versions on page 5057 and the dead ones on pages 2392 and 4806 cost Heap Fetches: 4 (shared hit=8), and the scan marks the 2 dead entries as it passes. VACUUM sets page 5057 all-visible again, and the count drops to 0.

Step 4 · Costs and other engines. The covering index takes 32 MB instead of 15 MB, every insert writes the wider entry, and status updates can no longer be HOT: 80 of 990 were before, none after. Between vacuums, changed pages cost heap fetches again. In InnoDB every secondary index entry carries the primary key and the clustered index holds the rows; SQL Server has INCLUDE too, with the columns at the leaf level only.

The problem

Acme's mobile app opens on a customer's latest orders, and the list shows only three columns:

SELECT id, status, total_thb FROM orders
WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;

With an index on (customer_id, created_at), PostgreSQL finds the 20 entries quickly. It reads the root, one internal page and one leaf page, where customer 42's newest entries sit side by side (leaf page 58 here), and walks that leaf backward, newest first. But a B-tree entry holds only the key and the row's address, its tid (heap page and item number). To return id, status and total_thb, the scan has to read each row from the table, and customer 42's orders are spread through it among everyone else's: the 20 rows sit on 20 different pages of the 5,058-page, 40 MB heap.

On PostgreSQL 18.6, EXPLAIN (ANALYZE, BUFFERS) reports Buffers: shared hit=23: 3 index pages and 20 heap pages, a whole 8 kB page for every row. With all of it cached, that still ran in well under 0.1 ms (one run on a laptop). Once those pages are no longer in memory, opening the screen can cost 20 random reads. Without any index on customer_id, the same query read all 5,058 pages.

How it works

A covering index holds every column a query uses, so PostgreSQL can answer it from the index alone with an index-only scan. Since PostgreSQL 11, the INCLUDE clause adds columns to a B-tree index as payload: they are stored in each entry but are not part of the key.

CREATE INDEX orders_customer_covering_idx ON orders (customer_id, created_at)
  INCLUDE (id, status, total_thb);
  • The key still does the work. customer_id and created_at order the entries and serve the search and the sort. The included columns ride along in the leaf entries, which grew from 24 to 56 bytes here. Root and internal pages keep only key values, because B-tree suffix truncation always removes non-key columns from the upper levels.
  • Every column counts, id too. The planner considers an index-only scan only when every column the query references is in the index. A PostgreSQL index entry doesn't carry the primary key (an InnoDB one does), so with INCLUDE (status, total_thb) alone the plan stayed an Index Scan with 23 buffers. With id included it became Index Only Scan Backward with Heap Fetches: 0 and Buffers: shared hit=4: 3 index pages and 1 page of the visibility map.
  • Payload, not key. Included columns can't serve as an index search condition: adding AND status = 'cancelled' gave a Filter checked against all 179 of customer 42's entries (175 removed, still without touching the heap), not an Index Cond. They give no order either: ORDER BY total_thb added a Sort over the 179 rows. In a UNIQUE index they take no part in the uniqueness check.
  • The visibility map decides whether the heap is skipped. An index entry carries no visibility information: whether a row version is visible to a snapshot is recorded only in the heap. So PostgreSQL keeps a visibility map for each table, two bits per heap page, the first of which says that every row on the page is visible to all transactions. For each entry, the index-only scan looks up the bit of the entry's heap page. If it is set, the values come straight from the index; if not, PostgreSQL fetches the row from the heap to check it, and EXPLAIN ANALYZE counts the visit under Heap Fetches.
  • VACUUM sets the bits; writes clear them. Any insert, update or delete on a page clears its bit, and only VACUUM (or autovacuum) sets it again. In the run, an UPDATE that took 500 baht off 2 of customer 42's orders wrote the new row versions to page 5057 and left the old ones dead on pages 2392 and 4806, clearing 3 bits: Heap Fetches: 4 and shared hit=8, two visits to return the new versions and two to find the old ones dead. In passing, the scan marked those 2 dead entries in the index, so later scans skip them. After VACUUM, page 5057 was all-visible again and the count dropped to 0. With only 2 dead rows, VACUUM skipped its pass over the indexes (it does when very few rows are dead), so pages 2392 and 4806 kept a dead line pointer and stayed off the map until a later vacuum; no live entry pointed there anymore.

Try it

The sample data comes from samples/acme-shop, a script that builds the template database acme on PostgreSQL 18 in about a minute.

Make a fresh copy of the sample data from another database on the same server, then run the script in the copy. Buffer counts after each CREATE INDEX are from a second run of the EXPLAIN, because the first run also reads the new index into the buffer cache. The counts after the UPDATE are from its first run: that run marks the 2 dead entries, so a second one shows Heap Fetches: 2.

-- from another database:  CREATE DATABASE covering_index TEMPLATE acme STRATEGY FILE_COPY;
-- then, connected to covering_index (PostgreSQL 18):

VACUUM (ANALYZE) orders;  -- a fresh copy has an empty visibility map

PREPARE recent AS
  SELECT id, status, total_thb FROM orders
  WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;

CREATE INDEX orders_customer_id_created_at_idx ON orders (customer_id, created_at);
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF) EXECUTE recent;
-- Index Scan Backward using orders_customer_id_created_at_idx on orders
--   Index Cond: (customer_id = 42)
--   Buffers: shared hit=23               3 index pages + 20 heap pages

CREATE INDEX orders_customer_covering_idx ON orders (customer_id, created_at)
  INCLUDE (id, status, total_thb);
DROP INDEX orders_customer_id_created_at_idx;
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF) EXECUTE recent;
-- Index Only Scan Backward using orders_customer_covering_idx on orders
--   Heap Fetches: 0
--   Buffers: shared hit=4                3 index pages + 1 visibility map page

UPDATE orders SET total_thb = total_thb - 500 WHERE id IN (495179, 493709);
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF) EXECUTE recent;
--   Heap Fetches: 4                      new versions on page 5057, dead ones on 2392 and 4806
--   Buffers: shared hit=8

VACUUM orders;
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF) EXECUTE recent;
--   Heap Fetches: 0
--   Buffers: shared hit=4

SELECT pg_size_pretty(pg_relation_size('orders_customer_covering_idx'));
-- 32 MB   (orders_customer_id_created_at_idx was 15 MB)

When to use it

  • A hot, narrow query. A screen, an API endpoint or a lookup that runs thousands of times a minute and reads a few short columns through a selective index: a recent-orders list, a status check, a price lookup.
  • Pages that stay all-visible. The heap is skipped only where the visibility bit is set, so the payoff is largest on read-mostly or append-mostly tables that autovacuum keeps up with: history, reference data, orders that are rarely changed once delivered. On a table whose rows change all the time, the scan visits the heap anyway and the payload only costs.
  • A unique key that needs a column or two more. With CREATE UNIQUE INDEX … ON customers (email) INCLUDE (id, name), a lookup by email returns the customer's id and name from the index, and uniqueness still applies to email alone.
  • Not for wide or many columns. Including most of the row copies the table into the index, and reading the heap is cheaper than keeping a second copy current. For a read model with many columns or joins, a materialized view or a separate read store, as in CQRS, fits better.

Trade-offs

  • Size. The covering index took 32 MB (4,121 pages) against 15 MB (1,927 pages) for the plain two-column index: 122 entries per leaf page instead of 261, and twice the memory to keep it cached.
  • Every write pays. Each insert writes the wider entry: 10,000 new orders wrote 26 MB of WAL instead of 15 MB right after a checkpoint, when the first change to each page logs the whole page. An update of an included column can never be HOT (heap-only), because HOT requires that no indexed column changes: of 990 pending → paid updates, 80 were HOT with the plain index and none with the covering one, so every one of them added an entry to each index on the table.
  • The benefit lasts only as long as the visibility map. Between vacuums, changed pages cost heap fetches again. By default autovacuum processes a table once 50 rows plus 20 % of it are dead: 100,050 rows for orders. A table that depends on index-only scans may want a lower autovacuum_vacuum_scale_factor, set as a storage parameter on that table.
  • Limits. A B-tree entry can't exceed about a third of a page, so a wide payload can make inserts fail. Included columns can't be expressions, and B-tree deduplication (PostgreSQL 13 and later) is never used for an index with INCLUDE.

Implementation notes

  • Reading EXPLAIN. Look for Index Only Scan, then at Heap Fetches. A count close to the number of rows returned means the visibility map isn't helping, and the scan is an index scan in disguise. pg_class.relallvisible against relpages is the planner's estimate of the all-visible share, and the pg_visibility extension reads the map itself. Since PostgreSQL 18, EXPLAIN ANALYZE reports buffers without being asked. Reading plans in general is the subject of Query Execution Plans.
  • Vacuum after bulk loads. A freshly loaded or restored table has an empty visibility map: on a fresh copy of the sample data the plan already said Index Only Scan, yet showed Heap Fetches: 20 and 23 buffers, no better than the plain index, until VACUUM ran. Since PostgreSQL 13, inserts also trigger autovacuum, so append-only tables get their bits set too.
  • INCLUDE or key columns. Listing the columns as trailing key columns, (customer_id, created_at, id, status, total_thb), gave the same 32 MB here, because suffix truncation also drops trailing key columns from the upper pages when the leading ones are enough. Use INCLUDE when the extra columns must not change what is unique, when their types have no B-tree operator class, or to make the payload explicit; use key columns when queries also filter or sort on them, as in Composite Index. B-tree supports INCLUDE since PostgreSQL 11, GiST since 12 and SP-GiST since 14; the other index types are compared in Index Types, and the B-tree itself in B-Tree Index.
  • MySQL 8.4 (InnoDB). The table itself is a clustered index on the primary key, and every secondary index entry holds the primary key columns, which InnoDB uses to find the row. An index on (customer_id, created_at) therefore already covers SELECT id …, and a lookup by id reaches the row in the clustered index directly. There is no INCLUDE: add status and total_thb as trailing key columns, and EXPLAIN shows Using index when the query is covered. The visibility question is answered per index page: when a secondary index record is delete-marked, or its page was updated by a newer transaction, InnoDB looks the row up in the clustered index instead of answering from the index.
  • SQL Server. Nonclustered indexes accept INCLUDE (status, total_thb); included columns are stored at the leaf level only and don't count toward the key limits (32 key columns and 1,700 bytes since SQL Server 2016). On a table with a clustered index, each nonclustered entry carries the clustering key, much as in InnoDB. When the index lacks a column the query needs, the plan shows a Key Lookup (a RID Lookup on a heap table) for each row.
  • DynamoDB. A global secondary index stores a projection of each item, KEYS_ONLY, INCLUDE or ALL, and the table's key attributes are always in it. A query on a global secondary index can't fetch attributes that aren't projected, so the projection has to cover the query; see Amazon DynamoDB.
  • How UPDATE writes row versions and how VACUUM removes them is explained on the PostgreSQL page.