Covering Indexes for Foreign Key Joins #

A page shows an order with its 40 line items. The join from orders to order_lines does an index probe and then 40 heap fetches to read quantity and price. Multiply by 3,000 page views a minute and the heap fetches dominate the endpoint’s I/O. The same index, extended with two INCLUDE columns, answers the join from the index alone — and, incidentally, makes deleting an order fast.

Covering index design in general is in covering index design. Foreign key relationships are where a single well-chosen index pays off in three different places.

The Index Condition #

PostgreSQL indexes the referenced side of a foreign key automatically — the primary key or unique constraint the reference points at. It never indexes the referencing column. That one missing index affects:

Adding INCLUDE columns to that index turns the join probes into index-only scans when the query reads only those columns. The key stays (order_id), so the index remains the one the constraint check uses; the payload rides along.

Sizing matters. Each INCLUDE column widens every index entry, which costs storage, write time and — because deduplication is disabled for INCLUDE indexes — the compaction that repetitive keys would otherwise get, as described in B-tree deduplication and index size. Include only the columns the hot queries actually return.

Three uses of one foreign key index A stack showing one index on order_lines order_id with included quantity and price. It serves the parent to child join as an index-only scan, the child side lookup for one order, and the foreign key constraint check when a parent row is deleted. INDEX (order_id) INCLUDE (quantity, price) one index, three consumers join orders → order_lines index-only probes per order lookup lines of one order same access path DELETE FROM orders constraint check uses the key INCLUDE columns help reads; the key column is what the constraint needs

Annotated EXPLAIN Evidence #

Without any index on the referencing column:

EXPLAIN (ANALYZE, BUFFERS)
SELECT l.product_id, l.quantity, l.price
FROM orders o JOIN order_lines l ON l.order_id = o.id
WHERE o.id = 918204;
Hash Join  (actual time=6104.2..6104.8 rows=40 loops=1)
  Hash Cond: (l.order_id = o.id)
  ->  Seq Scan on order_lines l  (actual rows=412004110 loops=1)
  ->  Hash  ->  Index Scan using orders_pkey on orders o  (actual rows=1 loops=1)
Execution Time: 6105.1 ms
-- 412 million child rows scanned to find 40

With a plain index on the referencing column:

Nested Loop  (actual time=0.04..0.09 rows=40 loops=1)
  ->  Index Scan using orders_pkey on orders o  (actual rows=1 loops=1)
  ->  Index Scan using order_lines_order_id_idx on order_lines l  (actual rows=40 loops=1)
        Index Cond: (order_id = o.id)
        Buffers: shared hit=43            -- 3 index pages + 40 heap fetches
Execution Time: 0.11 ms

With INCLUDE columns:

CREATE INDEX CONCURRENTLY order_lines_order_id_cov_idx
  ON order_lines (order_id) INCLUDE (product_id, quantity, price);
  ->  Index Only Scan using order_lines_order_id_cov_idx on order_lines l  (actual rows=40 loops=1)
        Index Cond: (order_id = o.id)
        Heap Fetches: 0
        Buffers: shared hit=4             -- 40 rows answered from 4 index pages
Execution Time: 0.05 ms
Buffers per order across three configurations Three plan fragments are annotated. A sequential scan of 412 million child rows without an index. An index scan reading three index pages plus forty heap fetches. An index-only scan reading four index pages with no heap fetches. Seq Scan on order_lines (rows=412004110) no index on the referencing column Index Scan … Buffers: shared hit=43 3 index pages + 40 heap fetches Index Only Scan … Heap Fetches: 0 hit=4 payload served from the index the third form also serves constraint checks on delete

Step-by-Step Resolution #

  1. Find foreign keys without a supporting index using the catalog query in foreign key trigger cost in deletes.

  2. Create the plain index first if deletes are the urgent problem; it fixes constraint checks immediately:

    CREATE INDEX CONCURRENTLY order_lines_order_id_idx ON order_lines (order_id);

  3. Identify the hot child-side query’s projection. Look at the application’s actual select list, not the table definition.

  4. Add INCLUDE columns for that projection when the query volume justifies the extra width:

    CREATE INDEX CONCURRENTLY order_lines_order_id_cov_idx
  ON order_lines (order_id) INCLUDE (product_id, quantity, price);
