PostgreSQL Compound Indexes Explained

A compound index in PostgreSQL is just an index built on more than one column.

PostgreSQL calls these multicolumn indexes, but most developers also call them:

• compound indexes
• composite indexes
• multi-column indexes

They all point to the same idea.

And that idea matters a lot, because compound indexes are one of the most useful PostgreSQL tools for speeding up real application queries.

They are also one of the easiest indexing features to misuse.

A good compound index can:

• make common filters much cheaper
• support ORDER BY efficiently
• improve tenant-scoped queries
• reduce heap visits
• and make “latest N rows for X” style queries dramatically faster

A bad one can:

• waste space
• slow writes
• never get used
• and leave developers confused about why “the index exists but the query is still slow”

This guide explains how compound indexes actually work in PostgreSQL, when they help, when they do not, and how to design them around real query patterns.

What a Compound Index Is

A compound index is an index on more than one column.

Example:

create index idx_orders_account_created
on orders (account_id, created_at);

That index stores entries ordered by:

1. account_id
2. then created_at within each account_id

This is different from having two separate indexes:

create index idx_orders_account_id on orders (account_id);
create index idx_orders_created_at on orders (created_at);

Those are not equivalent.

A compound index captures a combined access path. Two separate indexes capture two separate access paths.

That is why compound indexes are often the right answer when the application repeatedly uses a specific column combination together.

Why Compound Indexes Matter

Many real application queries do not filter on just one column.

They do things like:

• scope by tenant, then sort by time
• filter by status and date
• filter by foreign key and order by recency
• join on one column and filter on another
• retrieve the newest matching rows quickly with LIMIT

Examples:

select *
from orders
where account_id = $1
order by created_at desc
limit 20;

select *
from jobs
where queue_name = $1
  and processed_at is null
order by created_at
limit 100;

select *
from events
where tenant_id = $1
  and event_type = $2
  and occurred_at >= now() - interval '7 days';

These are exactly the kinds of queries where a well-designed compound index can be much more useful than a pile of unrelated single-column indexes.

PostgreSQL Term: Multicolumn Indexes

PostgreSQL’s docs use the term multicolumn indexes. PostgreSQL supports multicolumn indexes for B-tree, GiST, GIN, and BRIN, and indexes can have up to 32 columns total when INCLUDE columns are counted too. :contentReference[oaicite:1]{index=1}

That does not mean you should build giant 12-column indexes.

In fact, PostgreSQL’s docs explicitly caution that multicolumn indexes should be used sparingly, and that indexes with more than three columns are unlikely to help unless the table’s usage is extremely stylized. :contentReference[oaicite:2]{index=2}

That is a very practical rule.

The Most Important Rule: Column Order Matters

This is the part many developers get wrong.

For B-tree compound indexes, column order is critical.

PostgreSQL’s docs explain that a multicolumn B-tree index is most efficient when constraints exist on the leading leftmost columns. Equality constraints on leading columns, plus any inequality constraint on the first column without an equality condition, are what most strongly limit the part of the index that has to be scanned. :contentReference[oaicite:3]{index=3}

That means:

Index

(account_id, created_at)

is great for queries like:

where account_id = 42
order by created_at desc
limit 20

and also:

where account_id = 42
  and created_at >= now() - interval '30 days'

But it is much less naturally aligned with a query like:

where created_at >= now() - interval '30 days'

because account_id is the leftmost column and the query does not constrain it.

A Simple Mental Model

For B-tree compound indexes, think like this:

• PostgreSQL first uses the leftmost column(s) to narrow where it should look
• then uses the later column(s) inside that narrowed range
• later columns still help, but the leftmost columns matter most for efficiently shrinking the scan

That is why compound index design must start from:

• real filters
• real sort order
• real query repetition

not from:

• “these columns seem important”

Example: Good Compound Index

Query:

select id, status, created_at
from orders
where account_id = $1
order by created_at desc
limit 20;

Strong index:

create index idx_orders_account_created_desc
on orders (account_id, created_at desc);

Why this is strong:

• account_id is the leading filter
• created_at desc matches the order
• limit 20 benefits because PostgreSQL can often get the first matching rows quickly instead of sorting a big result set

This is a classic compound-index win.

Example: Weak Compound Index

Same query:

select id, status, created_at
from orders
where account_id = $1
order by created_at desc
limit 20;

Weaker index:

