Md Tarek — Portfolio© 2026

Full-stack Developer

System Design — built to hold up.

dhaka → remote, worldwide

Loading experience

Md TarekTarek
Tech

PostgreSQL Indexes & Query Planning: EXPLAIN ANALYZE Explained

By: Md Tarek · 17 Oct 2026

PostgreSQL query planning with B-tree indexes and EXPLAIN output
PostgreSQL query planning with B-tree indexes and EXPLAIN output

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 serves WHERE user_id = ? and WHERE user_id = ? ORDER BY created_at, but NOT WHERE 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 prefer REINDEX CONCURRENTLY over 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 CONCURRENTLY in production to avoid locking writes.
  • Keep an eye on pg_stat_user_indexes.idx_scan to 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.