Composite and Partial Indexes

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.

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.

Quick check: Why can a partial index be much smaller?

• It only includes rows matching its WHERE condition
• It compresses all data
• It stores only column names
• It skips the primary key