PostgreSQL Composite vs Covering Indexes: Examples

A PostgreSQL covering index contains the values a query needs to search and return. Use key columns for the lookup and INCLUDE columns for extra output values. Whether the query avoids heap access also depends on row visibility.

Choose key columns separately from INCLUDE columns

For a query filtering by customer and sorting by order date, those columns belong in the search keys. Return-only values can go in INCLUDE. Adding a value to INCLUDE does not make it a search key. Check the actual plan and heap fetch count after the index is built, then measure its write and storage costs before keeping it.

Begin with the query pattern

Write down the complete pattern: equality filters, range filters, joins, requested order, selected columns, result limit, and how often the operation runs. Then collect EXPLAIN (ANALYZE, BUFFERS) for representative parameter values. An index for a rare support report may not justify the write cost; an index that removes 300 ms from a checkout query may be essential.

Consider this common request:

SELECT id, created_at, total_cents
FROM orders
WHERE customer_id = $1
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 25;

The useful index must help PostgreSQL find one customer's paid orders and return the newest rows without sorting a large intermediate result.

Choose the index type from the operator

PostgreSQL's default B-tree index supports equality, ordering, and range comparisons. It is the first choice for identifiers, timestamps, numbers, and strings used with operators such as =, <, >, and BETWEEN.

Other types solve different search problems:

GIN: useful when a row contains many searchable values, including arrays, jsonb keys, and full-text search tokens. Updates can be more expensive than B-tree updates.
GiST: a framework used by data types and extensions for searches such as geometric overlap, ranges, and nearest-neighbor operations.
BRIN: stores summaries for physical block ranges. It can be tiny and effective for very large tables whose physical order correlates with the searched value, such as append-only timestamps.
Hash: supports equality comparisons. B-tree is usually more versatile, so choose hash only after measurement shows a reason.

The operator class matters too. An index type is not a promise that every operator on that data type will use it. Check the documentation for the data type or extension you use.

Order multicolumn indexes around access

For a B-tree index, leading columns determine which query shapes can efficiently narrow the scan. Equality columns normally come before range or ordering columns. For the example query, this is a reasonable candidate:

CREATE INDEX CONCURRENTLY idx_orders_customer_status_created
ON orders (customer_id, status, created_at DESC);

PostgreSQL can scan the entries for one customer and status in the requested time order, then stop after 25 rows. Reversing the order to (created_at, customer_id, status) would make the leading timestamp less useful for locating one customer's rows.

Do not create every permutation. Check the actual workload. An index on (customer_id, status) can support a query that filters only by customer_id, but it is generally not the right access path for a query that filters only by status. A separate index may be justified only if that second query matters.

Use partial indexes for stable subsets

A partial index contains rows that satisfy a predicate. If most orders are archived and the application repeatedly looks for open orders, a partial index can be smaller and cheaper to scan:

CREATE INDEX CONCURRENTLY idx_orders_open_customer_created
ON orders (customer_id, created_at DESC)
WHERE status = 'open';

The query predicate must imply the index predicate for PostgreSQL to use it. Parameterized or logically equivalent conditions are not always recognized the way a developer expects, so verify the plan. Partial indexes work best when the subset definition is stable and matches repeated queries.

Use covering indexes selectively

The INCLUDE clause stores payload columns that are not part of the search key:

CREATE INDEX CONCURRENTLY idx_orders_customer_created_cover
ON orders (customer_id, created_at DESC)
INCLUDE (id, status, total_cents);

This can enable an index-only scan, but "covering" does not guarantee one. PostgreSQL may still visit the heap to check visibility, especially on frequently updated pages. Wide payload columns also enlarge the index, reduce cache density, and increase write amplification. Include only the small columns needed by an important, frequent query.

Index expressions when the query uses the same expression

A normal index on email may not help a query that searches lower(email). An expression index can:

CREATE UNIQUE INDEX users_email_lower_unique
ON users (lower(email));

This both speeds case-normalized lookup and enforces the intended uniqueness rule. The expression adds work when rows are inserted or the underlying column changes. Keep the indexed expression deterministic and make the application query match it.

Recognize indexes that will not help

Low-selectivity filters: an index on a two-value boolean may be ignored when one value covers most rows. A partial index for the rare value can be better.
Leading-wildcard search: a B-tree index does not normally accelerate LIKE '%term%'. Consider full-text search or a trigram extension when the search requirement justifies it.
Small tables: reading the whole relation can be cheaper than visiting an index and heap.
Functions or casts: applying a function or mismatched type to an indexed column can prevent the intended access path unless a matching expression index exists.
Queries returning much of the table: an index is designed to avoid work. It offers little when most rows are required.

Measure write cost and duplicate coverage

Before adding an index, inspect existing definitions. Two indexes with the same leading keys may duplicate much of their value. After deployment, observe index scans, size, write volume, and query latency over a representative period. A zero scan count is useful evidence, but it is not enough by itself to drop an index: the index may enforce uniqueness, support an infrequent critical job, or not have been observed since statistics reset.

Create large production indexes with the database's online or concurrent mechanism where appropriate. In PostgreSQL, CREATE INDEX CONCURRENTLY reduces blocking of writes but takes longer, performs more work, and cannot run inside a transaction block. Monitor it and understand how to clean up a failed invalid index.

An index review checklist

Identify a frequent or business-critical slow query.
Capture its real plan and parameter distribution.
Choose the index type from the operators used.
Order B-tree keys around equality, range, and sort behavior.
Estimate index size and write amplification.
Create the index safely and refresh statistics if needed.
Compare latency, buffers, rows read, and write impact.
Document why the index exists and when to re-evaluate it.

For the broader measurement workflow, read PostgreSQL performance tuning.

Frequently asked questions

Should every foreign key have an index?

PostgreSQL does not automatically create one on the referencing columns. An index is often useful for joins and for checking or cascading changes to the referenced row, but the right decision depends on update/delete behavior and query patterns.

How many indexes are too many?

There is no universal count. Review total size, cache behavior, write latency, vacuum work, and overlap. A write-heavy table can feel the cost of every additional index.

Can the planner combine multiple indexes?

PostgreSQL can combine scans with bitmap operations. A purpose-built multicolumn index may still be faster when it matches an important query and supplies ordering, but measurement should decide.

Primary sources

Why a covering index can still fetch heap pages

Check two things separately: does the index contain every referenced column, and can PostgreSQL establish visibility from the visibility map? An index-only plan with heap fetches is possible when visibility needs checking. A missing selected column, however, prevents the index alone from supplying the result. See the PostgreSQL covering-index explanation.