Composite and Partial Indexes

Single-column indexes get you far. Real queries usually filter on more than one thing.

Composite indexes

create index orders_customer_created_idx
  on orders (customer_id, created_at);

A composite B-tree is sorted by the first column, then by the second column within each value of the first, like a phone book sorted by last name, then first name.

That ordering decides which queries it serves well:

-- Great: uses the leading column
where customer_id = 42
 
-- Great: leading column, then a range on the second
where customer_id = 42 and created_at > now() - interval '30 days'
 
-- Also great: matches the sort order, no separate sort step
where customer_id = 42 order by created_at desc

A query that filters only on created_at cannot use the tree's ordering the same way, because matching rows are scattered across every customer_id. Newer Postgres versions can sometimes skip through the leading column, but it is far less efficient than a properly ordered index.

Rule of thumb: put columns compared with = first, and the column you range over or sort by last.

Partial indexes

If your queries only ever care about a small slice of a table, index just that slice:

create index jobs_pending_idx
  on jobs (run_at)
  where status = 'pending';

Most jobs are done, so this index is tiny compared to indexing every row. It stays fast to scan and cheap to maintain. The catch: the planner only uses it when the query's where clause clearly implies the index condition, for example where status = 'pending' and run_at < now().

Partial indexes also enforce rules. A unique partial index like

create unique index one_active_subscription
  on subscriptions (user_id)
  where active;

allows many inactive subscriptions per user but at most one active one.

Knowing when to stop

Before adding another index, check:

• Is there an existing composite index whose leading columns already cover this query?
• How write-heavy is the table? Every index slows every insert.
• Does pg_stat_user_indexes show indexes with idx_scan = 0? Those cost you writes and never help reads. Consider dropping them.

The best index set is the smallest one that makes your important queries fast.