Many queries filter on two columns. An index can hold both.
sql
CREATE INDEX members_country_joined_idx ON members (country, joined_on);Sorted by the first column, then the second
members_country_joined_idx
| country | joined_on | row |
|---|---|---|
| BR | 2020-01-02 | → 353 |
| BR | 2020-01-02 | → 2353 |
| … | … | … |
| JP | 2021-05-30 | → 1795 |
| JP | 2021-06-09 | → 1325 |
| … | … | … |
| US | 2020-01-08 | → 471 |
PostgreSQL can use this index for:
| Condition | Uses the index? |
|---|---|
country = 'JP' AND joined_on = '2021-06-09' | yes: go to JP, then to the date |
country = 'JP' | yes: the first column alone |
joined_on = '2021-06-01' | poorly: the dates sit in every country group |
This is the leftmost prefix rule: an index on (a, b, c) serves conditions on a, on a and b, and on a, b and c.
The first column matters
Without the first column, PostgreSQL must search each group of the first column. country has only 10 values, so PostgreSQL 18 can still do this in 10 small searches (a skip scan). The plan costs more than an index on joined_on alone:
text
Bitmap Index Scan on members_country_joined_idx (cost=0.00..43.35 rows=25 width=0)
Index Cond: (joined_on = '2021-06-01'::date)When the first column has thousands of values, the search costs too much and the plan goes back to a Seq Scan.
Choose the order: put first the column that your queries always filter on. If two queries need different first columns, create two indexes.