# Composite and Partial Indexes — PostgreSQL

Source: https://www.skillbyai.com/en/postgresql/x-composite

> Match the index to the query.

## Column order and WHERE clauses

A **composite** index on `(user_id, created_at DESC)` serves queries that filter on user_id and sort by created_at, letting PostgreSQL read just the first N rows in order. Column order matters: put equality-filtered columns first. A **partial** index includes only rows matching a condition (`WHERE kind = 'purchase'`), making it much smaller and faster to maintain when queries always use that condition.

## A composite index for "latest 5 events" and a partial index, run

I ran this with psql against PostgreSQL 16.2 on a fresh database with a 200,000-row events table (created with generate_series and analysed). The query uses an index scan on the composite index and stops after 5 rows (Limit), with no separate sort. The partial index covering only purchases is 64 kB, versus 6,184 kB for the full composite index.

```sql
CREATE INDEX events_user_time_idx ON events (user_id, created_at DESC);
EXPLAIN (COSTS OFF)
SELECT id, kind, created_at FROM events
WHERE user_id = 42 ORDER BY created_at DESC LIMIT 5;

CREATE INDEX events_purchases_idx ON events (created_at) WHERE kind = 'purchase';
SELECT pg_size_pretty(pg_relation_size('events_user_time_idx')) AS full_index,
       pg_size_pretty(pg_relation_size('events_purchases_idx')) AS partial_index;
```

Output:

```
                      QUERY PLAN                       
-------------------------------------------------------
 Limit
   ->  Index Scan using events_user_time_idx on events
         Index Cond: (user_id = 42)
(3 rows)

 full_index | partial_index 
------------+---------------
 6184 kB    | 64 kB
(1 row)
```

## Avoid redundant indexes

An index on (a, b) also serves queries on a alone; a separate index on a is usually unnecessary.

**Quiz:** Why can a partial index be much smaller?

- [x] It only includes rows matching its WHERE condition
- [ ] It compresses all data
- [ ] It stores only column names
- [ ] It skips the primary key

*Answer:* It only includes rows matching its WHERE condition. Index only what queries need.