Composite Index Column Order

Equality columns, then ranges

A composite index on (user_id, kind) is sorted by user_id, then by kind within each user. It serves queries filtering on user_id, or on user_id and kind, but not efficiently on kind alone (the leftmost prefix rule). Put columns used with equality first and range or sort columns last, and choose the order from your most important queries. Too many indexes slow down writes, so add them deliberately.

Which filters can use an index on (user_id, kind)?, run

I ran this with psql on PostgreSQL 16.2 against an events table of 200,000 generated rows with two indexes. Filtering on user_id, with or without kind, uses the index via a bitmap scan; filtering only on kind falls back to a parallel sequential scan.

-- Index on (user_id, kind): leading column used
EXPLAIN (COSTS OFF) SELECT count(*) FROM events WHERE user_id = 42 AND kind = 'buy';
EXPLAIN (COSTS OFF) SELECT count(*) FROM events WHERE user_id = 42;

-- Only the second column: the index is not a good fit
EXPLAIN (COSTS OFF) SELECT count(*) FROM events WHERE kind = 'buy';

Output:

                             QUERY PLAN                              
---------------------------------------------------------------------
 Aggregate
   ->  Bitmap Heap Scan on events
         Recheck Cond: ((user_id = 42) AND (kind = 'buy'::text))
         ->  Bitmap Index Scan on events_user_kind_idx
               Index Cond: ((user_id = 42) AND (kind = 'buy'::text))
(5 rows)

                      QUERY PLAN                       
-------------------------------------------------------
 Aggregate
   ->  Bitmap Heap Scan on events
         Recheck Cond: (user_id = 42)
         ->  Bitmap Index Scan on events_user_kind_idx
               Index Cond: (user_id = 42)
(5 rows)

                    QUERY PLAN                    
--------------------------------------------------
 Finalize Aggregate
   ->  Gather
         Workers Planned: 1
         ->  Partial Aggregate
               ->  Parallel Seq Scan on events
                     Filter: (kind = 'buy'::text)
(6 rows)

Design from the query backwards

List the WHERE and ORDER BY columns of the slow query, then build the index in that order.

त्वरित जाँच: An index on (user_id, kind) is least useful for which filter?

• WHERE kind = 'buy'
• WHERE user_id = 42
• WHERE user_id = 42 AND kind = 'buy'
• WHERE user_id = 42 ORDER BY kind