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

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

FAQs

Related

• 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+.