ThisCalculator

Everyday

Why Your PostgreSQL Query Isn’t Using an Index

Updated · Standard formula, shown below

Why Your PostgreSQL Query Isn't Using an Index

Your PostgreSQL query isn’t using an index because the planner estimates that a sequential scan will be faster than the index scan, based on the table statistics, the query’s WHERE clause, and the cost parameters. The planner isn’t being stubborn—it’s doing the math and deciding that reading the whole table is cheaper than jumping through the index. Below, we break down the real reasons this happens and how to diagnose and fix each one.

How PostgreSQL Chooses a Scan Type

PostgreSQL uses a cost-based optimizer. For each possible plan, it assigns a cost based on disk I/O, CPU, and memory. The planner doesn’t care about “using an index” as a goal—it only cares about the cheapest way to return the correct rows. The cost model is roughly:

  • Sequential scan cost: proportional to the number of pages in the table.
  • Index scan cost: index pages read + table pages fetched (for each matching row) + CPU cost per row.

If the index would require fetching a large fraction of the table’s rows, the planner will often prefer a sequential scan because reading every page once is cheaper than reading many pages repeatedly via the index. This is the classic “index not used” situation.

Reason 1: The Query Returns Too Many Rows

If your WHERE clause matches more than roughly 5–10% of the table, an index scan usually loses to a sequential scan. The planner estimates this from the table’s statistics (collected by ANALYZE). For example, a query like WHERE status = 'active' where 80% of rows are active will almost always use a sequential scan, even if an index on status exists. The index would force random I/O for most of the table, which is slower than just reading it in order.

What to do: If you truly need most of the rows, a sequential scan is correct. If you only need a few, make sure the query is selective enough. If you need to aggregate over a large subset, consider a covering index (see below) or a partial index that only includes the rows you care about.

Reason 2: Outdated or Missing Statistics

The planner relies on pg_statistic to estimate row counts. If you haven’t run ANALYZE after significant data changes, the estimates can be wildly wrong. For example, a table that had 1 million rows when analyzed, but now has 10 million, may cause the planner to think a sequential scan is cheaper because it underestimates the number of rows that will be filtered.

Fix: Run ANALYZE tablename; or let autovacuum do it. You can also increase the statistics target for a column with ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 1000; to get more detailed histograms, which helps for columns with skewed data distributions.

Reason 3: The Index Can’t Be Used for the Query Pattern

Even a perfect index can’t help if the query’s WHERE clause uses a function or a pattern that doesn’t match the index’s b-tree ordering. Common cases:

  • Function on the column: WHERE lower(name) = 'alice' won’t use a plain index on name. Create an index on lower(name) instead.
  • Leading wildcard: WHERE name LIKE '%alice%' can’t use a b-tree index (only a trigram index). WHERE name LIKE 'alice%' can.
  • Type mismatch: If the column is varchar and you compare to an integer, PostgreSQL may cast the column, preventing index use. Fix the query to use the correct type.
  • OR conditions: If you have WHERE a = 1 OR b = 2 and only separate indexes on a and b, the planner may choose a bitmap OR, but if one side is non-selective, it may skip both. A composite index on (a,b) can help, but only if the query uses both columns with AND, not OR.

Reason 4: The Planner’s Cost Settings Are Off

PostgreSQL has a set of cost parameters that model hardware performance. The defaults are conservative and assume spinning disks. If you’re on fast SSD or have a lot of RAM, the planner may underestimate the cost of random I/O, making it think a sequential scan is cheaper than it really is. Key parameters:

  • random_page_cost (default 4.0): the cost of a random page read. On SSD, set it to 1.0–1.5.
  • seq_page_cost (default 1.0): cost of a sequential page read.
  • effective_cache_size (default 4GB): how much memory the OS uses for caching. If you have 32GB RAM, set this to 24GB so the planner assumes more data fits in cache.

