Multicolumn Index Order

B-tree composite indexes honor left-prefix rules: the planner can use the index only while predicates match leading columns in order on PostgreSQL 18.4.

Recipe

Quick-reference recipe card - copy-paste ready.

-- Good for: WHERE tenant_id = ? AND status = ? AND created_at > ?
CREATE INDEX events_tenant_status_created_idx
ON events (tenant_id, status, created_at);

When to reach for this: Queries filter on column B but index leads with column A and plans show seq scan despite an existing index.

Working Example

CREATE TABLE events (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id int NOT NULL,
  status text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
 
CREATE INDEX events_wrong_order_idx ON events (status, tenant_id, created_at);
CREATE INDEX events_right_order_idx ON events (tenant_id, status, created_at);
 
INSERT INTO events (tenant_id, status, created_at)
SELECT
  (i % 100) + 1,
  CASE WHEN i % 20 = 0 THEN 'failed' ELSE 'ok' END,
  now() - (random() * interval '30 days')
FROM generate_series(1, 400000) AS i;
 
ANALYZE events;
 
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM events
WHERE tenant_id = 7 AND status = 'failed' AND created_at >= now() - interval '1 day';

Drop wrong index in real deployments after verifying the right index is chosen.

What this demonstrates:

Equality columns before range columns
Tenant (selective in app context) often leads status
EXPLAIN index name confirms which composite is used

Deep Dive

How It Works

Rule: index usable for predicates on (col1), (col1, col2), (col1, col2, col3) left prefixes.
Skip leading column (WHERE status = ? only) cannot use (tenant_id, status) efficiently.
Mix equality and range: put equalities first, range last (tenant_id =, status =, created_at >).
Sort direction: align DESC with common ORDER BY to avoid sorts.

Ordering Heuristics

Priority	Place
Equality filters in every query	Leftmost
Higher selectivity equality	Further left among equals
Range / sort column	After equalities
Rarely filtered column	Do not include

SQL Notes

-- Two indexes when two query shapes dominate
CREATE INDEX events_tenant_created_idx ON events (tenant_id, created_at);
CREATE INDEX events_status_created_idx ON events (status, created_at)
WHERE status IN ('failed', 'retry');

Gotchas

One mega-index for all reports - Serves no query well. Fix: 2-3 proven composites plus partial indexes.
Low-cardinality leading column - status first when always combined with tenant_id. Fix: Put tenant_id left unless partial index fixes status slice.
Implicit type coercion - Predicate type mismatch skips index. Fix: Match column type in SQL and ORM.
OR across columns - Index order cannot save OR across non-prefix paths. Fix: UNION ALL rewrite or multiple partial indexes.
Duplicate indexes with permuted columns - Write amplification doubled. Fix: Redundant index audit.

Alternatives

Alternative	Use When	Don't Use When
Partial index per status	Few hot statuses	Hundreds of enum values
Partition by tenant	Huge multi-tenant facts	Small shared table
Expression index	Function in WHERE	Simple column available

FAQs

tenant_id vs created_at order?

tenant_id equality first when queries always scope tenant; created_at range follows.

Can planner skip middle column?

Generally no for B-tree; exceptions limited (e.g., IN lists on middle with leading equality).

INCLUDE columns order?

INCLUDE does not affect search key order; only key columns matter for prefix rules.

Multicolumn stats?

Extended ndistinct on (tenant_id, status) helps join cardinality estimates.

Index only scan?

Needs visibility map fresh and INCLUDE/select list covered.

Three-column limit?

No hard limit; practical indexes stay 2-4 key columns.

DESC mixed columns?

PostgreSQL supports mixed ASC/DESC multicolumn indexes since v8.3+.

Bitmap AND of indexes?

Multiple single-column indexes may bitmap; one good composite often beats two weak.

Migration order?

Create new concurrent index, verify plan, drop old.

Next?

Duplicate and redundant index audit queries.

Index Design Basics - Overall design
Duplicate & Redundant Indexes - Cleanup
Extended Statistics - Composite ndistinct
Partial & Covering Indexes - Variants

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

Recipe

Quick-reference recipe card - copy-paste ready.

-- Good for: WHERE tenant_id = ? AND status = ? AND created_at > ?
CREATE INDEX events_tenant_status_created_idx
ON events (tenant_id, status, created_at);

When to reach for this: Queries filter on column B but index leads with column A and plans show seq scan despite an existing index.

Working Example

CREATE TABLE events (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id int NOT NULL,
  status text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
 
CREATE INDEX events_wrong_order_idx ON events (status, tenant_id, created_at);
CREATE INDEX events_right_order_idx ON events (tenant_id, status, created_at);
 
INSERT INTO events (tenant_id, status, created_at)
SELECT
  (i % 100) + 1,
  CASE WHEN i % 20 = 0 THEN 'failed' ELSE 'ok' END,
  now() - (random() * interval '30 days')
FROM generate_series(1, 400000) AS i;
 
ANALYZE events;
 
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM events
WHERE tenant_id = 7 AND status = 'failed' AND created_at >= now() - interval '1 day';

Drop wrong index in real deployments after verifying the right index is chosen.

What this demonstrates:

Equality columns before range columns
Tenant (selective in app context) often leads status
EXPLAIN index name confirms which composite is used

Deep Dive

How It Works

Rule: index usable for predicates on (col1), (col1, col2), (col1, col2, col3) left prefixes.
Skip leading column (WHERE status = ? only) cannot use (tenant_id, status) efficiently.
Mix equality and range: put equalities first, range last (tenant_id =, status =, created_at >).
Sort direction: align DESC with common ORDER BY to avoid sorts.

Ordering Heuristics

Priority	Place
Equality filters in every query	Leftmost
Higher selectivity equality	Further left among equals
Range / sort column	After equalities
Rarely filtered column	Do not include

SQL Notes

-- Two indexes when two query shapes dominate
CREATE INDEX events_tenant_created_idx ON events (tenant_id, created_at);
CREATE INDEX events_status_created_idx ON events (status, created_at)
WHERE status IN ('failed', 'retry');

Gotchas

One mega-index for all reports - Serves no query well. Fix: 2-3 proven composites plus partial indexes.
Low-cardinality leading column - status first when always combined with tenant_id. Fix: Put tenant_id left unless partial index fixes status slice.
Implicit type coercion - Predicate type mismatch skips index. Fix: Match column type in SQL and ORM.
OR across columns - Index order cannot save OR across non-prefix paths. Fix: UNION ALL rewrite or multiple partial indexes.
Duplicate indexes with permuted columns - Write amplification doubled. Fix: Redundant index audit.

Alternatives

Alternative	Use When	Don't Use When
Partial index per status	Few hot statuses	Hundreds of enum values
Partition by tenant	Huge multi-tenant facts	Small shared table
Expression index	Function in WHERE	Simple column available

FAQs

tenant_id vs created_at order?

tenant_id equality first when queries always scope tenant; created_at range follows.

Can planner skip middle column?

Generally no for B-tree; exceptions limited (e.g., IN lists on middle with leading equality).

INCLUDE columns order?

INCLUDE does not affect search key order; only key columns matter for prefix rules.

Multicolumn stats?

Extended ndistinct on (tenant_id, status) helps join cardinality estimates.

Index only scan?

Needs visibility map fresh and INCLUDE/select list covered.

Three-column limit?

No hard limit; practical indexes stay 2-4 key columns.

DESC mixed columns?

PostgreSQL supports mixed ASC/DESC multicolumn indexes since v8.3+.

Bitmap AND of indexes?

Multiple single-column indexes may bitmap; one good composite often beats two weak.

Migration order?

Create new concurrent index, verify plan, drop old.

Next?

Duplicate and redundant index audit queries.

Index Design Basics - Overall design
Duplicate & Redundant Indexes - Cleanup
Extended Statistics - Composite ndistinct
Partial & Covering Indexes - Variants

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

Multicolumn Index Order

Search Documentation

Multicolumn Index Order