DROP INDEX CONCURRENTLY order_lines_order_id_idx;

  5. Keep Heap Fetches at zero by vacuuming the child table often enough; without that, the INCLUDE columns buy nothing — see index-only scan eligibility rules.

  6. Measure the write cost. Compare insert throughput into the child table before and after; a wide covering index on a high-ingest table can cost more than the read saving.

Read cost against index size With no index, one order costs 1.2 million buffers through a sequential scan. A plain index on order_id costs 43 buffers and occupies 8.9 gigabytes. The covering index with three included columns costs 4 buffers and occupies 21 gigabytes. no index — buffers 1.2M buffers plain index — buffers 43 buffers, 8.9 GB index covering index — buffers 4 buffers, 21 GB index the covering index costs 12 GB more storage and write time

Before and After #

-- BEFORE: no index on order_lines.order_id
Hash Join → Seq Scan on order_lines (412M rows)                    Execution Time: 6105.1 ms

-- AFTER: INDEX (order_id) INCLUDE (product_id, quantity, price)
Nested Loop → Index Only Scan  Heap Fetches: 0  Buffers: hit=4     Execution Time: 0.05 ms

Composite foreign keys and partitioned children #

For a composite foreign key — (tenant_id, order_id) — the supporting index must lead with those columns in the constraint’s order. An index on (order_id, tenant_id) does not serve the check, because the check supplies both values but the planner needs the leading column to bound the scan. Keep the index column order identical to the constraint’s column order, then add INCLUDE columns after.

On partitioned child tables, each partition needs the index. Creating it on the partitioned parent creates it on every partition, but only CREATE INDEX (not CONCURRENTLY) works directly on the parent; the low-lock procedure is to build each partition’s index concurrently, then create the parent index ON ONLY the parent and ATTACH PARTITION each one. Foreign keys referencing a partitioned parent are enforced per partition, so a missing index on one partition is enough to make deletes slow.

Judging whether the payload is worth it #

A useful test before adding INCLUDE columns: multiply the child-side query’s frequency by the heap fetches it performs, and compare with the index’s added write volume — roughly the number of inserts and non-HOT updates per second times the added bytes per entry. For the order page above, 3,000 requests a minute times 40 heap fetches is 120,000 fetches a minute saved, against roughly 2,000 line inserts a minute each writing 24 bytes more of index. That trade is clearly worth it. For a child table ingesting 50,000 rows a second whose parent-side query runs once a minute, it is clearly not. When the numbers are close, keep the plain index: it is smaller, it deduplicates, and it still serves the constraint checks.

Common Pitfalls #

Indexing only for reads. The same index is what makes parent deletes viable. Diagnostic signal: fast joins, slow deletes. Fix: one index serving both, with the constraint columns first.

Wide INCLUDE lists. Every column widens every entry. Diagnostic signal: covering index several times the size of the key-only index. Fix: include only what the hot query returns.

Ignoring vacuum. INCLUDE columns cannot help while heap fetches are high. Diagnostic signal: Heap Fetches in the thousands. Fix: vacuum tuning for the child table.

Duplicate indexes. Keeping both the plain and the covering index doubles write cost. Diagnostic signal: two indexes with the same leading column. Fix: drop the narrower one after verifying usage.

Frequently Asked Questions #

Does PostgreSQL index foreign key columns automatically? #

No. It requires a unique index on the referenced columns, but the referencing column in the child table has no index unless you create one. That index is needed for joins, child-side lookups and parent deletes.

Should the foreign key index include extra columns? #

Include the columns the hot child-side queries return, so those queries become index-only scans. Keep the list short: each included column widens every index entry and adds write cost.

Does an INCLUDE column help the foreign key check itself? #

No. The check only needs to find rows by the key columns. Included columns help reads, and they do not interfere with the constraint check as long as the key columns come first.

Up: Covering Index Design