PostgreSQL Composite Indexes: Column Order Is Not a Suggestion

You have a composite index. It is in the wrong order.

I realize this is a presumptuous opening. But the statistics support the claim. The majority of composite indexes I encounter in production databases have their columns in a suboptimal order, and their owners are unaware of the consequence.

This is not a matter of opinion or taste. A composite index — an index on two or more columns — is a B-tree sorted first by the leftmost column, then by the second column within each group of the first, and so on, like nested filing cabinets. This is not a detail of the implementation. This is the implementation. The order of columns in your CREATE INDEX statement determines which queries the index can serve efficiently, which it can serve poorly, and which it cannot serve at all.

The difference between the right order and the wrong order is not a minor tuning gain. It is 23x on the benchmark you are about to see. And the covering index variant — where we take one additional step — delivers a 347x improvement over the worst approach. These are not theoretical figures. They are measured on 10 million rows, and the methodology is straightforward enough that you could reproduce them this afternoon.

If you'll permit me, I shall walk through exactly why column order matters, how to determine the correct order for your queries, what a covering index is and when to deploy one, and — because I believe honest advice requires honest boundaries — when a composite index is the wrong tool entirely.

Why does column order matter?

A B-tree composite index is sorted lexicographically — like a phone book sorted by last name, then first name. If you know the last name, you can quickly find all matching entries and then narrow by first name. If you only know the first name, the phone book is useless. "John" appears on every page, scattered across every letter of the alphabet. You would need to scan the entire book.

The same principle applies to your index. The leading column is the primary sort key. PostgreSQL can efficiently navigate the B-tree starting from the leftmost column, but it cannot skip the leading column to reach an inner one. This is not a limitation of PostgreSQL specifically — it is a fundamental property of how B-trees work. The data structure is sorted in a particular order, and queries must follow that order or forfeit the benefits of the sort.

This has a consequence worth emphasizing: two indexes with the same columns in different orders are functionally different indexes. They serve different queries. They have different performance characteristics. Swapping column order is not rearranging furniture — it is rebuilding the house with a different floor plan.

Wrong order: range column first

-- You run this query often:
SELECT * FROM orders
WHERE status = 'shipped'
  AND created_at > '2025-01-01';

-- You create this index:
CREATE INDEX idx_orders_date_status ON orders (created_at, status);

-- EXPLAIN ANALYZE:
Index Scan using idx_orders_date_status on orders
  (cost=0.43..8924.12 rows=4120 width=64)
  (actual time=0.08..42.31 rows=4087 loops=1)
  Index Cond: (created_at > '2025-01-01')
  Filter: (status = 'shipped')
  Rows Removed by Filter: 31204

The index is on (created_at, status). PostgreSQL uses the index to find all rows after January 1st — a large range — then filters each one by status. Notice Rows Removed by Filter: 31204. The index returned 35,291 rows to be examined, and 31,204 were discarded after the fact. The index did work. But it did 8.6 times more work than necessary, fetching rows only to throw them away.

This is the insidious part: the query plan shows an Index Scan, so it looks correct. You see "Index Scan" in the EXPLAIN output and assume all is well. It is not. An index scan that reads 35,000 rows to return 4,000 is a sequential scan wearing a disguise.

Correct order: equality column first

-- Same query, correct column order:
CREATE INDEX idx_orders_status_date ON orders (status, created_at);

-- EXPLAIN ANALYZE:
Index Scan using idx_orders_status_date on orders
  (cost=0.43..412.65 rows=4120 width=64)
  (actual time=0.03..1.82 rows=4087 loops=1)
  Index Cond: ((status = 'shipped') AND (created_at > '2025-01-01'))
  Buffers: shared hit=127

The index is on (status, created_at). PostgreSQL jumps directly to the "shipped" section of the B-tree — a precise equality lookup — then walks only the entries after January 1st within that section. No rows removed by filter. No wasted reads. Both conditions are satisfied by the index navigation itself, not by post-hoc filtering.

Same columns. Same query. 42ms versus 1.8ms. The difference is the order.

I should note: 1.8ms on 10 million rows is not merely "fast." It is fast enough that this query ceases to be a performance concern entirely. It is the difference between a query that appears on your monitoring dashboard and one that does not. Between a query that needs caching and one that does not need caching. That is the practical value of getting column order right.

The equality-first rule

The principle is straightforward, and I would encourage you to commit it to memory:

