PostgreSQL Composite Indexes and the Leftmost Prefix Rule

← Back to the section

The most common mistake with indexes is not "there is no index" but "the index exists, yet it does not work for this query." The cause is almost always the wrong column order in a composite index.

How a composite index is built

Imagine a phone book sorted by last name, then by first name within a last name, then by middle name within a first name. Finding all the "Smiths" is easy. Finding all the "Smith Alexanders" is easy too — you just jump to the right "Smiths." But finding all the "Alexanders" without a last name means flipping through the entire book.

A composite B-tree index works exactly the same way. An index (a, b, c) is physically sorted by a, then by b within each a, then by c within that.

This is the leftmost prefix rule: the index is used only if the condition includes the leftmost column (or several consecutive columns from the start).

The reason is in how the tree is built: entries are ordered by a first, and without a known a there is no way to step into the right place. What happens next is a matter of cost: sometimes the planner walks the whole index because it is smaller than the table, sometimes it reads the table. Both are a scan, only the object differs. PostgreSQL 18 added a skip scan that walks the distinct values of a and looks up b under each — it helps when there are few distinct a.

The layout of entries in an index is visible from an ordinary query. On an orders table, ORDER BY status, created_at gives exactly the order in which the entries of an index on (status, created_at) are stored.

live example

SELECT status, created_at, total_amount
FROM orders
ORDER BY status, created_at
LIMIT 8;

Groups by status come first, and only inside a group do the dates grow. Now let us ask by the second column — by date, without status:

live example

SELECT status, created_at
FROM orders
WHERE created_at >= '2026-08-10 00:00:00'
  AND created_at < '2026-08-15 00:00:00'
ORDER BY status, created_at;

Five rows match, and all five are in different statuses. In the tree they sit in five different places — the only way to collect them is to walk every group.

Order in WHERE does not matter

The order of conditions in a WHERE clause does not affect index usage — the PostgreSQL optimizer arranges the conditions in the right order itself.

-- index (a, b, c)
WHERE a = 1 AND b = 2 AND c = 3   -- uses the index
WHERE c = 3 AND b = 2 AND a = 1   -- uses the index in exactly the same way
WHERE b = 2 AND a = 1             -- works like WHERE a = 1 AND b = 2

In EXPLAIN you can see the difference between a condition checked against the index and a condition used as a filter. What separates them is not the order in the query but whether the column is part of the index. Here is the plan for WHERE a = 1 AND b = 2 AND d = 3 on that same index (a, b, c) — column d is not in it:

Index Cond: ((a = 1) AND (b = 2))   ← checked against index entries
Filter:     (d = 3)                  ← column d is not in the index
Rows Removed by Filter: 19999        ← rows read from the table for nothing

Index Cond is work inside the index. Filter is a check on rows the database has already fetched from the table — and it is those extra trips that cost.

How to choose the column order

Three rules apply here, and they work together.

Equality columns first

Columns that most queries filter with = go first. This gives the most precise navigation through the tree.

-- If 90% of queries filter by status:
CREATE INDEX ix_orders_status_created ON orders (status, created_at);

The popular advice "put the most selective column first" is misleading. The more accurate rule: the first column is the one that most often has an equality condition, and only after that do you look at selectivity.

Example: an orders table with filters on status (5 values, but in 90% of queries) and customer_id (a million values, but only in 10% of queries). It is better to create (status, created_at) for the main queries and a separate (customer_id) for the rest, than a single complex (customer_id, status, created_at).

Range conditions last

After a range condition (>, <, BETWEEN, LIKE 'prefix%'), the tree stops being used for the following columns.

-- good: range last
CREATE INDEX ix_orders_status_created ON orders (status, created_at);
WHERE status = 'NEW' AND created_at > now() - interval '1 day'
-- Index Cond: (status = 'NEW') AND (created_at > ...)

-- bad: range first, the entry point is lost
CREATE INDEX ix_orders_created_status ON orders (created_at, status);
WHERE status = 'NEW' AND created_at > now() - interval '1 day'
-- Index Cond: (created_at > ...) AND (status = 'NEW')

Both conditions landed in Index Cond, so at first glance there is no difference. But in the second case the database has to walk every index entry for the period and check status on each one — that can be a million entries for a thousand matching rows. In the first case it steps straight into the part of the tree that holds only NEW orders and reads exactly the slice it needs by date. EXPLAIN (ANALYZE, BUFFERS) shows the difference: on the same data the right column order read 8 pages, the wrong one 27.

Order matches ORDER BY

If a query often sorts its results, it makes sense to reflect that in the index — then PostgreSQL will not perform a separate sort.

CREATE INDEX ix_msg_user_at ON messages (user_id, created_at DESC);

-- the index works for both the filter and the sort — with no extra Sort operation
SELECT * FROM messages WHERE user_id = ? ORDER BY created_at DESC LIMIT 20;