You can change these with SET random_page_cost = 1.5; for the session, or in postgresql.conf globally. Be careful—over-tuning can cause the planner to choose index scans that are actually slower. Test with real workloads.

Reason 5: The Index Is Not Being Considered at All

Sometimes the planner doesn’t even see the index. This happens if:

  • The index was just created but ANALYZE hasn’t run yet, so the planner has no stats for it.
  • The index is invalid (e.g., after a failed CREATE INDEX CONCURRENTLY). Check with SELECT * FROM pg_index WHERE NOT indisvalid;.
  • The index is on an expression that doesn’t match the query’s expression exactly (e.g., you indexed date_trunc('day', created_at) but query uses created_at::date).

Diagnosis: Use EXPLAIN (ANALYZE, BUFFERS) to see what the planner chose and why. If you see Seq Scan and you expected an index, check the Filter line—it shows the condition. If the condition is not what the index supports, that’s the problem.

Reason 6: The Index Is Too Large or Too Wide

Indexes are not free. A b-tree index on a wide column (e.g., a long text column) can be larger than the table itself, making index scans more expensive. Also, if the index doesn’t contain all the columns needed for the query, PostgreSQL must fetch the table row for each match (a “heap fetch”). This doubles the I/O. A covering index that includes all columns in the SELECT and WHERE can eliminate heap fetches:

CREATE INDEX idx_covering ON users (email) INCLUDE (name, created_at);

Then a query selecting only those columns can be answered entirely from the index. But be careful: covering indexes make writes slower and take more disk. Use them only for hot read paths.

How to Diagnose and Fix

Follow this systematic approach:

  1. Run EXPLAIN (ANALYZE, BUFFERS) SELECT ...; and look at the actual rows vs. estimated rows. If they differ wildly, stats are stale.
  2. Check if the query has a function or type mismatch on the indexed column.
  3. Check the selectivity: if the query returns >10% of the table, accept the seq scan or rewrite the query.
  4. Try SET enable_seqscan = off; to force an index scan and see if it’s actually faster. If it is, adjust cost parameters or stats. If it’s slower, the planner was right.
  5. Consider a partial index: CREATE INDEX idx_active ON users (email) WHERE active; for queries that always filter on active = true. This makes the index tiny and highly selective.

Frequently Asked Questions

Why does my index work in one query but not another with the same column?

Because the queries differ in selectivity, joins, or the columns selected. If the second query selects columns not in the index, it needs heap fetches, which may make it cheaper to scan the table. Also, if the query joins to another table, the planner may choose a different join order that doesn’t use the index.

Should I use SET enable_seqscan = off to force index usage?

Only as a diagnostic, not as a permanent fix. It’s a global switch that disables all sequential scans, which will make many queries slower. If forcing an index scan makes your query faster, the real issue is cost settings or statistics—fix those instead.

How often should I run ANALYZE?

Autovacuum runs it automatically, but for tables that change rapidly, you may need to tune autovacuum_analyze_threshold and autovacuum_analyze_scale_factor. A good practice is to run ANALYZE after large bulk loads or deletes.

Does an index on a boolean column ever get used?

Rarely, because a boolean has only two values—a query filtering on one value will match about half the table, which is not selective. A partial index on WHERE active is more useful because it only contains the rows where the boolean is true, making it small and fast.

Why does my query use a bitmap index scan instead of a plain index scan?

Bitmap scans are chosen when the planner expects many matches but still fewer than a seq scan threshold. It reads index pages in bulk, sorts the heap locations, and then reads the table pages in order, reducing random I/O. This is normal and often faster than a plain index scan for moderate selectivity.

Can I make an index for a LIKE ‘%text%’ query?

Yes, but not with a regular b-tree. You need a trigram index: CREATE EXTENSION pg_trgm; CREATE INDEX ON table USING gin (column gin_trgm_ops);. This supports wildcard searches on both sides. For simple prefix searches ('text%'), a b-tree index works fine.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.