Laurenz Albe: Indexing “LIKE” in PostgreSQL and Oracle

© Laurenz Albe 2023 Unless you use the binary collation, creating a b-tree index to support a LIKE condition in PostgreSQL is not straightforward. This keeps surprising Oracle users, who claim that a regular b-tree index will of course always support LIKE. I decided to explore the differences between Oracle and PostgreSQL when it comes to indexing LIKE conditions. Is Oracle really smarter here? Indexing LIKE conditions It is clear that a pattern must not start with a wildcard if we want to support it with a b-tree index. A b-tree index is ordered, and a pattern like %smith could be anywhere in the ordered list. Consider searching a phone book for all names ending in “smith”: you’d have to search all entries to make sure you find all “Blacksmith”, “Hammersmith” and who knows what other “smiths” might be in there. The phone book is a good example for an index: the database could use the b-tree index, but it would have to scan the whole index, and that is not very efficient. If a pattern does not start with a wildcard, the case should be different. After all, the phone book is efficient if you are searching for all names that start with “black”: you may have to scan several pages, but “Black”, “Blackthorn” and “Blacksmith” are right next to each other in an alphabetically sorted list. So a b-tree index should be helpful in this case. So what is the difficulty with indexing LIKE conditions in PostgreSQL? An example to show the difficulties with indexing LIKE in PostgreSQL We create a table with three columns. The columns will contain the same strings, but I define them with different collations: CREATE TABLE likeme ( id integer PRIMARY KEY, s_binary text COLLATE "C" NOT NULL, s_libc text COLLATE "cs_CZ.utf8" NOT NULL, s_icu text COLLATE "cs-CZ-x-icu" NOT NULL ); The three collations I used are The “binary” collation C, also known as POSIX. It compares strings byte by byte, so it is very fast and never changes. The Czech collation as de[...]