Sorted Means Seekable Prefixes
Because a B tree keeps keys sorted, any condition that pins down a leading prefix of the value can seek. A search for names starting with a given letter lands on the first matching leaf and walks forward until the prefix no longer matches.
Anchored Versus Floating
The anchor must be on the left:
- A prefix match like a string that starts with a value is seekable, since the sort order groups those entries together.
- A leading wildcard, matching anything that ends with or contains a value, is not, because the matching rows are scattered across the whole index.
What Breaks A Prefix Seek
A prefix seek also dies when the leading part is transformed:
- Wrapping the column in a function, like lowercasing it, hides the sorted order from the planner.
- Comparing against a value with a leading wildcard forces a scan.
- Implicit type casts on the column can have the same effect.
To keep the seek, filter on the raw column with the constant transformed instead, or build a dedicated index on the transformed expression.
Key idea
A sorted index seeks when a query pins a leading prefix, but a leading wildcard, a function on the column, or an implicit cast scatters the matches and forces a scan.