create index idx_orders_created_account
on orders (created_at desc, account_id);

This is usually a worse fit because the leftmost column is no longer the main filter column.

The index is not “useless,” but it is usually not shaped around the query as well as the first version.

Equality First, Then Range or Sort

A very practical B-tree design pattern is:

• equality-filter columns first
• then range or sort columns

That often leads to good index shapes like:

(tenant_id, status, created_at desc)

for queries like:

where tenant_id = $1
  and status = 'active'
order by created_at desc
limit 50;

This pattern works well because:

• tenant_id is equality
• status is equality
• created_at is then useful for ordering or range filtering inside that narrowed set

What About Skip Scan?

This is one of the more interesting modern PostgreSQL details.

PostgreSQL can sometimes apply skip scan with B-tree multicolumn indexes. The docs explain that this can let PostgreSQL effectively use later column constraints even when an earlier column lacks a conventional equality condition, by performing repeated searches internally. But it is only attractive when the planner expects there are few enough distinct values in the earlier column that skipping most of the index is worthwhile. :contentReference[oaicite:4]{index=4}

Example from the idea:

• index on (x, y)
• query predicate on y = 7700
• PostgreSQL may decide repeated searches over possible x values are cheaper than scanning everything

Practical lesson

Skip scan is useful to know about, but it is not a license to ignore column order.

Design the index for the real common query shape first. Treat skip scan as a planner optimization bonus, not the foundation of the design.

Compound Index vs Two Single-Column Indexes

This is a very common design choice.

Separate indexes

(account_id)
(created_at)

Compound index

(account_id, created_at)

These solve different problems.

Separate indexes are better when:

• queries vary a lot
• sometimes you filter by one column only
• sometimes by the other column only
• there is no strong repeated combined access pattern

Compound indexes are better when:

• the same column combination appears repeatedly
• filter and order go together
• tenant-scoped recent-item queries are common
• top-N queries with LIMIT are common
• the same join/filter combination appears constantly

Practical example

If most real traffic uses:

where account_id = ?
order by created_at desc
limit 20

then the compound index is usually far more useful than two separate indexes.

Compound Indexes and ORDER BY

This is another major reason compound indexes help so much.

PostgreSQL’s docs explain that only B-tree indexes can produce sorted output for ORDER BY, and that this is especially helpful when ORDER BY is combined with LIMIT, because PostgreSQL can often retrieve the first rows directly instead of sorting the entire candidate set. :contentReference[oaicite:5]{index=5}

That is why an index like:

(account_id, created_at desc)

can be so strong for:

where account_id = ?
order by created_at desc
limit 20

Without the right compound index, PostgreSQL may need to:

• find many matching rows
• then sort them
• then discard almost all of them except the first 20

With the right compound B-tree index, it may be able to walk the index in the order the query already wants.

Custom Sort Direction in Compound Indexes

This is a more advanced but very useful feature.

PostgreSQL’s docs note that in multicolumn B-tree indexes, non-default sort directions can matter because scanning backward only flips all indexed sort directions together. If your application needs mixed ordering like ORDER BY x ASC, y DESC, you may need to define the index with that explicit ordering. :contentReference[oaicite:6]{index=6}

Example:

create index idx_feed_priority_created
on feed_items (priority asc, created_at desc);

This can be valuable if the application frequently needs:

• one column ascending
• the next descending

It is specialized, but sometimes extremely effective.

Compound Indexes for Filtering Plus Sorting

One of the best compound-index use cases is:

• filter by one or more columns
• sort by one more column
• return a small number of rows

Examples:

• latest orders per account
• newest messages in a conversation
• next unprocessed jobs in a queue
• newest events by tenant
• top items per category

These are exactly the kinds of patterns where a good compound index can create large performance wins.

Compound Indexes and Partial Indexes

Sometimes the best index is not only compound. It is also partial.

Example query:

select *
from jobs
where queue_name = $1
  and processed_at is null
order by created_at
limit 100;

A strong index might be:

create index idx_jobs_queue_created_unprocessed
on jobs (queue_name, created_at)
where processed_at is null;

Why this is powerful:

• it is compound
• it is smaller than a full-table index
• it matches the hot subset
• and it supports the real query shape

This is often better than a broad index over the whole table.

Compound Indexes and Covering Indexes

Sometimes you want the compound index not only to find rows well, but also to avoid extra heap lookups where possible.

