A LIKE Pattern That Starts With a Wildcard Cannot Use a B-Tree Index
A B-tree finds rows by shared prefix, so LIKE 'smith%' is a range it can jump to, while LIKE '%smith%' has no known starting point and forces a scan of every row. Case-insensitive search has the same problem unless the index matches the comparison. When you genuinely need substring search you have left B-tree territory and need a different kind of index or a search service — knowing that is what stops you from tuning a query that cannot be tuned.
Questions this Concept answers
Why does a leading wildcard defeat a B-tree?
J
jeremy
Text
PostgreSQL LIKE Query Speedup: Indexes & Strategies
This page explains why some text searches in PostgreSQL are fast and others crawl. A normal B-tree index keeps values in sorted order, so a search like LIKE 'foo%' can jump straight to the matching r…