PostgreSQL Indexes & Query Planning: EXPLAIN ANALYZE Explained
By: Md Tarek · 17 Oct 2026
Executive Summary & Key Takeaways
- An index is a sorted copy of a column, not a magic wand. A B-tree index lets Postgres find rows in O(log n) instead of a full table scan, but it only helps when the planner can actually use it against your WHERE / JOIN / ORDER BY clause.
- Read the query plan before adding an index.
EXPLAIN (ANALYZE, BUFFERS)shows you the real cost:Seq Scan,Index Scan,Bitmap Heap Scan, and the estimated vs actual row counts that reveal stale statistics. - Composite (multi-column) indexes are ordered left-to-right. A
(user_id, created_at)index servesWHERE user_id = ?andWHERE user_id = ? ORDER BY created_at, but NOTWHERE created_at = ?alone. - Covering indexes kill round-trips. Put the SELECT-ed columns into the index (or use
INCLUDE) so the query is answered entirely from the index without touching the heap. - Bloat and stale stats are the silent killers. Run
ANALYZE, watch for table bloat, and preferREINDEX CONCURRENTLYover a blocking rebuild.
1. How the Planner Chooses a Scan
The planner compares the estimated cost of a sequential scan against the alternatives. Its input is the statistics stored in pg_stats — approximate row counts, n_distinct, and most-common-values. When those stats are stale (after a big import or delete), the planner makes bad choices and a perfectly good index goes unused.
EXPLAIN (ANALYZE, BUFFERS) SELECT id, title FROM notes WHERE author = 'Md Tarek' AND created_at > now() - interval '30 days' ORDER BY created_at DESC LIMIT 20;
Read it bottom-up: the innermost node executes first. Look for a Seq Scan with rows far larger than the actual rows, or a Filter that discards most rows after fetching them — both are index opportunities.
2. Choosing the Right Index Type
| Index | Best for | Notes | |---|---|---| | B-tree | equality, ranges, ORDER BY | the default; the only one you usually need | | Hash | equality only | faster than B-tree in some versions, not WAL-logged pre-PG10 | | GIN | arrays, JSONB, full-text | supports @>, ?, @@ | | GiST | geometry, ranges | PostGIS and range types | | BRIN | huge, naturally-ordered tables | tiny index over block ranges |
For a JSONB WHERE data @> '{"status":"draft"}', a GIN index is the difference between milliseconds and a full scan.
3. Composite Indexes and Column Order
The order of columns in a composite index is everything. An index on (a, b, c) supports filtering on a, a + b, and a + b + c, in that order. It does not help a query that filters on b alone. Design indexes around your most selective leading column.
Pro-Tips & Common Pitfalls
- Don't index low-selectivity columns (booleans, status enums) in isolation.
- Avoid redundant indexes; each one slows writes.
- Prefer
CREATE INDEX CONCURRENTLYin production to avoid locking writes. - Keep an eye on
pg_stat_user_indexes.idx_scanto find indexes nobody uses.
FAQ
Why is Postgres ignoring my index? Usually stale statistics, a low-selectivity predicate, a function wrapping the column (lower(email) = ? needs an expression index), or the planner correctly estimating that a scan is cheaper for small tables.
Should I add an index to every foreign key? Almost always yes — deletes and joins on the parent table need an index on the referencing column.