-- THE RULE: equality columns first, then range column last.

-- Query pattern:
WHERE status = 'shipped'          -- equality (=)
  AND region = 'us-east'          -- equality (=)
  AND created_at > '2025-01-01'   -- range (>)

-- Correct index:
CREATE INDEX idx_orders_status_region_date
  ON orders (status, region, created_at);
--           ^^^^^   ^^^^^^   ^^^^^^^^^^
--           eq #1   eq #2    range (last)

Equality conditions (=) produce exact index lookups. PostgreSQL descends the B-tree to a precise point: "show me the page where status is 'shipped' and region is 'us-east'." Range conditions (>, <, BETWEEN, LIKE 'prefix%') produce scans through a contiguous portion of the index. Once a range condition is encountered, all subsequent columns in the index can only be used as filters, not as navigation conditions.

This distinction — navigation versus filtering — is the entire mechanism. Let me be precise about what happens internally:

• Navigation (Index Cond) means PostgreSQL uses the column to descend the B-tree and find the starting point. This is logarithmic — O(log n). It is how B-trees earn their keep.
• Filtering (Filter) means PostgreSQL has already found a set of candidate rows and is now examining each one to check an additional condition. This is linear in the number of candidates. It is work, not navigation.

The equality-first rule ensures that every column participates in navigation for as long as possible. The moment a range condition appears, it defines the scan boundaries, and everything after it falls back to filtering.

What happens after the range column

This point deserves a concrete demonstration, because the abstract description leaves room for doubt.

-- What happens after the range column?
CREATE INDEX idx_example ON orders (status, created_at, region);

-- Query:
WHERE status = 'shipped'
  AND created_at > '2025-01-01'
  AND region = 'us-east';

-- EXPLAIN ANALYZE:
Index Scan using idx_example on orders
  (cost=0.56..1847.23 rows=4120 width=64)
  (actual time=0.05..14.72 rows=4087 loops=1)
  Index Cond: ((status = 'shipped') AND (created_at > '2025-01-01'))
  Filter: (region = 'us-east')
  Rows Removed by Filter: 8241

-- PostgreSQL navigated to status = 'shipped', scanned the date range,
-- but could only FILTER on region — not navigate to it.
-- The region column is stored in the index but not used for lookup.

The index is on (status, created_at, region). PostgreSQL navigates to status = 'shipped', scans the date range, but can only filter on region after the fact. The region column sits in the index — the data is right there — but PostgreSQL cannot use it to narrow the scan, because the B-tree sort order was interrupted by the range on created_at.

The correct order for this three-column query would be (status, region, created_at) — both equality columns first, range column last. That arrangement lets PostgreSQL navigate to status = 'shipped', then within that to region = 'us-east', and then scan the date range. Every column contributes to navigation.

The leftmost prefix rule

A composite index serves any query that uses a leading prefix of its columns. This is not a bonus feature — it is a direct consequence of the B-tree's sort order, and understanding it well can save you from creating redundant indexes.

-- Can one composite index serve multiple queries?
-- Yes — if the leading columns match.

CREATE INDEX idx_orders_status_region_date
  ON orders (status, region, created_at);

-- This index serves ALL of these:
WHERE status = 'pending';                                    -- uses column 1
WHERE status = 'pending' AND region = 'us-east';             -- uses columns 1-2
WHERE status = 'pending' AND region = 'us-east'
  AND created_at > '2025-01-01';                             -- uses columns 1-3

-- But NOT this:
WHERE region = 'us-east';                                    -- column 2 alone
-- The index is sorted by status first. Without status,
-- PostgreSQL cannot use the index efficiently.

An index on (status, region, created_at) is, in effect, three indexes bundled together: an index on (status), an index on (status, region), and the full index on (status, region, created_at). Any query that uses a leading prefix can navigate the B-tree as far as those columns allow.

This has a practical implication that I encounter frequently in production: teams create separate single-column indexes on status and region, then later add a composite on (status, region, created_at). The single-column index on status is now redundant — the composite already serves that query. The single-column index on region is not redundant, because region is not the leading column of the composite.

This is why I encourage thinking of column order as a design decision. The leading column of a composite index is the most versatile — it serves the widest range of queries. Choose it based on which column appears in the most query patterns, not which column has the highest cardinality.

The skip-scan question

I am asked this frequently enough that it merits a direct answer.

-- "But can't PostgreSQL skip-scan to use inner columns?"
-- As of PostgreSQL 17: no.

