Why your query is slow: reading the plan

An index isn’t a speed button — the planner uses it only when it fetches few enough rows to be worth the trip.

The idea

When a query is slow, EXPLAIN shows you the planner’s plan — how it means to get the rows. The choice that matters most is Seq Scan (read the whole table once, in order) versus Index Scan (jump to matches through an index).

Which wins is decided by selectivity — the fraction of rows your filter keeps. Very selective (a handful of rows)? The index is a bargain. Not selective (a big slice of the table)? Each index match means a random jump into the heap, and doing that hundreds of thousands of times costs more than one clean sweep — so the planner ignores the index on purpose.

How it works

The planner estimates a cost for each candidate plan and picks the cheapest. In this simulator the model is deliberately transparent:

rows in table ............... 1,000,000
matched  = rows × selectivity

seq scan cost   = rows ............... = 1,000,000   (one ordered sweep)
index scan cost = matched × 12 ........            (each match: index hop + random heap fetch)

planner keeps the cheaper plan.  crossover:
  matched × 12 = 1,000,000  ->  matched ≈ 83,000  ->  ~8% of the table

  below ~8% selective  ->  index scan wins
  above ~8% selective  ->  seq scan wins — even when the index exists

Two more rules the tree obeys. A composite index (a, b) can drive a filter on a (the leading column) but not on b alone — so column order decides which query it helps. And an index the planner can use, it will still refuse when the sweep is cheaper. Real planners cost in disk pages and factor caching, correlation, and parallelism, but the shape is exactly this: index for the selective slice, sweep for the rest.

When to use it

The planner reaches for…	…when
Index Scan	The filter is selective (small % of rows) and an index covers the leading predicate column. Great for lookups by id, narrow date ranges.
Seq Scan	The filter keeps a large slice, or no usable index exists. One ordered pass beats hundreds of thousands of random jumps.
Bitmap Heap Scan (middle ground)	Selectivity sits in between: gather matching row locations from the index first, sort them, then read the heap in page order. Not shown here, but it’s what fills the gap.

Watch out for

• An indexed column that’s still slow. You built the index, but the filter isn’t selective (status = 'shipped' keeps 45%). The planner correctly ignores it — the index was never the fix.
• A wide date range that ignores the index. created_at ≥ last year matches most of the table, so the sweep wins. Narrow the range, or the index can’t help.
• Composite column order. An index on (customer_id, created_at) is useless for a query filtering on created_at alone — the leading column is unconstrained. Order the columns for your real predicates.
• Wrapping the column in a function. WHERE date(created_at) = … or lower(email) = … hides the raw column, so a plain index can’t match. Rewrite as a range, or add an expression index.
• A partition filter that never prunes. A table partitioned by created_at only skips partitions when you filter created_at directly. Filter on another column — or wrap the date in a function — and every partition gets scanned.

Worked example

An interviewer says: “This report query on a 50-million-row table takes 40 seconds. There’s an index on created_at. Why is it slow?” A strong answer runs EXPLAIN first, then reasons about selectivity. If the report pulls a whole quarter, that’s a large slice — the planner rightly sweeps, and the index isn’t the lever. You’d ask whether the query really needs the whole quarter, whether a narrower window or a covering index on the exact columns selected would let it stay in the index, and whether stats are fresh (a stale ANALYZE makes the planner mis-guess selectivity and choose wrong). And if the WHERE wrapped the date in to_char(created_at, …), you’d spot that the index can never match and the partitions never prune — the real bug.