PostgreSQL supports INCLUDE columns for covering indexes, and B-tree indexes can support index-only scans. The docs explain that INCLUDE columns are stored as payload rather than key columns, and that index-only scans are possible when the query only needs columns present in the index and visibility conditions cooperate. GIN indexes cannot do index-only scans. :contentReference[oaicite:7]{index=7}

Example:

create index idx_orders_account_created_include
on orders (account_id, created_at desc)
include (status, total_cents);

This can help queries like:

select created_at, status, total_cents
from orders
where account_id = $1
order by created_at desc
limit 20;

Important caution

Do not add payload columns casually. Every included column makes the index larger. Only do this for genuinely hot read paths.

Do Compound Indexes Help Every Index Type the Same Way?

No.

This is an important nuance.

B-tree

Column order matters a lot. Leftmost columns dominate how well PostgreSQL can narrow the scan. :contentReference[oaicite:8]{index=8}

GiST

Conditions on the first column matter most for how much of the index must be scanned. :contentReference[oaicite:9]{index=9}

GIN

Multicolumn GIN search effectiveness is the same regardless of which indexed column the conditions use. :contentReference[oaicite:10]{index=10}

BRIN

Like GIN, multicolumn BRIN search effectiveness is not dependent on leftmost-column order in the same way B-tree is. The main reason to split BRIN indexes instead of using one multicolumn BRIN index is different pages_per_range needs. :contentReference[oaicite:11]{index=11}

Practical takeaway

When developers talk about compound indexes, they are most often really talking about multicolumn B-tree behavior, where order matters most.

Common Compound Index Mistakes

1. Wrong column order

This is the biggest one.

2. Building a compound index from theory instead of real queries

Indexes should match repeated access paths.

3. Replacing every separate index with compound indexes

Sometimes separate indexes are the better tool.

4. Creating very wide indexes with too many columns

PostgreSQL’s docs explicitly caution against overusing multicolumn indexes, especially very wide ones. :contentReference[oaicite:12]{index=12}

5. Forgetting sort direction

If ORDER BY ... DESC is part of the hot path, index definition should reflect that when it matters.

6. Using a compound index when the workload is inconsistent

If the app’s filters vary too much, one compound index may help only a small subset of the real workload.

A Good Design Workflow

When deciding whether to create a compound index, ask:

1. What exact query is slow or repeated?
2. Which columns are equality filters?
3. Which column is used for range or sort?
4. Is there a LIMIT?
5. Does the same pattern happen often?
6. Would two single-column indexes solve this as well, or worse?
7. Would a partial or covering variant be even better?

That thinking usually leads to much better indexes than:

• “these three columns seem important”

Example Patterns That Often Deserve Compound Indexes

Tenant + time

(tenant_id, created_at desc)

Foreign key + status + recency

(account_id, status, created_at desc)

Queue + created time for unprocessed jobs

(queue_name, created_at)
where processed_at is null

User + event time

(user_id, occurred_at desc)

These are all classic real-world patterns where compound indexes shine.

FAQ

What is a compound index in PostgreSQL?

A compound index, also called a multicolumn index, is an index built on more than one column, such as (account_id, created_at). It is useful when queries repeatedly filter, join, or sort on those columns together.

Does column order matter in PostgreSQL compound indexes?

Yes, especially for B-tree indexes. PostgreSQL is most efficient when the leading leftmost columns are constrained in the query, so column order is one of the most important design decisions.

Is one compound index better than two single-column indexes?

Sometimes. A compound index is usually better when the workload repeatedly uses the same column combination together, especially with filtering plus ordering. Separate indexes can be better when access patterns vary a lot.

Should I put many columns into one PostgreSQL index?

Usually no. PostgreSQL’s docs note that multicolumn indexes should be used sparingly and that indexes with more than three columns are unlikely to help unless table usage is very stylized.

Conclusion

PostgreSQL compound indexes are powerful because they let you optimize a combined query path, not just one column in isolation.

That is why they work so well for patterns like:

• tenant-scoped recent rows
• filtered lists with ordering
• top-N queries
• and repeated multi-column filters

The most important lesson is simple:

For B-tree compound indexes, column order is not cosmetic. It is the design.

If you shape the index around the real query pattern, compound indexes can be one of the best PostgreSQL performance tools you have.

If you shape them around guesswork, they become expensive decoration.