What an Index Actually Does, and the Query It Cannot Help

Start at the added and you miss it. Start at the scan and you miss how it arrived. The bug lived in the hand-off.

I have lost on-call weeks to this exact shape of slow burn, so the order below is the order I fix it in.

Strip the index that cannot be used down and one property makes it invisible: the index exists and the column is named in the query, so the expectation is reasonable, and the reasonableness is the surprise, because the index ordered the raw column and the query asked about the transformed one.

This was Postgres with a B-tree index, and the mechanics are the same for any index type that stores a derived ordering.

What the index actually stores

The index is the sorted copy, and the sorted is the column's values, and the values are the raw, and the raw is the ordered, and the ordered is the lookup's speed, and the speed is the binary search, and the search is the log, and the log is the fast, and the fast is the index's gift, and the gift is the conditional on the query matching the ordering.

The query must ask about the same expression the index ordered, and the same is the raw column, and the raw column is the not the function of it, and the function is the different value, and the different value is the not in the index, and the not in the index is the scan, and the scan is the every row, and the every row is the slow, and the slow is the incident.

The index is the map, and the map is the territory's ordering, and the ordering is the specific expression, and the specific is the contract, and the contract is the query's requirement, and the requirement is the match, and the match is the fix's target, and the target is the predicate's shape.

The predicates that defeat the index

The function on the column. The lower of the name is the transformed, and the transformed is the not indexed, and the not indexed is the scan, and the scan is the fix's target, and the target is the expression index, and the expression index is the same function's ordering, and the ordering is the match, and the match is the fix.

The implicit type conversion. The column is the text and the parameter is the number, and the number is the cast, and the cast is the function, and the function is the wrapped, and the wrapped is the scan, and the scan is the fix's target, and the target is the parameter's type, and the type is the match, and the match is the fix.

The leading wildcard. The like with the percent first is the unanchored, and the unanchored is the ordering's uselessness, and the uselessness is the scan, and the scan is the fix's target, and the target is the trigram index, and the trigram is the substring's ordering, and the ordering is the fix.

The negation. The not equal is the everything except, and the except is the most of the table, and the most is the scan's cheaper, and the cheaper is the planner's choice, and the choice is the correct, and the correct is the not the index's failure, and the failure is the query's shape, and the shape is the fix's target.

The low-selectivity column. The boolean is the two values, and the two values is the half the table, and the half is the scan's cheaper, and the cheaper is the planner's choice, and the choice is the correct, and the correct is the index's uselessness, and the uselessness is the not a bug, and the bug is the expectation, and the expectation is the fix's target.

How to see it

The plan is the evidence, and the evidence is the scan versus the index scan, and the two are the different nodes, and the nodes are the diagnosis, and the diagnosis is the fix's entry, and the entry is the EXPLAIN, and the EXPLAIN is the one command, and the command is the discipline, because the index's presence is the not the index's use.

The rows removed by the filter is the wasted work, and the wasted is the scan's read, and the read is the discarded, and the discarded is the predicate's late application, and the late is the index's absence, and the absence is the diagnosis, and the diagnosis is the fix.

This is the same reading as the EXPLAIN ANALYZE plan read like a map, which is why the two fixes look alike.

What actually fixed it

Match the predicate to the indexed expression. The query uses the raw column, and the raw is the ordered, and the ordered is the index's use, and the use is the fix, and the fix is the rewrite, and the rewrite is the discipline, because the transformation belongs in the application rather than the predicate.

Build the expression index where the transformation is required. The index is the function's result, and the result is the ordered, and the ordered is the match, and the match is the fix, and the fix is the create index on the expression, and the expression is the discipline.

Fixed the parameter's type. The cast is the absent, and the absent is the raw comparison, and the comparison is the index's use, and the use is the fix, and the fix is the type, and the type is the one character, and the character is the discipline.

Measured the selectivity before adding the index. The selectivity is the fraction, and the fraction is the scan's cheaper, and the cheaper is the index's uselessness, and the uselessness is the saving, and the saving is the fix, and the fix is the measurement, and the measurement is the discipline, because the index on the low-selectivity column is the cost without the benefit.

The takeaway

An index stores the ordering of a specific expression, so a query can only use it when the predicate matches that expression exactly; a function, a cast, a leading wildcard or a negation defeats it. Read the plan to confirm the index is used, rewrite the predicate or build an expression index, fix the parameter types, and check the selectivity before adding the index.

The index was created and the query still scanned the whole table, because the predicate asked about the lowercased column while the index ordered the raw one, and the database had no way to use an ordering of values it was not asked about. Name that one property in review and this class of bug gets hard to ship.

Published on debugly.dev by Rohit Bhadani | Founder of Krova Cloud