1---
2title: Create Composite Indexes for Multi-Column Queries
3impact: HIGH
4impactDescription: 5-10x faster multi-column queries
5tags: indexes, composite-index, multi-column, query-optimization
6---
7 
8## Create Composite Indexes for Multi-Column Queries
9 
10When queries filter on multiple columns, a composite index is more efficient than separate single-column indexes.
11 
12**Incorrect (separate indexes require bitmap scan):**
13 
14```sql
15-- Two separate indexes
16create index orders_status_idx on orders (status);
17create index orders_created_idx on orders (created_at);
18 
19-- Query must combine both indexes (slower)
20select * from orders where status = 'pending' and created_at > '2024-01-01';
21```
22 
23**Correct (composite index):**
24 
25```sql
26-- Single composite index (leftmost column first for equality checks)
27create index orders_status_created_idx on orders (status, created_at);
28 
29-- Query uses one efficient index scan
30select * from orders where status = 'pending' and created_at > '2024-01-01';
31```
32 
33**Column order matters** - place equality columns first, range columns last:
34 
35```sql
36-- Good: status (=) before created_at (>)
37create index idx on orders (status, created_at);
38 
39-- Works for: WHERE status = 'pending'
40-- Works for: WHERE status = 'pending' AND created_at > '2024-01-01'
41-- Does NOT work for: WHERE created_at > '2024-01-01' (leftmost prefix rule)
42```
43 
44Reference: [Multicolumn Indexes](https://www.postgresql.org/docs/current/indexes-multicolumn.html)
45