Skip to content
Sandeep Kumar
5 min read

The Postgres indexes I'd add first, and the one that slowed writes

Composite, partial and covering indexes in Postgres, in the order I reach for them, with EXPLAIN ANALYZE before and after, and what each costs on writes.

Sandeep Kumar, Full-stack EngineerSandeep KumarFull-stack Engineer
The Postgres indexes I'd add first, and the one that slowed writes: Same two columns, other order: thirty times faster
On this page
  1. 1. Read the plan before you create an index
  2. 2. Get the composite index column order right
  3. 3. Use a partial index for the rows you query
  4. 4. Add INCLUDE columns only for hot queries
  5. 5. Drop an unused index, because every index taxes writes
  6. Conclusion

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.

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

code
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 ms

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

sql
create index orders_created_customer on orders (created_at, customer_id);
code
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 ms

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

sql
create index orders_customer_created on orders (customer_id, created_at);
code
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 ms

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

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:

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

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

sql
create index orders_customer_created_incl on orders (customer_id, created_at)
  include (total_cents);
code
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 ms

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

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:

sql
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 INCLUDE only 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?

FAQs

Which columns should I index first in Postgres?

Start from the slow queries, not the schema. Take the queries that run most often or take longest (pg_stat_statements lists both), read their plans, and index the columns in their WHERE clause and ORDER BY. Foreign keys used in joins are the next candidates, because Postgres does not index them automatically.

Does column order matter in a Postgres composite index?

Yes. A B-tree on (a, b) is sorted by a, then by b inside each value of a. Put columns compared with equality first and the column used for a range or ORDER BY last, so the matching rows sit next to each other in the index. The reverse order makes Postgres walk a much larger part of the index.

What is a partial index in Postgres?

An index with a WHERE clause, so it only contains rows that match it, for example CREATE INDEX ON orders (created_at) WHERE status = 'pending'. It is smaller, cheaper to maintain, and used whenever the query's WHERE clause implies the index's condition.

How do I find unused indexes in Postgres?

Query pg_stat_user_indexes for indexes with idx_scan = 0, check how long the statistics have been collecting, and exclude indexes that back a primary key or unique constraint. Check replicas too, because each server keeps its own counters and a read replica may be the only place an index is used.

Found this useful?Share on XShare on LinkedIn

Related reading