- Which index does my slow Postgres query need?
- Run
EXPLAIN (ANALYZE, BUFFERS). A Seq Scan whose Rows Removed by Filter is most of the table wants a btree on the filter's columns. Put the columns compared with = first and at most one range column (<, >, BETWEEN) last: equality on the leading keys plus a range on the next one is a single contiguous slice of the index.
- Does column order matter in a Postgres composite index?
- Yes. A btree on
(account_id, created_at) serves WHERE account_id = $1 AND created_at > $2 as one index range, but a query that filters only created_at can't use its leading key and reads far more of it. Lead with the columns your queries filter by equality; end with the one they filter by range.
- Why is Postgres not using my index?
- Most often the filter isn't selective: when a query returns a large share of the table, a sequential scan is cheaper and the planner is right to choose it. Otherwise look for a function or cast on the column (
lower(email) needs an expression index), a leading index column the query never filters, or stale statistics — run ANALYZE.
- What does an applied index look like on a real database?
- On the first real app Postgres nlqdb attached, a range lookup scanned a ~10,200-row table to return 0.01 % of it per call. nlqdb proposed an index on the range column; after 72 hours the query's mean fell from 7.7 ms to 0.3 ms and its p95 estimate from 65.2 ms to 1.7 ms, so verify kept it.