-- Some databases (Oracle, MySQL 8.0.13+) support skip-scan,
-- which allows using an index even when the leading column
-- is not in the WHERE clause. PostgreSQL does not.

-- If you need to filter on region alone:
WHERE region = 'us-east';

-- You need a separate index:
CREATE INDEX idx_orders_region ON orders (region);

-- Or restructure your composite so region leads:
CREATE INDEX idx_orders_region_status ON orders (region, status);

-- This is a genuine limitation. Plan your leading columns accordingly.

Some databases — Oracle in particular, and MySQL since 8.0.13 — implement a technique called skip-scan, which allows using an index even when the leading column is omitted from the query. PostgreSQL, as of version 17, does not implement skip-scan. There have been patches proposed, and it may arrive in a future version, but today you must design your indexes with the leftmost prefix rule as a hard constraint.

This is an honest limitation. If your workload has multiple query patterns that filter on different columns, and no single column leads all of them, you will need multiple indexes. A composite index is not a universal accelerator — it is a precise tool that accelerates a specific family of queries sharing a common prefix.

Composite indexes and ORDER BY

There is a benefit to composite indexes that is often overlooked: they can eliminate sort operations entirely.

-- Composite indexes serve ORDER BY, not just WHERE.

CREATE INDEX idx_orders_status_date ON orders (status, created_at);

-- This query gets both filter AND sort from the index:
SELECT * FROM orders
WHERE status = 'shipped'
ORDER BY created_at DESC
LIMIT 20;

-- EXPLAIN:
Index Scan Backward using idx_orders_status_date on orders
  (cost=0.43..1.87 rows=20 width=64)
  (actual time=0.02..0.05 rows=20 loops=1)
  Index Cond: (status = 'shipped')
  Buffers: shared hit=3

-- No Sort node. The index delivers rows in the right order.
-- Without this index, PostgreSQL would scan, sort, then discard — wasteful.

When the index columns match both the WHERE clause and the ORDER BY clause, PostgreSQL can read from the index in sorted order and skip the Sort node entirely. On a query with LIMIT 20, this is the difference between reading 20 index entries and returning immediately, versus scanning thousands of matching rows, sorting them in memory, and then discarding all but 20.

For paginated APIs and dashboard queries — which tend to filter on a status or category and sort by a timestamp — a composite index that matches both the filter and the sort order is one of the most impactful optimizations available. Three buffer reads for 20 rows. That is not optimization. That is the index doing exactly what it was designed to do.

Covering indexes with INCLUDE

A covering index includes not just the columns you filter on, but also the columns you read. When PostgreSQL can answer a query entirely from the index — without fetching the heap (table) row — it performs an Index Only Scan, which is the fastest possible access path.

-- A covering index includes columns the query needs to read,
-- not just the columns it needs to filter on.

-- This query:
SELECT id, total FROM orders
WHERE status = 'pending'
  AND created_at > '2025-01-01';

-- This index answers it without touching the table:
CREATE INDEX idx_orders_covering
  ON orders (status, created_at)
  INCLUDE (id, total);

-- EXPLAIN:
Index Only Scan using idx_orders_covering on orders
  (actual time=0.02..0.91 rows=4087 loops=1)
  Index Cond: ((status = 'pending') AND (created_at > '2025-01-01'))
  Heap Fetches: 0

Heap Fetches: 0 — PostgreSQL never touched the table. The answer came entirely from the index. For a query returning 4,087 rows, that is 4,087 heap lookups avoided. Each heap lookup is a random I/O operation (or a buffer cache hit, but the point stands) — eliminating them entirely is a qualitative improvement, not merely a quantitative one.

INCLUDE versus adding columns to the key

The INCLUDE clause, available since PostgreSQL 11, adds columns to the index's leaf pages without including them in the sort key. This distinction matters.

-- INCLUDE columns vs key columns: a critical distinction.

-- Key columns (in the main index definition):
--   • Participate in the B-tree sort order
--   • Can be used for navigation (Index Cond)
--   • Increase the index's branching width

-- INCLUDE columns (in the INCLUDE clause):
--   • Stored only in leaf pages
--   • Cannot be used for navigation or sorting
--   • Do not affect the tree structure
--   • Exist solely to avoid table lookups

-- Key column version (wider tree, can sort by total):
CREATE INDEX idx_key ON orders (status, created_at, total);

