Designing indexes
Column order, covering, partial and expression indexes, and when an index is ignored or hurts.
Draft. This page is new and has not yet been reviewed by a second person. Every query output on it was produced by a real run on PostgreSQL 18.6 (PGlite 0.5.9). If something looks wrong, report it.
On this page
Knowing how a B-tree works is half the job. The other half is choosing which indexes to build: which columns, in which order, covering which rows. A good index matches the queries you actually run; a bad one costs space and write time and is never used.
Column order in a composite index#
An index on several columns sorts by the first column, then by the second within each value of the first, like a phone book sorted by surname and then first name. That order decides which queries can use it.
create index users_country_status_idx on users (country, status);A query that fixes the leading column reads one contiguous slice of the index:
select id from users where country = 'DE' and status = 'banned';Bitmap Heap Scan on users (cost=4.88..134.17 rows=58 width=4) (actual time=0.042..0.130 rows=52.00 loops=1)
Recheck Cond: ((country = 'DE'::text) AND (status = 'banned'::text))
Heap Blocks: exact=50
Buffers: shared hit=50 read=2
-> Bitmap Index Scan on users_country_status_idx (cost=0.00..4.87 rows=58 width=0) (actual time=0.025..0.026 rows=52.00 loops=1)
Index Cond: ((country = 'DE'::text) AND (status = 'banned'::text))
Index Searches: 1
Buffers: shared read=2
Planning:
Buffers: shared hit=17 read=1
Planning Time: 0.241 ms
Execution Time: 0.187 msA query on the second column alone cannot read one slice, because the matching entries are spread through every country. The long-standing rule was that such a query cannot use the index. PostgreSQL 18 relaxed it with skip scan: when the leading column has few distinct values, the planner can jump to each value in turn and search inside it. The Index Searches line shows how many descents that took:
select id from users where status = 'banned';Bitmap Heap Scan on users (cost=30.35..246.34 rows=559 width=4) (actual time=0.246..0.849 rows=559.00 loops=1)
Recheck Cond: (status = 'banned'::text)
Heap Blocks: exact=197
Buffers: shared hit=217 read=10
-> Bitmap Index Scan on users_country_status_idx (cost=0.00..30.21 rows=559 width=0) (actual time=0.213..0.214 rows=559.00 loops=1)
Index Cond: (status = 'banned'::text)
Index Searches: 11
Buffers: shared hit=20 read=10
Planning Time: 0.078 ms
Execution Time: 1.532 msSkip scan is a useful safety net, not a design goal. It costs one descent per distinct leading value, so it is only worth it when there are few of them. The practical rules still hold:
- Put columns compared with
=first, and a column compared with a range (<,>,BETWEEN) last. After a range, the following columns cannot narrow the slice. - Put the column you filter on most often first, so that one index serves several queries.
- Match
ORDER BYtoo. An index on(user_id, created_at)serveswhere user_id = ? order by created_at descwith no sort at all.
Covering indexes and index-only scans#
If every column a query needs is in the index, the database can answer from the index alone, an index-only scan. INCLUDE adds columns to the leaf entries without making them part of the sort key, so they can be returned but not searched.
create index orders_user_created_idx on orders (user_id, created_at) include (total);select created_at, total from orders
where user_id = 42
order by created_at desc
limit 10;Limit (cost=0.42..2.41 rows=10 width=14) (actual time=0.210..0.224 rows=10.00 loops=1)
Buffers: shared hit=1 read=3
-> Index Only Scan Backward using orders_user_created_idx on orders (cost=0.42..9.19 rows=44 width=14) (actual time=0.208..0.214 rows=10.00 loops=1)
Index Cond: (user_id = 42)
Heap Fetches: 0
Index Searches: 1
Buffers: shared hit=1 read=3
Planning:
Buffers: shared hit=10 read=1
Planning Time: 0.269 ms
Execution Time: 0.296 msHeap Fetches: 0 means the table was not visited at all. That only works for pages that VACUUM has marked all-visible; on a table with many recent changes, an index-only scan still has to check the table for those pages, and Heap Fetches goes up.
Partial indexes#
A partial index only contains the rows that match a condition. When queries always ask for a small subset, such as the 4% of orders that are still pending, the index can be a fraction of the size of a full one.
create index orders_pending_idx on orders (created_at) where status = 'pending';select id, created_at from orders
where status = 'pending'
order by created_at
limit 20;Limit (cost=0.28..1.02 rows=20 width=12) (actual time=0.045..0.079 rows=20.00 loops=1)
Buffers: shared hit=6 read=2
-> Index Scan using orders_pending_idx on orders (cost=0.28..148.36 rows=4005 width=12) (actual time=0.042..0.061 rows=20.00 loops=1)
Index Searches: 1
Buffers: shared hit=6 read=2
Planning:
Buffers: shared hit=5 read=1
Planning Time: 0.510 ms
Execution Time: 0.109 mscreate index orders_created_all_idx on orders (created_at);select pg_size_pretty(pg_relation_size('orders_pending_idx')) as pending_only,
pg_size_pretty(pg_relation_size('orders_created_all_idx')) as all_orders;| pending_only | all_orders |
|---|---|
| 104 kB | 2208 kB |
The same column, indexed for every order and for pending orders only.
The planner only uses a partial index when it can prove the query's condition implies the index's condition, so the where status = 'pending' has to be in the query.
Indexes on expressions#
An index on a column does not help a query that applies a function to the column. Index the expression instead:
create index users_email_lower_idx on users (lower(email));select id, email from users where lower(email) = 'user77@example.com';Bitmap Heap Scan on users (cost=5.06..179.28 rows=100 width=25) (actual time=0.028..0.030 rows=1.00 loops=1)
Recheck Cond: (lower(email) = 'user77@example.com'::text)
Heap Blocks: exact=1
Buffers: shared hit=1 read=2
-> Bitmap Index Scan on users_email_lower_idx (cost=0.00..5.04 rows=100 width=0) (actual time=0.016..0.016 rows=1.00 loops=1)
Index Cond: (lower(email) = 'user77@example.com'::text)
Index Searches: 1
Buffers: shared read=2
Planning:
Buffers: shared hit=7 read=1
Planning Time: 0.211 ms
Execution Time: 0.053 msNotice the estimate in that plan. The planner only learns how values of a new expression are distributed when the table is next analysed, so until then it guesses.
Unique indexes#
A unique index refuses a second row with the same key, which is how PRIMARY KEY and UNIQUE constraints are enforced. The sample users.email column has one:
select indexname, indexdef from pg_indexes where tablename = 'users' order by indexname;| indexname | indexdef |
|---|---|
| users_country_status_idx | CREATE INDEX users_country_status_idx ON public.users USING btree (country, status) |
| users_email_key | CREATE UNIQUE INDEX users_email_key ON public.users USING btree (email) |
| users_email_lower_idx | CREATE INDEX users_email_lower_idx ON public.users USING btree (lower(email)) |
| users_pkey | CREATE UNIQUE INDEX users_pkey ON public.users USING btree (id) |
When an index is not used, or hurts#
When most rows match. Following an index means jumping between pages; reading the whole table in order is cheaper once a large share of rows qualify. With an index on status, the rare value uses it and the common one does not:
create index orders_status_idx on orders (status);select * from orders where status = 'refunded';Bitmap Heap Scan on orders (cost=12.19..877.95 rows=1019 width=31)
Recheck Cond: (status = 'refunded'::text)
-> Bitmap Index Scan on orders_status_idx (cost=0.00..11.94 rows=1019 width=0)
Index Cond: (status = 'refunded'::text)select * from orders where status = 'delivered';Seq Scan on orders (cost=0.00..2060.00 rows=70011 width=31) Filter: (status = 'delivered'::text)
When the query hides the column. where created_at::date = '2024-06-01' or where total * 1.2 > 100 cannot use a plain index on created_at or total. Rewrite the condition so the bare column is compared with a value (where created_at >= '2024-06-01' and created_at < '2024-06-02'), or index the expression.
When it is never read. Every index slows down every insert and many updates. pg_stat_user_indexes.idx_scan counts how often each index has been used since statistics were reset; one that stays at zero on a busy production system is a candidate to drop.
Hash indexes support equality only. They are crash-safe since PostgreSQL 10, but a B-tree is almost always just as good and also supports ranges and sorting, so reach for a hash index only after measuring.
A checklist#
- Start from the slow query and its plan, not from the table.
- Index the columns in
WHEREandJOIN … ON, equality columns first. - Add the
ORDER BYcolumns if that removes a sort. - Consider
INCLUDEfor a hot query, a partial index for a hot subset, an expression index for a hot expression. - Check the plan again. Then check that writes are still fast enough.
Try it yourself#
Each exercise runs on the same sample database. Your changes are made inside a transaction and undone after every check, so you cannot break anything.
In MySQL#
Not run here; from the MySQL reference manual. MySQL has composite, covering and unique indexes, functional (expression) indexes since 8.0.13, and a skip scan access method since 8.0.13. It has no partial indexes. Every InnoDB secondary index implicitly contains the primary key columns, which is why queries that only need the primary key are often covered for free.
Sources#
- PostgreSQL documentation: Multicolumn Indexes, Index-Only Scans and Covering Indexes, Partial Indexes, Indexes on Expressions, Unique Indexes, Examining Index Usage, CREATE INDEX; PostgreSQL 18 release notes (B-tree skip scan).
- Use The Index, Luke: "Concatenated Indexes", "Partial Indexes", "Functions".
- MySQL reference manual: Multiple-Column Indexes, Functional Key Parts, Skip Scan Range Access Method.