Indexes That Do More: Composite, Covering, Partial

A single-column B-tree is the workhorse, but three variations on it solve most of the problems that a plain index cannot. A composite index spans several columns at once. A covering index carries extra columns so the query never touches the table. A partial index covers only the rows matching a condition. Each is a small change to CREATE INDEX, and each has a rule that decides exactly when it applies. This chapter shows all three as real plans, because the rules are precise and the plans are how you check you got them right.

Composite indexes and the leftmost prefix

A composite index sorts by more than one column, in the order you list them. Build one on (customer_id, order_date):

CREATE INDEX idx_orders_cust_date ON orders(customer_id, order_date);
ANALYZE orders;

The mental model is a phone book sorted by last name, then first name. It is trivially useful for “everyone named Patel,” and for “Patel, Anjali.” It is useless for “everyone named Anjali,” because first names are scattered throughout. A composite B-tree works the same way: it is sorted by the first column, and only sorted by the second within a fixed value of the first. This gives the leftmost-prefix rule: the index serves a query that constrains a leading run of its columns, and only that.

Filter on the first column alone, and the index applies:

EXPLAIN (ANALYZE) SELECT * FROM orders WHERE customer_id = 42;

 Bitmap Heap Scan on orders  (actual time=0.036..0.060 rows=8.00 loops=1)
   Recheck Cond: (customer_id = 42)
   ->  Bitmap Index Scan on idx_orders_cust_date
         Index Cond: (customer_id = 42)

Filter on both columns, and it applies even better, using both as an Index Cond:

EXPLAIN (ANALYZE) SELECT * FROM orders WHERE customer_id = 42 AND order_date >= '2025-01-01';

 Bitmap Heap Scan on orders  (actual time=0.028..0.037 rows=4.00 loops=1)
   Recheck Cond: ((customer_id = 42) AND (order_date >= '2025-01-01'::date))
   ->  Bitmap Index Scan on idx_orders_cust_date
         Index Cond: ((customer_id = 42) AND (order_date >= '2025-01-01'::date))

Both conditions are resolved by the index. But filter on the second column alone, skipping the first, and watch it fall apart:

EXPLAIN (ANALYZE) SELECT * FROM orders WHERE order_date >= '2025-09-01';

 Seq Scan on orders  (cost=0.00..1133.00 rows=1673 width=20)
                     (actual time=0.011..3.219 rows=1641.00 loops=1)
   Filter: (order_date >= '2025-09-01'::date)
   Rows Removed by Filter: 58359

A sequential scan, despite the query being highly selective (1,641 of 60,000 rows, under 3%) and despite order_date sitting right there in the index. The index cannot help, because order_date is only sorted within each customer_id, and this query fixes no customer_id. The dates are smeared across the whole index in no usable order. This is the rule that catches people: a composite index on (a, b) accelerates WHERE a, and WHERE a AND b, but not WHERE b alone. Column order in a composite index is a design decision, not a formality. Put the column you always filter on first.

Covering indexes and the index-only scan

Even a perfect index scan usually does two steps: find the row’s location in the index, then fetch the full row from the table (the “heap”) to get the other columns. That second step is the Bitmap Heap Scan or the heap fetch inside an Index Scan. If every column the query needs is already in the index, the second step is unnecessary. Postgres can answer from the index alone.

The INCLUDE clause adds columns to an index as payload — stored in the leaves, but not part of the sort key:

CREATE INDEX idx_orders_cust_incl ON orders(customer_id) INCLUDE (order_date, status);
VACUUM ANALYZE orders;

Now a query that selects only customer_id, order_date, and status needs nothing from the table:

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, order_date, status FROM orders WHERE customer_id = 42;

 Index Only Scan using idx_orders_cust_incl on orders
        (cost=0.41..4.62 rows=12 width=17) (actual time=0.075..0.077 rows=8.00 loops=1)
   Index Cond: (customer_id = 42)
   Heap Fetches: 0
   Buffers: shared hit=1 read=3

