PostgreSQL Composite Indexes and Query Patterns

A composite (multicolumn) index is one index over several columns, like (customer_id, created_at). The question interviewers ask is rarely “what is it”. It’s “here are three queries, what index would you create and in what column order”, and the answer depends on understanding one thing: how a B-tree with two columns is sorted.

All the plans below come from PostgreSQL 16.15 on a table of one million synthetic orders.

Picture the index as a sorted phone book

A B-tree on (customer_id, created_at) stores entries sorted by customer_id, and within each customer, sorted by created_at. It’s a phone book ordered by surname, then first name. Finding “all Patels” is easy. Finding “all Patels named Ravi” is easy. Finding “everyone named Ravi” means reading the whole book, because Ravis are scattered across every surname.

That analogy predicts almost every result below. Here’s the setup:

CREATE TABLE orders (
  id          bigserial PRIMARY KEY,
  customer_id int NOT NULL,
  status      text NOT NULL,
  created_at  timestamptz NOT NULL,
  total       numeric(10,2)
);
-- 1,000,000 rows, ~20,000 customers, dates spread over 600 days
CREATE INDEX orders_customer_created ON orders (customer_id, created_at);
VACUUM ANALYZE orders;

The query this index was built for

“Show a customer’s 20 most recent orders” is the classic account-page query:

EXPLAIN (COSTS OFF)
SELECT id, created_at, total
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

 Limit
   ->  Index Scan Backward using orders_customer_created on orders
         Index Cond: (customer_id = 42)

No sort node. PostgreSQL jumps to customer 42’s section, which is already ordered by created_at, walks it backwards, and stops after 20 rows. That’s the best case: equality on the leading column, then the next column supplies the order. The rule of thumb that falls out of it is equality columns first, then the column you sort or range-filter on.

The same index also handles an equality plus a range on the second column:

EXPLAIN (COSTS OFF)
SELECT id FROM orders
WHERE customer_id = 42 AND created_at >= '2026-01-01';

 Bitmap Heap Scan on orders
   Recheck Cond: ((customer_id = 42) AND (created_at >= '2026-01-01 00:00:00+00'::timestamp with time zone))
   ->  Bitmap Index Scan on orders_customer_created
         Index Cond: ((customer_id = 42) AND (created_at >= '2026-01-01 00:00:00+00'::timestamp with time zone))

Both conditions appear in Index Cond, so both narrow the range of the index that’s read. (The bitmap scan is the planner’s choice for a few dozen scattered rows without an ORDER BY; it’s still using the composite index fully.)

Where column order stops helping

Filtering only on the second column. Count recent orders across all customers:

EXPLAIN (COSTS OFF)
SELECT count(*) FROM orders WHERE created_at >= '2026-08-01';

 Finalize Aggregate
   ->  Gather
         Workers Planned: 2
         ->  Partial Aggregate
               ->  Parallel Seq Scan on orders
                     Filter: (created_at >= '2026-08-01 00:00:00+00'::timestamp with time zone)

The index isn’t used. Recent orders are spread through every customer’s section, which is the “everyone named Ravi” problem. PostgreSQL can technically scan a whole multicolumn index with a condition on a later column, but the docs describe that as rarely worth it compared with a sequential scan. If this query matters, it needs its own index on created_at.

Note

PostgreSQL 18 added B-tree skip scan, which can use an index without a condition on its leading column when that column has few distinct values. On 16 and 17, which are still what most teams run, assume the leading column must be constrained.

A range on the first column, then sort by the second. Asking for customers 40 to 50 ordered by date:

EXPLAIN (COSTS OFF)
SELECT id FROM orders
WHERE customer_id BETWEEN 40 AND 50
ORDER BY created_at LIMIT 20;

 Limit
   ->  Sort
         Sort Key: created_at
         ->  Bitmap Heap Scan on orders
               Recheck Cond: ((customer_id >= 40) AND (customer_id <= 50))
               ->  Bitmap Index Scan on orders_customer_created
                     Index Cond: ((customer_id >= 40) AND (customer_id <= 50))

