Why Your Index Is Not Being Used: Reading EXPLAIN ANALYZE Line by Line

The short answer

Run EXPLAIN (ANALYZE, BUFFERS) and compare the estimated rows= against actual rows= on every node. Where those diverge by more than about ten times, you have found the cause. The planner is choosing a strategy that would be correct if its estimate were right.

The index is not broken. The planner decided against it based on bad arithmetic.

Five usual reasons an index gets skipped: stale statistics, a function or cast wrapping the indexed column, low selectivity, a type mismatch, and correlated columns the planner assumes are independent.

Tested on PostgreSQL 16.3.

Read the plan inside out

A query plan is a tree printed with children indented under parents. Execution runs bottom up and inside out: the most indented nodes run first and feed rows to their parents.

Nested Loop  (cost=0.43..8912.66 rows=1 width=48) (actual time=0.089..2841.552 rows=18422 loops=1)
  ->  Seq Scan on orders o  (cost=0.00..8901.00 rows=1 width=32) (actual time=0.021..142.331 rows=18422 loops=1)
        Filter: ((status)::text = 'pending'::text)
        Rows Removed by Filter: 381578
  ->  Index Scan using customers_pkey on customers c  (cost=0.43..8.45 rows=1 width=24) (actual time=0.011..0.012 rows=1 loops=18422)
        Index Cond: (id = o.customer_id)
Planning Time: 0.204 ms
Execution Time: 2853.119 ms

Every node carries two sets of numbers and the entire skill is comparing them.

Estimates, from EXPLAIN: cost=0.00..8901.00 rows=1 width=32. The first cost is startup cost, meaning work before the first row can be emitted. The second is total cost. Units are arbitrary, so only ratios matter. rows is the planner's guess.

Actuals, only from ANALYZE: actual time=0.021..142.331 rows=18422 loops=1. Times are startup and total in milliseconds. rows is what really came out. loops is how many times the node ran.

Critical detail: actual time and rows are per loop averages. In the plan above the index scan shows actual time=0.011..0.012 rows=1 loops=18422. That is 0.012ms each, run 18,422 times, so about 221ms total rather than 0.012ms.

Multiply by loops or you will misread nested loop plans consistently. This single fact is the most common reason people stare at a slow plan and see nothing wrong.

Find the divergence

In that plan, Seq Scan on orders estimated rows=1 and produced rows=18422. An 18,000 times error.

Everything downstream follows from it. The planner chose a Nested Loop because joining one row against customers through a primary key lookup is optimal. Joining 18,422 rows that way means 18,422 index lookups. Had it known the true count it would have chosen a Hash Join and finished in a fraction of the time.

The plan is not wrong. The input to the plan is wrong. Fixing this means fixing the estimate, not hinting the join.

The five reasons

1. Stale statistics

The planner relies on samples collected by ANALYZE and stored in pg_statistic. If the table has changed substantially since, estimates are fiction.

SELECT relname, n_live_tup, n_mod_since_analyze, last_analyze, last_autoanalyze
FROM pg_stat_user_tables WHERE relname = 'orders';

If n_mod_since_analyze is a large fraction of n_live_tup, or last_autoanalyze is old:

ANALYZE orders;

Then re run the plan. This fixes a genuinely large share of "the index is not being used" reports and it takes ten seconds to test, so do it before anything else.

Autovacuum triggers ANALYZE at roughly ten percent modified rows by default. On a 200 million row table that is 20 million rows of drift before it fires. For large, rapidly changing tables, lower it per table:

ALTER TABLE orders SET (autovacuum_analyze_scale_factor = 0.02);

Bulk loads are the classic trap. You insert five million rows and query immediately. Autovacuum has not run, so statistics describe an empty table. Always ANALYZE explicitly at the end of a bulk load.

2. A function or cast wraps the column

An index on created_at cannot serve this:

WHERE DATE(created_at) = '2026-06-08'

The index stores created_at values. The query asks about DATE(created_at), a different value the index knows nothing about, and Postgres will not invert the function.

Two fixes. Rewrite as a range, which is also faster because it avoids per row computation:

WHERE created_at >= '2026-06-08' AND created_at < '2026-06-09'

Or build an expression index:

CREATE INDEX ON orders (DATE(created_at));

Same trap with LOWER(email) = ..., email ILIKE ..., and CAST(id AS text) = .... Anything wrapping the column on the left hand side kills the index. Useful reflex: keep the indexed column bare on the left of the operator.

3. Low selectivity, where the index is genuinely worse

If a condition matches forty percent of the table, an index scan is slower than a sequential scan. Each index hit requires a random read into the heap, and at high match rates reading the table sequentially is cheaper. The planner is right and your index is not useful for this query.

