When to use a composite index in PostgreSQL?

Databases & SQL
,
1

Composite index rules

Column order is CRITICAL

PostgreSQL can use a composite index (a, b, c) for:

  • :white_check_mark: Queries on a
  • :white_check_mark: Queries on a, b
  • :white_check_mark: Queries on a, b, c
  • :cross_mark: Queries on b alone (can’t skip leading column)
  • :cross_mark: Queries on b, c alone

For your queries:

-- Query 1: WHERE user_id = 123 AND status = 'pending'
CREATE INDEX idx_orders_user_status 
ON orders (user_id, status);
-- Put equality column first (user_id), then filter column

-- Query 2: WHERE user_id = 123 ORDER BY created_at DESC
CREATE INDEX idx_orders_user_created 
ON orders (user_id, created_at DESC);
-- Sort column goes last, matching ORDER BY direction

-- Covers both patterns:
CREATE INDEX idx_orders_user_status_created 
ON orders (user_id, status, created_at DESC);

Verify with EXPLAIN

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders 
WHERE user_id = 123 AND status = 'pending'
ORDER BY created_at DESC LIMIT 10;

-- Look for: Index Scan (good) vs Seq Scan (bad)
-- Check: Actual Rows vs Estimated Rows (big mismatch = stale stats)

When to VACUUM/ANALYZE

-- After bulk inserts/deletes
ANALYZE orders;
-- Check index usage
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE tablename = 'orders'
ORDER BY idx_scan DESC;