Composite and covering indexes

Part of the free LogicWiz SQL & Databases course, Module 14: Indexes and query plans. The queries and practice run in the app on real Postgres.

Kabir creates one index on (customer_id, ordered_on) and the order-history page flies. Then the "orders on 1 March" report, which filters only on ordered_on, is still slow. Same index, same column. Why does it help one query and not the other?

You'll learn to

• Explain how a composite index is sorted and the leftmost-prefix rule
• Choose the column order for a composite index
• Build a covering index with INCLUDE for an Index Only Scan
• Use selectivity to predict whether an index is worth it

Learn

A phone book sorted by two columns

An index on (customer_id, ordered_on) is sorted by customer_id first, and within each customer by ordered_on. Like a phone book sorted by surname, then first name.

• Find all Sharmas: easy, they are together.
• Find Sharma, Riya: easy, inside the Sharmas.
• Find every Riya, any surname: the book is no help. Riyas are scattered under every surname.

That is the leftmost-prefix rule: a composite index can jump straight to rows only when the query fixes its first column (and then the next, and so on, left to right).

Both columns used

EXPLAIN (COSTS OFF)
SELECT * FROM orders
WHERE customer_id = 42
  AND ordered_on >= DATE '2025-06-01';
EXPLAIN (COSTS OFF)
SELECT * FROM orders
WHERE customer_id = 42;

In the first plan both conditions are in the Index Cond: Postgres goes to customer 42, then to June inside that customer. The second query uses only the first column, which is a leftmost prefix, so the same index still works.

Second column alone: Seq Scan

EXPLAIN (COSTS OFF)
SELECT * FROM orders
WHERE ordered_on = DATE '2025-03-01';

Orders from 1 March are scattered under all 6,000 customers in the index, so Postgres can't jump to them. It reads the table instead. (When the first column has only a few distinct values, Postgres sometimes reads the whole index instead; that still isn't a jump, just a smaller full scan.)

What an index on (a, b) can do

Choosing the column order

1. Put columns tested with equality (=) before columns tested with a range (>, BETWEEN). After a range, later columns can't be used to jump.
2. Put first the column that queries use on its own most often, because (a, b) also serves WHERE a = ….
3. One (a, b) index often replaces a separate index on a. It does not replace an index on b.

For the order-history page (customer_id = ? AND ordered_on >= ?), (customer_id, ordered_on) is right on both counts.

Covering indexes and Index Only Scans

An index normally gives row addresses, and Postgres then visits the table for the other columns. If the index already holds every column the query needs, it can skip the table: an Index Only Scan.

INCLUDE adds extra columns to the leaf entries without making them part of the sort key:

CREATE INDEX … ON orders (customer_id) INCLUDE (amount);

Now "total spent by customer 42" never reads the table. Postgres also checks the visibility map to know a page has no recent changes; VACUUM keeps that map up to date.

A covering index in action

CREATE INDEX idx_orders_cust_amount ON orders (customer_id) INCLUDE (amount);
VACUUM orders;
EXPLAIN (COSTS OFF)
SELECT SUM(amount) FROM orders WHERE customer_id = 42;
EXPLAIN (COSTS OFF)
SELECT SUM(amount) FROM orders WHERE customer_id = 42 AND status = 'pending';

The first query needs only customer_id and amount: Index Only Scan. The second also needs status, which isn't in the index, so Postgres must visit the table again.

Selectivity: how many rows does a value match?

SELECT status,
       COUNT(*) AS orders,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct
FROM orders
GROUP BY status
ORDER BY orders DESC;

Selectivity is the share of rows a condition keeps. status = 'pending' keeps 1%: an index pays off. status = 'delivered' keeps 97%: reading the whole table in order is cheaper than 29,100 index jumps. Columns with many distinct values (order_id, customer_id, phone) are highly selective and make the best index keys.

Two separate indexes on a and on b are not the same as one index on (a, b). Postgres can combine them with a BitmapAnd, but that reads both indexes fully for the matching values; a composite index goes straight to the rows that match both. And an index on (a, b) does nothing for WHERE b = … alone.

In one line

A composite index (a, b) is sorted by a then b: it serves a and a + b but not b alone; equality columns first, ranges last; INCLUDE makes it covering for Index Only Scans.

Interview corner

What is the leftmost-prefix rule?

A composite index on (a, b, c) is sorted by a, then b, then c, so it can seek on a, (a, b) or (a, b, c), but not on b or c alone, just as a phone book sorted by surname can't find all people named Riya.

How do you pick the column order of a composite index?

Equality-filtered columns before range-filtered ones (a range stops further seeking), and the column most often queried alone goes first so the index also serves those queries. Selectivity matters less than these two rules.

What is a covering index?

An index that contains every column a query needs, so the database answers from the index alone (an Index Only Scan) without reading the table. In Postgres you add non-key columns with INCLUDE; MySQL InnoDB secondary indexes always carry the primary key.

What is selectivity?

The fraction of rows a condition matches. Low fraction (high selectivity, like an ID or phone) means an index is worth it; a condition that matches most of the table (like status = delivered) is cheaper as a sequential scan.