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.

Before thisB-tree indexes
On this page
  1. Column order in a composite index
  2. Covering indexes and index-only scans
  3. Partial indexes
  4. Indexes on expressions
  5. Unique indexes
  6. When an index is not used, or hurts
  7. A checklist
  8. Try it yourself
  9. In MySQL
  10. Sources

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.

sql
create index users_country_status_idx on users (country, status);

A query that fixes the leading column reads one contiguous slice of the index:

sql
select id from users where country = 'DE' and status = 'banned';
Plan, executed real output from PostgreSQL 18.6 (PGlite 0.5.9). Timings depend on the machine; the shape and the row counts do not
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 ms

A 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:

sql
select id from users where status = 'banned';
Plan, executed real output from PostgreSQL 18.6 (PGlite 0.5.9). Timings depend on the machine; the shape and the row counts do not
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 ms

Skip 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:

  1. Put columns compared with = first, and a column compared with a range (<, >, BETWEEN) last. After a range, the following columns cannot narrow the slice.
  2. Put the column you filter on most often first, so that one index serves several queries.
  3. Match ORDER BY too. An index on (user_id, created_at) serves where user_id = ? order by created_at desc with 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.

sql
create index orders_user_created_idx on orders (user_id, created_at) include (total);
sql
select created_at, total from orders
where user_id = 42
order by created_at desc
limit 10;
Plan, executed real output from PostgreSQL 18.6 (PGlite 0.5.9). Timings depend on the machine; the shape and the row counts do not
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 ms

Heap 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.

sql
create index orders_pending_idx on orders (created_at) where status = 'pending';
sql
select id, created_at from orders
where status = 'pending'
order by created_at
limit 20;
Plan, executed real output from PostgreSQL 18.6 (PGlite 0.5.9). Timings depend on the machine; the shape and the row counts do not
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 ms
sql
create index orders_created_all_idx on orders (created_at);
sql
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;
Result 1 row; real output from PostgreSQL 18.6 (PGlite 0.5.9)
pending_onlyall_orders
104 kB2208 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:

sql
create index users_email_lower_idx on users (lower(email));
sql
select id, email from users where lower(email) = 'user77@example.com';
Plan, executed real output from PostgreSQL 18.6 (PGlite 0.5.9). Timings depend on the machine; the shape and the row counts do not
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 ms

Notice 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:

sql
select indexname, indexdef from pg_indexes where tablename = 'users' order by indexname;
Result 4 rows; real output from PostgreSQL 18.6 (PGlite 0.5.9)
indexnameindexdef
users_country_status_idxCREATE INDEX users_country_status_idx ON public.users USING btree (country, status)
users_email_keyCREATE UNIQUE INDEX users_email_key ON public.users USING btree (email)
users_email_lower_idxCREATE INDEX users_email_lower_idx ON public.users USING btree (lower(email))
users_pkeyCREATE 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:

sql
create index orders_status_idx on orders (status);
sql
select * from orders where status = 'refunded';
Plan real output from PostgreSQL 18.6 (PGlite 0.5.9)
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)
sql
select * from orders where status = 'delivered';
Plan real output from PostgreSQL 18.6 (PGlite 0.5.9)
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#

  1. Start from the slow query and its plan, not from the table.
  2. Index the columns in WHERE and JOIN … ON, equality columns first.
  3. Add the ORDER BY columns if that removes a sort.
  4. Consider INCLUDE for a hot query, a partial index for a hot subset, an expression index for a hot expression.
  5. 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.

Exercise

Log in with any capitalisation

Users type their email address however they like, so the login query compares lower-cased values. There is already a unique index on email, yet every login reads the whole users table.

Your task. Make the login lookup use an index, without changing the query.

select id from users where lower(email) = 'user123@example.com'

This exercise runs a real database in your browser and needs JavaScript.

Exercise

Sign-ups on one day

A report counts the users who signed up on a given day. Someone added an index on created_at, but the report is still slow: it reads the whole index and throws almost everything away.

Your task. Make the count read only the entries for that day.

select count(*) from users where created_at::date = '2023-06-15'

This exercise runs a real database in your browser and needs JavaScript.

Exercise

The oldest pending orders

The warehouse screen shows the 50 oldest orders that are still pending, so staff can deal with them first. Only about 4% of orders are pending, yet the screen reads the whole table and sorts what it finds.

Your task. Let the database find the oldest pending orders already in order, without sorting and without reading the whole table.

select id, user_id, created_at
from orders
where status = 'pending'
order by created_at
limit 50

This exercise runs a real database in your browser and needs JavaScript.

Exercise

A customer's latest orders, from the index alone

The account page shows a customer's 20 most recent orders: date and total only. It is called on every page view, so it should touch as little as possible.

Your task. Serve the query from the index alone, with no sort step.

select created_at, total
from orders
where user_id = 7
order by created_at desc
limit 20

This exercise runs a real database in your browser and needs JavaScript.

Exercise

The newest sign-ups in one country

The growth dashboard shows the ten newest users in a chosen country. Many users sign up on the same day, so ties are broken by id.

Your task. Find an index that returns exactly these ten rows without sorting and without reading rows it then discards.

select id, email, created_at
from users
where country = 'IN'
order by created_at desc, id desc
limit 10

This exercise runs a real database in your browser and needs JavaScript.

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#

Esc
↑ ↓ to move↵ to open