Which Postgres index should you add first? A composite index with the equality column first and the sort column last. On a 2-million-row orders table it took one customer's recent orders from 53 ms to 0.09 ms.
The trouble is that four or five reasonable indexes all make a slow query faster. One is thirty times faster than the rest, and one quietly slows every write a month later.
Here are the five index moves I make, in order, with real EXPLAIN ANALYZE output from a sample orders table of 2 million rows (174 MB) on Postgres 14 on a dev machine. Your timings will differ; the ratios hold.
create table orders (
id bigserial primary key,
customer_id int not null,
status text not null, -- 'paid', 'refunded', 'pending' (about 2%)
created_at timestamptz not null,
total_cents int not null,
deleted_at timestamptz -- soft delete, about 5% set
);1. Read the plan before you create an index
Before typing CREATE INDEX, ask Postgres what it does now. Take the classic slow screen: one customer's orders from the last 30 days, newest first, two hundred times a second.
EXPLAIN (ANALYZE, BUFFERS) runs the query and reports the plan, the real time per step and how many 8 KB pages it touched:
Sort (actual time=49.123..53.405 rows=6 loops=1)
Sort Key: created_at DESC
Buffers: shared hit=12444 read=4375
-> Gather (actual time=15.137..53.378 rows=6 loops=1)
-> Parallel Seq Scan on orders (actual time=21.892..46.565 rows=2 loops=3)
Filter: ((customer_id = 4242) AND (created_at > (now() - '30 days'::interval)))
Rows Removed by Filter: 666665
Execution Time: 53.440 msTwo million rows read to return six, almost 17,000 pages. 53 ms sounds fine until it runs 200 times a second.
The habit is the same as in reading a slow MySQL query plan: find the step that reads far more rows than it returns.
2. Get the composite index column order right
The obvious fix indexes both filtered columns, and there are two orders. Date first:
create index orders_created_customer on orders (created_at, customer_id);Index Scan Backward using orders_created_customer on orders (actual time=0.046..2.821 rows=6 loops=1)
Buffers: shared hit=11 read=316
Execution Time: 2.847 msFrom 53 ms to 2.8 ms. Most of us would go home here.
But 327 pages means Postgres walked every order from the last 30 days, for all customers, checking customer_id on each. Swap the order:
create index orders_customer_created on orders (customer_id, created_at);Index Scan Backward using orders_customer_created on orders (actual time=0.031..0.064 rows=6 loops=1)
Buffers: shared hit=7 read=3
Execution Time: 0.091 ms10 pages, 0.09 ms, thirty times faster from the same two columns.
The rule: columns compared with = go first, the range or sort column last. The B-tree keeps each customer's orders together and sorted by date, so Postgres jumps to customer 4242, reads six entries and stops.
Reading backward handles ORDER BY created_at DESC with no Sort node. The multicolumn index docs say the same thing more formally.
- No index (parallel seq scan)53.44 ms
- (created_at, customer_id)2.85 ms
- (customer_id, created_at)0.09 ms
3. Use a partial index for the rows you query
The second common query is a worker pulling its queue, here a rider-assignment job:
select id, customer_id, created_at from orders
where status = 'pending' and deleted_at is null
order by created_at limit 50;Without an index it scans the whole table (56.8 ms), though only about 2% of rows could ever match. A partial index stores only those rows:
create index orders_pending on orders (created_at)
where status = 'pending' and deleted_at is null;It runs in 0.48 ms. A full (status, created_at) index was just as fast, at 0.50 ms. The size is the difference:
| Index | Size |
|---|---|
orders_status_full (status, created_at) |
62 MB |
orders_pending (created_at) WHERE ... |
848 kB |
Seventy times smaller, so it stays in memory, and inserting a paid order never touches it. Soft-delete columns are the best use I know.
One catch: the query must contain the same predicate, so a parameterised status = $1 will not use an index on status = 'pending'.
To confirm the planner uses it, run EXPLAIN on the exact query your worker sends, with its literal values. If the plan shows a sequential scan, the predicate in the query and the one in the index do not match word for word.
4. Add INCLUDE columns only for hot queries
An Index Scan still visits the table for each row's other columns. If the index holds everything the query selects, it can skip the table:
create index orders_customer_created_incl on orders (customer_id, created_at)
include (total_cents);Index Only Scan Backward using orders_customer_created_incl on orders (actual time=0.036..0.037 rows=6 loops=1)
Heap Fetches: 0
Buffers: shared read=4
Execution Time: 0.055 msFour pages, which is as small as I have seen this query get. Heap Fetches: 0 depends on vacuum keeping the visibility map current, so on a heavily updated table it fades.
And 0.09 ms to 0.055 ms is small. I add INCLUDE for queries that run thousands of times a second or return many rows.
5. Drop an unused index, because every index taxes writes
Here's the catch: every INSERT writes to every index, and most UPDATEs do too unless they qualify as HOT, which needs no indexed column to change. A nightly import that slows down a month after an indexing spree is the usual symptom.
I inserted the same 500,000 rows into copies of the table:
- 1 index (primary key)0.83 s
- 3 indexes1.91 s
- 6 indexes6.13 s
The cost grows faster than the count, because once indexes stop fitting in memory each insert reads pages from disk.
So before adding an index, look for one to remove:
select s.indexrelname as index,
pg_size_pretty(pg_relation_size(s.indexrelid)) as size,
s.idx_scan as scans
from pg_stat_user_indexes s
join pg_index i on i.indexrelid = s.indexrelid
where s.idx_scan = 0 and not i.indisunique and not i.indisprimary
order by pg_relation_size(s.indexrelid) desc;idx_scan = 0 counts since the last stats reset (check pg_stat_database.stats_reset), and counters are per server, so check replicas too.
Look for duplicates as well: (customer_id) beside (customer_id, created_at) is almost always redundant. Multi-tenant tables collect these constantly, as in multi-tenant data isolation.
Conclusion
- Read the plan.
- Get the column order right before shipping.
- Go partial when the query wants a slice.
- Add
INCLUDEonly for hot queries. - Delete one unused index before going home.
The habit I would change is adding indexes one at a time and never removing any. Treat the index list like the columns: each maps to a query you can name.
Dropping one is cheap, since DROP INDEX CONCURRENTLY takes no blocking lock and CREATE INDEX CONCURRENTLY brings it back. If your plans look fine and the database is still struggling, read Postgres connection pooling with PgBouncer next.
How many indexes on your busiest table could you name a query for?