Reading the Signs in EXPLAIN: When Queries Ignore Your Indexes

In PostgreSQL, full scans showing up even after you've created an index are a common sight. The moment Seq Scan appears in the EXPLAIN output, a familiar feeling of confusion follows: "Why was the index ignored?" But in most cases, the index wasn't never created. It was created and then simply ignored by the query. The patterns that sever the connection between a query and its index usually narrow down to four: function-wrapped columns, type mismatches, leading wildcards in LIKE, and low selectivity. Let's look at how each one distorts the execution plan.

The moment a function wraps a column, the index becomes a theory

A condition like WHERE lower(email) = 'foo@example.com' is a typical case. Even with a btree index on the email column, this condition won't use the index. The index only holds the original values, before any function was applied. The planner needs to look up the expression lower(email) as a whole in the index, but that expression exists nowhere. In the end it scans the entire table, calling the function on every row.

The fix for this situation is a functional index. Put the function in the index expression, as in CREATE INDEX idx_email_lower ON users ((lower(email)));, and the index gets used when the expression in the condition matches character-for-character. Writing the condition as LOWER(email) in uppercase usually matches thanks to normalization, but if the form changes even slightly, like lower(email || ''), the planner treats it as a different expression. Even if the behavior is identical, a different string kills the index.

Functional indexes also only accept immutable functions by default. lower() is fine, but a function whose result changes on every call, like now(), can't go into an index. And the real lesson of this pattern lies on the application side. If you have to look up with lower() on every login, normalizing emails to lowercase at write time and putting a plain btree index on them is easier to manage. A functional index works, but it leaves the ongoing cost of keeping the condition and the index expression in sync in two places. For something queried infrequently, wrapping only the constant side, as in WHERE email = lower($1), is also worth considering.

Type mismatch leaves a trace in Filter:

The second pattern is when the condition and the column type don't line up. Strings tend to be forgiving about this. Comparisons between a varchar column and a text literal are often let through. The painful side is numbers and time. Passing a string number to an integer column, or comparing a timestamp column directly with a date, brings implicit casting into play. The question is which side the cast lands on. When it lands on the column, the index collapses on the spot; when it lands on the literal, everything stays intact.

Reading the sign in EXPLAIN is simple. If something like created_at::date appears next to the column in the Filter: condition, that column has already become unable to use an index. Conversely, if the cast is on the literal, like '2026-01-01'::date, it's normal. The fix usually ends with matching the literal to the column type. Writing WHERE created_at >= DATE '2026-01-01' keeps the column unwrapped, so the index survives. ORMs sometimes slip such casts in quietly, so if the plan doesn't change after fixing the SQL, logging and comparing the query the app actually sends is the faster path.

A leading % in LIKE leaves btree with no answer

The third pattern is the one you'll witness most often. LIKE '%keyword%' leaves the territory of a btree index the moment a % goes on the front. A btree only knows sorted range searches; it has no way to find a string tucked in the middle. If the prefix is fixed, like LIKE 'abc%', the btree engages. That single difference splits the execution plan.

If a middle-of-string search is genuinely required, you have to change the index type. A trigram index built by the pg_trgm extension lets even %keyword% be narrowed down by an index.

CREATE EXTENSION pg_trgm;
CREATE INDEX idx_name_trgm ON users USING gin (name gin_trgm_ops);

A trigram index splits the string into three-character chunks and stores them. So if the search term is shorter than three characters, it's only half as helpful. If two-character searches feel conspicuously slow, the trigram index's nature is the likely culprit. Using ILIKE removes case sensitivity, but it also changes the conditions under which the trigram index engages. By this point, it's no longer a problem that ends with an index alone. Cooperation from the application layer, such as setting a minimum search-term length as a service policy, has to come along with it.

When selectivity is low, a full scan is the correct answer

The last pattern differs in character from the first three. It's not a bug or a mistake; it's just that the planner's calculation happened to turn out that way. If using the index would still mean reading 20–30% of the table, an index scan with its interspersed random page access costs more than a sequential scan. The planner picks whichever is cheaper based on its statistics-driven cost estimates. An index built on a low-selectivity column therefore never even makes it into consideration.

Say a status column has only two values, and one of them accounts for 90% of the rows. A query looking for that value will pick a full scan even with an index in place. This judgment is usually accurate. It's close to reading nearly the whole table, so there's no reason to go back and forth through index pages. If there's a problem, it's not in creating the index but in the design of trying to narrow queries down with this column at all. A column with a skewed value distribution is not a candidate for an index.

If you want to keep the index alive, there are two paths. Either add other constraints to the condition to raise selectivity, or aim for an index-only scan with a covering index. Usually the former comes first. When multiple conditions are joined with AND, the planner weighs the selectivity of each together, so an index that was discarded on its own becomes useful in the overall plan.

Statistics can also go stale and undervalue the index. If ANALYZE hasn't run after a large delete or a batch, the planner computes costs with a distribution from long ago. Before doubting the index, run a single line of ANALYZE first. If the distribution itself hasn't changed but the plan looks odd, that's the moment to check the statistics settings.

The order for reading signs in EXPLAIN

The procedure for narrowing down the cause is fixed. The first step is finding the spot in the plan where Seq Scan is attached. In a query with joins, pin down which table is doing the full scan first. If only one of several tables is fully scanned while the rest use indexes, you can treat that one as the only problem.

Next, read the conditions on that table. If Filter: shows a function, a cast, or a wildcard, that's the culprit. If Index Cond: is present yet a full scan still happens, it's not a condition problem but a cost-judgment problem. In that case, turn your attention to selectivity. EXPLAIN only shows the execution plan; it doesn't tell you actual timing. When you need confirmation, run EXPLAIN (ANALYZE, BUFFERS). If the estimated rows and the actual rows diverge widely, it's a statistics problem, not an index problem. If the estimate is off by a factor of several, it's time to check the autovacuum settings.

One warning to add. The habit of toggling planner settings like enable_seqscan = off to experiment while confirming the cause is only half a solution. Forcing the index can still result in slow performance. If forcing it made things faster, that means the planner's cost calculation was wrong, so you should look at random_page_cost or the statistics settings. If it's still slow even when forced, then the query never needed an index in the first place. This distinction is the key. The planner usually discards an index because the index is slow, not because the planner is stupid.

Comments

Loading comments.