If the index is created with ASC but the query asks for DESC, PostgreSQL can walk the index backwards (Index Scan Backward). This works, but it is slightly slower.

Duplicate indexes — extra overhead

If you already have an index (a, b, c), a separate index (a) is not needed: any query that would use (a) will use (a, b, c) in exactly the same way by the leftmost prefix rule.

Duplicate indexes:

• take up extra disk space;
• slow down INSERT, UPDATE, DELETE — every operation updates all indexes on the table;
• confuse the planner.

You can check for similar indexes like this:

live example

SELECT indexrelname, indrelid::regclass, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_index
JOIN pg_stat_user_indexes USING (indexrelid)
ORDER BY indrelid, indkey;

Index Only Scan is not always fast

When PostgreSQL uses an Index Only Scan, it seems like "the index worked." But that does not always mean a fast query.

Example: there is an index (status, created_at) and a query:

live example

SELECT count(1) FROM orders WHERE created_at > now() - interval '7 days';

In EXPLAIN it looks like this:

Index Only Scan using ix_orders_status_created on orders  (actual rows=26000)
  Index Cond: (created_at > '2026-06-20')
  Heap Fetches: 0
  Buffers: shared hit=270

Here created_at is the second column. Without a condition on status there is no way to step into the right place in the tree, so PostgreSQL walks the whole index and checks the condition on every entry. What gives the scan away is Buffers: 270 pages against eight for the same query that also has a condition on status. That is why EXPLAIN is run here as EXPLAIN (ANALYZE, BUFFERS) — without BUFFERS the difference is invisible.

Note also that there is no Rows Removed by Filter line here. When a column is part of the index, PostgreSQL checks the condition against the index entries and writes it into Index Cond — even when it has to check every one. A Filter line appears for columns that are not in the index: the database fetches the row from the table first and checks afterwards, counting the discarded rows in Rows Removed by Filter.

For such a query you need a separate index (created_at) or (created_at, status).

One more thing: Heap Fetches > 0 in an Index Only Scan means that PostgreSQL still went to the table for some of the rows — apparently the visibility map is stale. This is fixed by running VACUUM.

Covering index with INCLUDE

An ordinary composite index stores all of its columns in the tree. Sometimes you need to add columns for reading only — without affecting the order in the tree and without bloating the key. That is what INCLUDE is for.

CREATE INDEX ix_orders_customer_inc
    ON orders (customer_id) INCLUDE (status, created_at, total_amount);

-- PostgreSQL can answer the query from the index alone, without touching the table
SELECT customer_id, status, created_at, total_amount
FROM orders
WHERE customer_id = ?;

Columns from INCLUDE:

• do not affect the sort order in the tree;
• are stored only in the index leaves;
• let you avoid an extra table read (heap fetch).

Such an index is called a covering index. This is useful for report queries that read a fixed set of columns by a single condition.

FK without an index — a hidden trap

PostgreSQL does not create an index on a foreign key automatically. This means that when a parent row is deleted, PostgreSQL fully scans the child table — to check whether there are any references.

CREATE TABLE order_item (
    id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id bigint NOT NULL REFERENCES order_doc(id) ON DELETE CASCADE
);

-- without this index, DELETE FROM order_doc WHERE id = ? = a full scan of order_item
CREATE INDEX ix_order_item_order ON order_item (order_id);

On a large order_item table without an index, deleting a single parent row can take seconds and block other operations. You should always add an index on the FK column.

Functional index

An ordinary index stores column values as they are. If queries filter by the result of a function — for example, lower(email) for case-insensitive search — you need a functional index.

CREATE INDEX ix_account_email_lower ON account (lower(email));

-- the query must match the expression in the index exactly
SELECT * FROM account WHERE lower(email) = lower('IVAN@EXAMPLE.COM');

The same works with COALESCE, EXTRACT, and computed expressions. The key condition: the query must use the same expression as in the index definition.

In short

• A composite index (a, b, c) is a phone book by a → b → c: only the leftmost prefix works, a search without a means flipping through everything.
• The order of conditions in WHERE does not matter, the optimizer rearranges them; what matters is the column order in the index itself.
• Equality columns first, then range (>, <, BETWEEN): after a range condition the tree stops narrowing the search for the following columns.
• If a query sorts often, align ORDER BY with the order and direction of the columns, otherwise a separate Sort shows up in the plan.
• (a) next to (a, b, c) is redundant: space and slower writes. An Index Only Scan proves nothing by itself — the price of a scan shows up in Buffers.
• INCLUDE gives a covering index; an FK without an index means a scan of the child table on delete; a functional index is needed for lower() and the like, with the same expression in the query.

What to read next

• PostgreSQL index types — B-tree, GIN, GiST, BRIN: when to choose which.
• Selectivity and EXPLAIN ANALYZE — how PostgreSQL decides whether to use an index.
• VACUUM, autovacuum, and bloat — why Heap Fetches > 0 and how to fix it.
• Object naming — the ix_<table>_<columns> convention.