The threshold depends on random_page_cost, which defaults to 4.0, a value calibrated for spinning disks. On SSDs this default is actively harmful and causes exactly this complaint.

SET random_page_cost = 1.1;

Test it in a session, then make it permanent. I have watched this one setting flip a dozen queries from sequential scan to index scan on a well provisioned server.

For a genuinely low selectivity column with a hot subset, a partial index works well:

CREATE INDEX ON orders (created_at) WHERE status = 'pending';

Small, cheap to maintain, and the planner will use it for the queries that matter.

4. Type mismatch

-- user_id is bigint, the parameter arrives as numeric
WHERE user_id = 12345.0

Postgres will not use a bigint index for a numeric comparison, because the implicit cast promotes the column rather than the literal. Common with ORMs, and with char(n) columns compared against text parameters.

EXPLAIN shows the cast:

Filter: ((user_id)::numeric = 12345.0)

Any :: around your column in a Filter line is a red flag.

5. Correlated columns

The planner assumes independence. Given:

WHERE city = 'Surat' AND state = 'Gujarat'

it multiplies the selectivities. If city matches 0.1% and state matches 5%, it estimates 0.005%. But every row with city Surat already has state Gujarat, so the true selectivity is 0.1%, twenty times the estimate.

Underestimates like this produce nested loops over far more rows than expected. Since Postgres 10 you can tell the planner about the correlation:

CREATE STATISTICS orders_city_state (dependencies, ndistinct)
  ON city, state FROM orders;
ANALYZE orders;

Underused, and often dramatic on schemas with natural hierarchies such as city and state, product and category, or tenant and region.

Read BUFFERS, not just time

EXPLAIN (ANALYZE, BUFFERS) adds I/O accounting:

Buffers: shared hit=4218 read=91043

hit means found in Postgres's buffer cache, which is fast. read means it had to go to the OS or disk, which is slow. dirtied and written mean this read caused writes.

Timing varies with cache state. Buffer counts are much more stable. If you are comparing two query formulations, compare buffers, because a query reading 90,000 buffers is doing more work than one reading 400 regardless of what the clock said on a warm cache.

Also watch for:

Sort Method: external merge  Disk: 48216kB

The sort spilled to disk because it exceeded work_mem. Raising it for that query often produces a step change:

SET LOCAL work_mem = '128MB';

Set it per transaction rather than globally, because it is allocated per sort node per connection. A global bump multiplied by 200 connections with three sorts each is how you run the server out of memory.

Node types worth recognising

Rows Removed by Filter is one of the most useful lines in any plan. In the opening example, 381,578 rows removed means the scan read 400,000 rows to return 18,000. That work is a missing or unusable index, stated plainly.

Heap Fetches on an Index Only Scan tells you the visibility map is stale, so the index had the data but Postgres still had to check the heap for row visibility. A VACUUM usually fixes it.

A worked fix

Back to the opening plan. Estimate said one row, reality was 18,422.

ANALYZE orders;

Re run:

Hash Join  (cost=1.18..9224.55 rows=18400 width=48) (actual time=0.412..168.203 rows=18422 loops=1)
  Hash Cond: (o.customer_id = c.id)
  ->  Seq Scan on orders o  (actual time=0.018..131.882 rows=18422 loops=1)
        Filter: ((status)::text = 'pending'::text)
        Rows Removed by Filter: 381578
  ->  Hash  (actual time=0.361..0.362 rows=842 loops=1)
        ->  Seq Scan on customers c  (actual time=0.009..0.201 rows=842 loops=1)
Execution Time: 174.881 ms

From 2853ms to 175ms purely from an accurate row estimate. The join strategy changed on its own.

The sequential scan on orders remains, still discarding 381,578 rows. Since status = 'pending' is a small hot subset, a partial index finishes the job:

CREATE INDEX CONCURRENTLY orders_pending_idx ON orders (customer_id)
  WHERE status = 'pending';

Use CONCURRENTLY on a live table. A plain CREATE INDEX takes a lock that blocks writes for the duration.

Habits

Always use ANALYZE and BUFFERS. Plain EXPLAIN shows only guesses, which is the thing you are trying to verify.

Compare estimated against actual first, before anything else. It points at the cause in seconds.

Multiply by loops, or nested loops look free.

Set random_page_cost to about 1.1 on SSD. The default is from a different era of hardware.

ANALYZE after bulk loads, always.

Paste plans into a visualiser. Tools like explain.dalibo.com highlight the largest divergence automatically, which is worth a lot on a 200 line plan.