-- INCLUDE version (narrower tree, cannot sort by total):
CREATE INDEX idx_include ON orders (status, created_at) INCLUDE (total);

-- Use INCLUDE when you need the column for SELECT but not for WHERE or ORDER BY.

If you add total as a key column — (status, created_at, total) — it becomes part of the B-tree's sort order. This makes the tree wider at every level, which means more pages to traverse and a larger index on disk. It also means you can use total in ORDER BY or WHERE clauses, which may or may not be useful.

If you add total via INCLUDE — (status, created_at) INCLUDE (total) — it is stored only in the leaf pages. The internal nodes of the B-tree remain narrow. The index is smaller, navigation is faster, and total is available for reading but not for sorting or filtering.

The rule: use INCLUDE for columns you SELECT but do not filter or sort on. Use key columns for columns that participate in WHERE, ORDER BY, or join conditions.

When covering indexes are worth the trade-off

Covering indexes are not free. Every column you add increases the index size on disk, which means more memory consumed in the buffer cache, longer CREATE INDEX operations, and more write overhead on every INSERT and UPDATE.

Use covering indexes for your most critical queries — the ones that run thousands of times per day and need to be as fast as possible. A dashboard query that executes 50,000 times daily and returns id and total? That is a strong candidate for a covering index. A monthly report that runs once? The 0.9ms improvement over 1.8ms is not worth the ongoing write cost.

There is also a visibility map consideration. Index Only Scans check the visibility map to determine whether a heap fetch is needed to verify row visibility. On tables with heavy UPDATE activity, the visibility map may be stale, and PostgreSQL will fall back to heap fetches even with a covering index. Running VACUUM (or ensuring autovacuum runs frequently enough) keeps the visibility map current and the Index Only Scans genuinely heap-free.

The benchmarks, for the numerically inclined

Ten million rows in the orders table. Five index strategies. Same query: WHERE status = 'shipped' AND created_at > '2025-01-01', selecting id and total.

The progression tells the story. No index: 312ms. Single-column: 18ms. Wrong composite order: 42ms — worse than the single-column index, because the range-first composite scans a wider index range and then filters. Correct composite order: 1.8ms. Covering index: 0.9ms.

The difference between the worst composite and the best is 347x. Between the two composites — same columns, different order — 23x. Between the correct composite and the covering variant — an additional 2x, achieved by eliminating heap fetches entirely.

I should draw particular attention to the wrong-order composite being worse than the single-column index. This is the detail that alarms most engineers, and rightly so. A composite index is not inherently better than a single-column index. If the column order does not match the query pattern, the composite reads a wider range of the index (because its leading column is the range predicate), performs more I/O, and delivers a slower result. You have paid the write overhead of a wider index and received negative value in return. That is not a trade-off. That is a loss.

When NOT to use a composite index

I have spent considerable time explaining when and how to use composite indexes. I should be equally forthcoming about when they are the wrong choice, because a waiter who only recommends his own speciality is not serving the guest — he is serving himself.

-- When a single-column index is the better choice:

-- 1. The column is queried independently, not in combination.
--    If you only ever filter on status alone:
WHERE status = 'pending';
--    A single-column index on (status) is simpler and sufficient.

-- 2. The column appears in many different query patterns.
--    A column like user_id that appears in dozens of different queries
--    is better served by its own index than by being the third column
--    in a composite that only helps one specific pattern.

-- 3. The table is write-heavy and the query is infrequent.
--    A composite index on a table receiving 50,000 inserts/second
--    for a query that runs twice a day is a poor trade.

-- 4. You already have too many indexes.
--    Check pg_stat_user_indexes for unused indexes:
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan ASC;
--    If idx_scan is 0, the index is dead weight.

The core tension is this: every index you add improves some reads and degrades all writes. A composite index, being wider than a single-column index, imposes a proportionally larger write penalty.

-- Every index you add slows every INSERT, UPDATE, and DELETE.

-- Benchmark: inserting 100,000 rows into the orders table.

-- 0 indexes:  1.2 seconds
-- 1 index:    1.4 seconds  (+17%)
-- 3 indexes:  2.1 seconds  (+75%)
-- 6 indexes:  3.8 seconds  (+217%)
-- 10 indexes: 6.9 seconds  (+475%)

-- Each composite index is wider than a single-column index,
-- so its write overhead is proportionally larger.

-- The question is never "should I add this index?"
-- It is "does the read improvement justify the write cost?"

