/blog

9 min read

Why your Postgres index isn't being used

The planner skipped an index on a 40-million-row table. Here is how to read EXPLAIN until it tells you why.

You added the index, ran the query again, and the plan still says Seq Scan. The planner isn't ignoring you. It made a cost estimate, and the estimate said a full scan was cheaper. The job is to find out which number it got wrong.

Start with the real plan

Plain EXPLAIN shows estimates. Add ANALYZE and BUFFERS so you see what actually happened, row counts included.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total
FROM orders
WHERE lower(email) = 'sam@example.com'
  AND created_at > now() - interval '30 days';

Compare rows= in the estimate with actual rows=. When they differ by 100x or more, the statistics are the problem, not the index.

The usual suspects

  • The expression doesn't match. An index on email can't serve lower(email). Index the expression itself.
  • The types don't match. Comparing a bigint column with a numeric parameter casts every row.
  • Stale statistics. A table that grew fast since the last ANALYZE looks smaller to the planner than it is.
  • Low selectivity. If the filter keeps 30% of the table, a sequential scan really is cheaper.

Fix the estimate, then the index

Run ANALYZE orders; first and look again. If the estimate is still off on correlated columns, extended statistics help:

CREATE STATISTICS orders_email_created (dependencies)
  ON email, created_at FROM orders;
ANALYZE orders;

Only then reach for a new index, and make it match the query exactly, expression and column order both.

$ subscribe

Want the next one?

Leave your email and new posts from Stack & Heap come to your inbox.

$ subscribe --email

Get new posts by email

Leave your address and new posts from Stack & Heap come to your inbox.

Rather write back? hello@stackandheap.example