The index finds the rows, but there’s an explicit Sort. The entries are sorted by date within each customer, not across eleven customers, so the output order isn’t free anymore. Once a column is used as a range, the columns after it can’t provide ordering, and they’re much less effective as filters.

Columns that only filter

Add a status condition the index doesn’t cover:

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id FROM orders
WHERE customer_id = 42 AND status = 'refunded'
ORDER BY created_at DESC LIMIT 5;

 Limit (actual rows=4 loops=1)
   ->  Index Scan Backward using orders_customer_created on orders (actual rows=4 loops=1)
         Index Cond: (customer_id = 42)
         Filter: (status = 'refunded'::text)
         Rows Removed by Filter: 44

status shows up under Filter, not Index Cond, and Rows Removed by Filter: 44 tells you the cost: PostgreSQL visited 48 heap rows to return 4. For one customer that’s trivial. If the same pattern ran for a merchant with two million orders, it would be the slow query in your logs. Whether to make the index (customer_id, status, created_at) depends on how often this exact query runs; that order keeps both equalities first and created_at last for the sort.

Covering the query with INCLUDE

If the query reads only a couple of columns, you can store them in the index leaf pages so PostgreSQL never touches the table:

CREATE INDEX orders_cust_created_incl
  ON orders (customer_id, created_at) INCLUDE (total);

 Limit (actual rows=20 loops=1)
   ->  Index Only Scan Backward using orders_cust_created_incl on orders (actual rows=20 loops=1)
         Index Cond: (customer_id = 42)
         Heap Fetches: 0

Heap Fetches: 0 depends on the visibility map being current, which is why the experiment ran VACUUM first; on a busy table that count rises between vacuums. The price is size: on this table the plain index was 30 MB and the one with INCLUDE (total) was 39 MB, and every insert pays to maintain both.

Mistakes interviewers listen for

• “Index every column separately and let PostgreSQL combine them.” PostgreSQL can combine single-column indexes with a BitmapAnd, but it can’t use that to return rows in sorted order, and the combine step costs more than one well-ordered composite index.
• “Put the most selective column first.” It’s a common rule and it’s incomplete. Equality-versus-range and the required sort order matter more. created_at is far more selective than customer_id here, yet putting it first would ruin the account-page query.
• Duplicating prefixes. With (customer_id, created_at) in place, a separate index on (customer_id) alone is usually redundant, since the composite serves WHERE customer_id = ? too. Extra indexes slow writes and add vacuum work.
• Guessing instead of measuring. Build the index, then read EXPLAIN (ANALYZE, BUFFERS) for the real query with realistic parameters. The EXPLAIN ANALYZE guide covers how to read those numbers.

Interview practice: one index for three queries

A ticketing system runs these three queries constantly:

1. WHERE tenant_id = $1 AND status = 'open' ORDER BY created_at DESC LIMIT 50
2. WHERE tenant_id = $1 AND status = 'open' AND assignee_id = $2
3. WHERE tenant_id = $1 ORDER BY created_at DESC LIMIT 50

What index or indexes would you create?

Answer

Start from equality columns. Every query has tenant_id = $1, so it leads. Query 1 adds status = 'open' and sorts by created_at, so (tenant_id, status, created_at) serves it with no sort: two equalities, then the order column.

Query 3 skips status. With status in the middle, the index can still find the tenant’s section, but entries are ordered by status first, so a sort over all of that tenant’s tickets is needed. If tenants are large, give query 3 its own (tenant_id, created_at) index.

Query 2 can use the first index’s (tenant_id, status) prefix and filter assignee_id from there. That’s fine if a tenant has a few hundred open tickets. If tenants have tens of thousands, consider (tenant_id, assignee_id, status), or, since only open tickets matter, a partial index ON tickets (tenant_id, assignee_id) WHERE status = 'open'. I’d close by saying I’d confirm each choice with EXPLAIN ANALYZE on production-sized data and drop any index the plans never use.

Keep going

• SQL and PostgreSQL interview questions
• SQL and PostgreSQL MCQs
• PostgreSQL partial indexes
• Reading EXPLAIN ANALYZE
• Official docs: Multicolumn indexes