On a read-heavy OLAP workload with batch inserts, six indexes might be perfectly acceptable — the reads happen millions of times, the writes happen once in bulk. On a write-heavy OLTP workload processing 10,000 transactions per second, every additional index is a measurable drag on throughput.

There is no universal number of indexes that is "too many." There is only the balance between read performance and write overhead for your specific workload. I would be a poor advisor indeed if I suggested otherwise.

The partial index alternative

If your composite index exists primarily to serve queries on a specific subset of the data — active orders, pending shipments, unread notifications — consider a partial index instead. I have prepared a separate guide on PostgreSQL partial indexes that covers the technique in detail. A partial index on (created_at) WHERE status = 'pending' is dramatically smaller than a composite on (status, created_at), because it only indexes the rows that match the WHERE clause. On a 10-million-row table where 50,000 rows are pending, the partial index is roughly 200x smaller. It is faster to scan, faster to maintain on writes, and uses a fraction of the memory.

The trade-off: a partial index serves only the queries whose WHERE clause matches or implies the index's predicate. If you need to filter on multiple different status values, you would need multiple partial indexes or a single composite. The right choice depends on your query patterns — as it always does.

Common mistakes with composite indexes

I have catalogued these from years of encountering them in production. They are not theoretical — they are the mistakes I see most often, and each one has a straightforward remedy.

• Indexing every column in the WHERE clause as separate single-column indexes. PostgreSQL can combine single-column indexes via BitmapAnd, but a single well-designed composite index is almost always faster. The BitmapAnd approach reads two separate indexes, builds two bitmaps in memory, intersects them, and then fetches the matching heap pages. A composite index does this in a single B-tree traversal. More I/O, more CPU, more latency — for no gain.
• Including high-cardinality columns first out of habit. Cardinality matters for single-column indexes, but for composites, the query pattern matters more. If your query always filters on status (5 distinct values) and then ranges on created_at, put status first — even though created_at has vastly higher cardinality. The equality-first rule trumps the cardinality heuristic every time.
• Creating duplicate indexes unwittingly. An index on (status, created_at) already serves queries that only filter on status. A separate index on (status) alone is redundant — it consumes disk, slows writes, and provides no benefit. Query pg_stat_user_indexes for indexes with zero scans. They are dead weight.
• Forgetting that ORDER BY benefits from composite indexes. If your query filters on status and sorts by created_at DESC, the index on (status, created_at) provides both the filter and the sort order. No Sort node in the plan. This is especially impactful for paginated queries with LIMIT — the index delivers the first N rows directly, without sorting the entire result set.
• Adding columns to a composite index "just in case." A four-column composite that exists to serve a two-column query is wider than necessary. The extra columns do not help navigation (they come after the columns the query actually uses), but they do increase the index size and write overhead. Every column in an index should earn its place.

Letting the traffic decide

The optimal composite index depends entirely on your actual query patterns. A composite that is perfect for one query may be useless for another. This is why index design requires knowing your workload — not just your schema.

I have laid out the principles: equality columns first, range column last, use INCLUDE for read-only columns, check the leftmost prefix rule, weigh the write overhead. These principles are correct and complete. Apply them methodically, and your composite indexes will be well-designed.

The difficulty is not the principles. It is that query patterns change. New features introduce new WHERE clauses. Traffic shifts from one endpoint to another. The composite index you designed for last quarter's dominant query may be irrelevant to this quarter's. And the index you need for this quarter's dominant query does not exist yet, because you have not looked at the traffic recently.

Gold Lapel observes the actual queries your application sends and identifies the patterns: which columns appear together in WHERE clauses, which are equalities, which are ranges, what the selectivity is. When the evidence is clear, it creates the appropriate composite index — columns in the right order, covering the right queries. When the workload changes, it adapts. When an index is no longer earning its keep, it is retired.

The goal is not to create indexes for every possible query. It is to create the minimum set of indexes that covers the maximum amount of actual traffic. That requires knowing the traffic — something a proxy is uniquely positioned to observe. And it requires knowing when an index is doing more harm than good — something that requires measuring both the read benefit and the write cost, continuously.

The principles in this article will serve you well. They are the same principles Gold Lapel applies, and they work whether you apply them manually or let the system apply them on your behalf. The question is not whether you know the rules — you do now. The question is whether you wish to be the one checking the traffic at three in the morning to see if the rules still apply.