PostgreSQL Not Using an Index? Diagnose It with EXPLAIN

Start with the rare value. The index can directly locate entries whose state equals 'open'. On this prepared dataset you should see events_state_idx, usually through an index scan or a bitmap scan. Both are legitimate uses of the index; do not search only for the exact phrase Index Scan.

Read the plan from the node that accesses events. Compare estimated rows with actual rows. The result should contain 200 events, so an estimate in that neighborhood is plausible. A large mismatch would point toward a statistics or data-distribution question before a planner-configuration question.

The separate count query confirms the dataset. EXPLAIN's cost figures are planner units, not milliseconds. The actual timing fields are measurements of this execution, with instrumentation overhead and this VM's cache state. BUFFERS counts accesses to shared buffers; a buffer hit means the page was already in PostgreSQL's shared cache, not that the whole query was free.

psql

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM events WHERE state = 'open';

SELECT count(*) AS open_events FROM events WHERE state = 'open';

Change only the requested value to 'closed'. This time almost every row qualifies. An index scan would still have to reach nearly the entire table. On this dataset the planner should prefer a sequential scan.

That is not evidence that the index is broken. It is evidence that the query asks for a different fraction of the data. Selecting only a few columns does not automatically make an index-only scan available either: this index contains state and tuple references, but not the selected ID value.

If your real query has a bad estimate, run ANALYZE after a substantial load or change and compare the plan again. Statistics help the planner estimate the result; they do not change SQL semantics. Autovacuum normally performs analyzing automatically, but a bulk import or a skewed distribution can justify closer inspection. For correlated predicates, consider whether ordinary per-column statistics describe the combination accurately.

Do not make enable_seqscan = off your application's permanent fix. It is a diagnostic way to compare alternatives, and can encourage a worse plan. First establish which plan is actually better for representative data and parameters.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM events WHERE state = 'closed';

SELECT count(*) AS closed_events FROM events WHERE state = 'closed';

Now ask for lower(state) = 'open'. The plain index stores state values, not the result of lower(state). A condition on a transformed value generally needs a suitable expression index, or a query rewrite that preserves the intended meaning.

The first plan shows the query before that index exists. Then create an index on lower(state), refresh statistics, and inspect the same query. The final plan should name events_lower_state_idx. The table values and matching row count did not change; the new access path matches the expression used by the predicate.

Real cases also involve casts, collations, partial-index predicates and operator classes. Compare the actual predicate and index definition instead of assuming that every appearance of a column can use every index on that column. Prepared statements add another wrinkle: a plan chosen without a specific parameter value may make a different tradeoff from your literal test query.

Reset the VM before repeating this before/after demonstration: after the first run, the expression index already exists. IF NOT EXISTS makes rerunning the creation harmless, but it cannot recreate the original missing-index situation.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM events WHERE lower(state) = 'open';

CREATE INDEX IF NOT EXISTS events_lower_state_idx ON events (lower(state));

ANALYZE events;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM events WHERE lower(state) = 'open';