The node type is now Index Only Scan, and the line that proves it worked is Heap Fetches: 0. Not one trip to the table. Every value came from the index leaves. Contrast a SELECT * of the same rows, which needs columns the index doesn’t carry and so must visit the heap:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42;

 Bitmap Heap Scan on orders  (actual time=0.029..0.056 rows=8.00 loops=1)
   Recheck Cond: (customer_id = 42)
   Heap Blocks: exact=8

That one reads the table. The covering version does not.

Two things to know about Heap Fetches. First, watch it: if it is not zero, the index-only scan is still dipping into the table and you are getting less benefit than you think. Second, it depends on VACUUM. Postgres can skip the heap only for rows it knows are visible to every transaction, which it tracks in a visibility map that VACUUM maintains. That is why the VACUUM ANALYZE above was not decoration. On a table with lots of recent writes and no vacuum, an index-only scan quietly reverts to fetching from the heap to check visibility, and Heap Fetches climbs. Covering indexes and vacuum are linked, a thread this series picks up again later.

Partial indexes

The last variation indexes fewer rows, not more columns. A partial index has a WHERE clause, and it stores entries only for rows that match. If your queries always target a small, well-defined slice of a table, there is no reason to index the rest.

Orders that are still placed — not yet shipped — are the ones an operations dashboard queries constantly, and they are about a sixth of the table. Index just those:

CREATE INDEX idx_orders_placed ON orders(order_date) WHERE status = 'placed';
ANALYZE orders;

The first payoff is size. Against a full index on the same column:

       relname        | pg_size_pretty
----------------------+----------------
 idx_orders_date_full | 432 kB
 idx_orders_placed    | 112 kB

Roughly a quarter the size, because it holds a quarter the rows. A smaller index is faster to scan, cheaper to cache, and lighter on every write that doesn’t touch a placed row. A query whose filter matches the partial condition uses it:

EXPLAIN (ANALYZE) SELECT * FROM orders WHERE status='placed' AND order_date >= '2025-09-01';

 Bitmap Heap Scan on orders  (actual time=0.087..0.437 rows=278.00 loops=1)
   Recheck Cond: ((order_date >= '2025-09-01'::date) AND (status = 'placed'::text))
   ->  Bitmap Index Scan on idx_orders_placed
         Index Cond: (order_date >= '2025-09-01'::date)

Notice the Index Cond is only order_date: the status = 'placed' part is implied by the index’s own definition, so it costs nothing at query time. But a query for a different status cannot use this index at all, because those rows simply are not in it:

EXPLAIN (ANALYZE) SELECT * FROM orders WHERE status='delivered' AND order_date >= '2025-09-01';

 Seq Scan on orders  (actual time=0.011..3.360 rows=651.00 loops=1)
   Filter: ((order_date >= '2025-09-01'::date) AND (status = 'delivered'::text))
   Rows Removed by Filter: 59349

Back to a sequential scan. The partial index knows nothing about delivered orders, which is precisely the point. You built it to serve one class of query cheaply, and it does, while staying out of the way of everything else. Partial indexes are also the standard tool for indexing “active” rows, or enforcing uniqueness on a subset (a unique partial index on WHERE deleted_at IS NULL).

A portability note: composite and partial indexes exist across the major engines, but INCLUDE is younger. Postgres has had it since 11 and SQL Server longer. MySQL has no INCLUDE, though its clustered primary key means secondary indexes implicitly carry the key columns, achieving something similar by a different route.

Final thoughts

Three moves take a plain B-tree a long way. Composite indexes serve a leftmost run of their columns, so column order is a real decision — put the always-filtered column first. Covering indexes with INCLUDE turn a lookup into an Index Only Scan with Heap Fetches: 0, as long as vacuum keeps the visibility map current. Partial indexes cover only the rows you query, trading generality for a smaller, faster, cheaper structure. All three are B-trees underneath. Next we leave the B-tree behind for the index types that handle what it cannot: documents, arrays, full text, and ranges.

Next: Beyond the B-tree: GIN and GiST