A composite index, also called a multicolumn index, covers several table columns. PostgreSQL sorts entries by the first column, then by each subsequent column within the preceding values, allowing one index to support related filtering and ordering queries. section 4.6 section 4.10
One index for a regional leaderboard
For player statistics, create an index ordered by region, score, and win count: CREATE INDEX idx_region_score_win_count ON game.player_stats (region, score DESC, win_count DESC); This ordering supports a query that finds players in one region by score and win count, and lets DISTINCT ON (region) return the highest-scoring player per region without an additional sort. EXPLAIN shows a Bitmap Index Scan for the filtered query and an Index Scan for the leaderboard query. section 4.6.2
Use composite indexes deliberately:
- Put the leading column first when queries constrain or order by it; an index on (region, score) is not suited to a query using only score. section 4.6
- Match the indexed order to the query order. Changing a leaderboard from region, score DESC, win_count DESC to region, win_count DESC, score DESC can force a sequential scan and sort. section 4.6.3
- Reuse one composite index across related queries instead of adding many single-column indexes, while remembering that every index requires maintenance. section 4.6
- Apply the same pattern to time-series data when queries commonly combine a timestamp with an identifier, such as (recorded_at, watch_id). section 9.7.1
Listing 4.8 Creating a composite index
CREATE INDEX idx_region_score_win_count ON game.player_stats (region, score DESC, win_count DESC);
Inspect the complete CREATE INDEX statement for the leaderboard example, including column order and descending sort directions. section 4.6.2
