Indexes That Collapse Before EXPLAIN: Tracking Down the Four Culprits

You've created an index, yet EXPLAIN shows a Seq Scan. The table has a few million rows. If it's the first time, start by checking whether the index was actually attached and whether ANALYZE has been run. If neither is the issue, the query's shape is fundamentally preventing the optimizer from using that index. The cause narrows down to four branches. If you dig around without knowing the order, a single common trap can eat up hours at a time.

When a Function Wraps a Column, the Index Doesn't Even Bother

Conditions like WHERE date_trunc('day', created_at) = '2026-08-14' are common on log tables. Even if you create an index on created_at, this query can't use it. The index is sorted by the column's raw values, and the value after applying date_trunc has nothing to do with that sort order. You're querying by a transformed value, so there's no way to pin down a search starting point. Using lower(name) for case-insensitive search is a variation of the same problem.

The fix goes two ways. One is opening up a range with WHERE created_at >= '2026-08-14' AND created_at < '2026-08-15'; the other is putting an expression index directly on date_trunc('day', created_at). The former is the standard approach, and the latter is for when transformed conditions show up often. At plan time, the expression must match the index definition literally, so even a single extra space gets it ignored. There's also a caveat when rewriting it as a range. If you bundle it into a BETWEEN, the boundary calculation easily drops the millisecond window. That's why the >= and < combination is safe. The deciding criterion is whether the column's raw form remains in the condition.

A Type Mismatch Puts the Cast on the Column Side

When a literal's type differs from the column's type, as in WHERE id = '123', PostgreSQL casts one side to make them match. A cast that turns the constant into the column's type doesn't hurt the index. But when the column is wrapped into the constant's type, it becomes exactly the same as the earlier function problem. Since the conversion is applied to the column, the sort order disappears.

The rule that decides the direction is called the preferred type, and it's easy to overlook. Because timestamptz is a preferred type, comparing a timestamp column against a timestamptz value casts the column side and breaks the index. Schemas where a varchar column meets a text literal run into the same direction problem. Explicitly casting the column like WHERE status::text = 'active' is a mistake of the same stripe.

The place this pattern actually blows up is parameter binding. When an ORM or prepared statement passes a literal as varchar, the server builds the comparison expression around that type and puts a cast on the column side. Seeing a cast like (created_at)::date = ... in EXPLAIN's Filter confirms the suspicion. If you're on JDBC, the fix is to match the binding type to the column — using setBigDecimal or setTimestamp instead of setString. Adding an explicit cast to the literal, turning it into '123'::bigint, and then checking whether the Seq Scan disappears is the same kind of verification.

A Leading % in LIKE Makes the B-tree Give Up

Substring search is something a B-tree can't do in the first place. The moment a wildcard lands in front, as in LIKE '%게시글%', no search starting point can be established. Even prefix search like LIKE '게시글%' sometimes can't be used under the default collation when the locale isn't C, because the dictionary order and the index's sort order diverge. Korean's dictionary order doesn't match the index's byte order. It's a trap you hit often in Korean-language environments, and case-insensitive ilike searches eventually resolve to lower() and fall into the same trap.

Prefix search is solved by building a B-tree with the text_pattern_ops operator class. Substring search uses a GIN index from the pg_trgm extension, which comes with a large index and write overhead. With search terms of just two or three characters, trigrams get weak against noise, accuracy drops, and the optimizer sometimes picks a full scan. Without a minimum-length restriction, this index is a half-dead resource. Once searches go past a few million rows, it's also time to stop tuning indexes here and consider moving to an external search engine.

When the Optimizer Decides a Plain Scan Is Better

The last branch where a full scan shows up even though the index is valid is pure cost calculation. In a million-row table where 90% of the rows have status = 'active', a WHERE status = 'active' query could take the index but won't. You're going to read nearly the whole table anyway, so taking the detour of index access is actually more expensive. Low selectivity means the index is a loss. This isn't a bug or a mistake. The optimizer wasn't wrong.

This decision lives on statistics. If you didn't run ANALYZE after a bulk load, the estimated and actual row counts diverge by a wide margin, and a full-scan decision made in that state can't be trusted. Start by looking at estimated and actual row counts side by side with EXPLAIN ANALYZE. If the statistics are correct and the index still isn't used, that's when it's a cost-estimation problem, and touching random_page_cost or effective_cache_size comes after that. For a column where only two or three values repeat, narrowing the condition and building a partial index is cheaper. If 100 million rows have piled up on a column with only 3 distinct values, that column's index was doomed from the start.

The Order for Narrowing Down the Cause with EXPLAIN

Look at the Filter under the Seq Scan first. If you see a function or a cast, it's the first two branches; if you see LIKE, it's the third; if nothing shows up, it's cost estimation. Casts are easy to miss. You need to check directly whether the filter expression contains :: or a cast the parser generated.

Seq Scan on logs  (cost=0.00..1542.00 rows=6 width=124)
  Filter: ((created_at)::date = '2026-08-14'::date)

That single line is enough. (created_at)::date is direct evidence that a cast was placed on the column.

There's one switch that's the most useful. Set SET enable_seqscan = off; and run EXPLAIN again. If the index still doesn't appear in this state, the query structure is the cause; if the index suddenly appears, the condition is valid and cost estimation is the cause. It's for diagnosis only — it must not be left on in production — so restore the original value after comparing.

The suspicion order is also set by table size. At the hundred-thousand-row level, start with type mismatch; past a million rows, look at function wrapping and selectivity together. There's only one way to verify: fix the suspected condition and check whether the Seq Scan turns into an Index Scan. If it does, the cause is confirmed; if it doesn't, move to the next branch. Two branches often overlap, so don't stop just because you fixed one.

Changing the shape of the query comes before creating an index. None of the four branches is solved by adding an index alone. Strip off the function, match the type, remove the % in front of LIKE. Only when the optimizer picks an Index Scan instead of a full scan are you standing at the starting point of the performance conversation.

Comments

Loading comments.