Why This Matters
When queries filter on multiple columns, a composite index is significantly more efficient than separate single-column indexes. With separate indexes, Postgres must perform a bitmap scan that combines results from multiple indexes - slower and more memory-intensive.
This becomes critical when you have queries like:
- Filtering by status AND date:
WHERE status = 'pending' AND created_at > '2024-01-01' - Multi-tenant queries:
WHERE tenant_id = 123 AND user_id = 456 - Search with sorting:
WHERE category = 'electronics' ORDER BY price
The Problem
Incorrect - Separate indexes require bitmap scan
-- Two separate indexes
CREATE INDEX orders_status_idx ON orders (status);
CREATE INDEX orders_created_idx ON orders (created_at);
-- Query must combine both indexes (slower)
SELECT * FROM orders
WHERE status = 'pending' AND created_at > '2024-01-01';The Solution
Correct - Single composite index
-- Single composite index (leftmost column first for equality checks)
CREATE INDEX orders_status_created_idx ON orders (status, created_at);
-- Query uses one efficient index scan
SELECT * FROM orders
WHERE status = 'pending' AND created_at > '2024-01-01';Column order matters! Place equality columns (=) first, range columns (>, <, BETWEEN) last. The index (status, created_at) works for WHERE status = 'pending' but NOT for WHERE created_at > '2024-01-01' alone.
The Leftmost Prefix Rule
A composite index on (a, b, c) can satisfy queries on:
WHERE a = ?- uses indexWHERE a = ? AND b = ?- uses indexWHERE a = ? AND b = ? AND c = ?- uses indexWHERE b = ?- does NOT use index (missing leftmost column)WHERE a = ? AND c = ?- partially uses index (only a column)
Check Your Project Automatically
Connect your Supabase project to analyze your indexes and query patterns.
Start Supabase AuditCommon Mistakes
- Wrong column order: Putting range columns before equality columns defeats the purpose. Always: equality first, then ranges, then ORDER BY columns.
- Too many columns: Indexes with 4+ columns are rarely fully utilized. Focus on your most common query patterns.
- Duplicate indexes: If you have
(a, b), you don't also need(a)- the composite index covers single-column queries ona.