Postgres isn't using the index

You added an index, the query is still slow, and EXPLAIN says Seq Scan. There are only two possibilities, and the fix depends on which one you have:

1. The index can't answer the query as written. The WHERE clause doesn't match what the index stores — a function, a cast, a wildcard in front, the wrong column first.
2. The planner could use it, and decided not to. It estimated a sequential scan as cheaper — sometimes correctly, sometimes from bad statistics or bad cost settings.

Telling them apart takes one session-local switch:

BEGIN;
SET LOCAL enable_seqscan = off;
EXPLAIN (ANALYZE, BUFFERS) SELECT … ;   -- your query
ROLLBACK;

• The plan switches to an Index Scan → the index works; the planner priced it higher. Compare the actual times of both plans: if the index plan really is faster, it is a statistics or cost-setting problem (the bottom half of this page).
• It still says Seq Scan → the index can't serve this query (the top half).

enable_seqscan = off doesn't forbid seq scans, it makes them look very expensive, so you'll see them anyway when there is no alternative. It is a diagnostic switch — never put it in postgresql.conf.

Match the symptom

The index can't answer the query

A function or expression on the column

An index on email stores email values. WHERE lower(email) = $1 asks about a different value, so there is nothing to look up. Index the expression itself — it must match the query exactly:

CREATE INDEX CONCURRENTLY users_lower_email_idx ON users (lower(email));
ANALYZE users;   -- statistics for the new expression

The same goes for dates. WHERE date(created_at) = '2026-11-10' can't use an index on created_at; rewrite it as a range, which can:

WHERE created_at >= '2026-11-10' AND created_at < '2026-11-11'

And arithmetic: WHERE price * 1.2 > 100 becomes WHERE price > 100 / 1.2. Keep the indexed column alone on one side.

A type cast on the column

When a parameter's type doesn't match the column, PostgreSQL may cast the column, and an index on the column no longer applies. The plan gives it away: Filter: ((id)::numeric = '42'::numeric). Typical source is an ORM or driver sending a numeric (or a Java BigDecimal) for a bigint column. Fix the parameter type in the application (or cast the parameter, $1::bigint), not the column.

A leading wildcard, or LIKE under a non-C collation

A B-tree index is sorted, so it can find everything starting with abc — but not everything ending with it. Two separate problems:

• LIKE 'abc%' works only if the database collation is C, or the index uses the pattern operator class: CREATE INDEX ON products (sku text_pattern_ops);
• LIKE '%abc%' and ILIKE can't use a B-tree at all. Use trigram matching:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX CONCURRENTLY products_name_trgm ON products USING gin (name gin_trgm_ops);

Trigram indexes need at least three characters of pattern to be useful.

The column isn't first in a multi-column index

An index on (tenant_id, created_at) is sorted by tenant_id first. A query on created_at alone has to read the whole index — before PostgreSQL 18 the planner usually prefers the table. PostgreSQL 18 added B-tree skip scan, which helps when the leading column has few distinct values, but the dependable fix is still an index whose leading column is the one you filter by with equality. Order: equality columns first, then the range or sort column.

A partial index the query doesn't match

CREATE INDEX … WHERE deleted_at IS NULL is only used when the planner can prove the query's WHERE implies that predicate. Add the same condition to the query, literally. With a parameter (WHERE status = $1 against an index WHERE status = 'open') a generic plan can't prove it — see prepared statements below.

An INVALID index

CREATE INDEX CONCURRENTLY that fails (a deadlock, a duplicate under a unique index, a cancelled session) leaves an index behind that is maintained on writes but never used for reads. \d table prints INVALID next to it. Find them all:

SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;

Drop it and build it again, or REINDEX INDEX CONCURRENTLY it.

The planner chose not to use it

The seq scan really is cheaper

Fetching a row through an index costs a random read of the table page. When the query returns more than a few percent of the table, or the table is a few pages, reading it straight through wins — and the planner is right. A Seq Scan is only a problem when the plan shows a large Rows Removed by Filter next to a small row count: it read everything to keep a handful. If you need those rows faster, a covering index (INCLUDE (…)) that allows an Index Only Scan is the usual next step.

Stale statistics

The planner estimates row counts from pg_statistic, which autovacuum's ANALYZE refreshes. After a bulk load, a big delete or a new expression index, the numbers are wrong until it runs again. The plan shows it as rows= estimates far from the actual rows. Run it yourself:

ANALYZE orders;

For skewed columns, raise the sample — ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000; then ANALYZE — and for correlated columns (city and zip) add extended statistics with CREATE STATISTICS … (dependencies) ON city, zip FROM ….

Cost settings tuned for spinning disks

random_page_cost defaults to 4.0, four times the cost of a sequential page read — a 1990s disk ratio. On SSDs and most managed databases, 1.1 describes reality better and makes index scans look as cheap as they are. Also check effective_cache_size: set too low, the planner assumes index pages won't be cached. Try both per session before changing them globally.

Prepared statements and generic plans

Your query is fast in psql and slow from the application. Drivers prepare statements; after five executions PostgreSQL may switch to a generic plan that ignores the parameter values, and for skewed data (a status that is 0.1% 'open' and 99.9% 'closed') the generic plan can be a Seq Scan. Reproduce it with PREPARE + EXPLAIN EXECUTE six times, then pin custom plans for that role or session:

SET plan_cache_mode = force_custom_plan;

Heap Fetches on an Index Only Scan

An Index Only Scan still visits the table for every page not marked all-visible in the visibility map. A large Heap Fetches count means autovacuum hasn't caught up; VACUUM orders; restores the cheap path.

Finding which query to look at

If you don't yet know which query is slow, pg_stat_statements ranks every normalised query by total time — see pg_stat_statements for the setup and the query that lists the top offenders. To catch the plan of a slow query in production, the auto_explain module logs it whenever a query exceeds auto_explain.log_min_duration.

Read the plan before and after

A plan in a terminal is hard to scan, and once pasted into Slack it loses the indentation that holds it together. Paste it into Comma's free Postgres EXPLAIN visualizer: it builds the node tree with each node's self time, ranks where the time goes, and flags seq scans that discard most of what they read, 10× row misestimates, sorts that spilled to disk and heap fetches — all in your browser, nothing uploaded.

When the index needs someone else's approval — the table owner, the DBA, whoever reviews migrations — publish the plan as a link they can comment on, node by node. Publish it again after the fix and the report keeps both as revisions, so the before and after sit side by side.

Open the EXPLAIN visualizer →

Related

• What EXPLAIN ANALYZE shows — cost, rows, loops and self time
• pg_stat_statements — finding the queries worth explaining
• Postgres EXPLAIN visualizer — paste a plan, get the report
• Flame graph — the other way to see where time goes
• Troubleshooting — every fix page