Searching for a string at the end of a text column is typically slow, because no index is used. How to build a suitable index and make LIKE '%end' fast.
The problem with LIKE
In the article Find texts starting, ending or containing a given string we discussed the LIKE operator and its performance. We mentioned that a query like this cannot use an index:
SELECT title FROM film WHERE title LIKE '%odissey';The reason is that an SQL query can use an index, or the leftmost part of an index. So it cannot use an index to search the end of a column, or in the middle of a column. LIKE can only use an index to search for the beginning of a column.
The solution: indexes on expressions
Some DBMSs allow to create indexes on an SQL expression. They are called expressional indexes, or functional indexes.
Some other DBMSs allow to create a column that contains the result of an SQL expression, and an index on this column. The result is the same. This type of columns are called virtual columns, or generated fields.
Also, most DBMSs support the reverse() function, that reverses the order of characters in a string. If we index an expression like reverse(column) and we search the resulting index for string, we’ll be able to search the end of a string while using an index.
PostgreSQL
The PostgreSQL syntax is the following, but we’ll not dig into the details:
CREATE INDEX ON film (reverse(title) text_pattern_ops);
SELECT title FROM film WHERE reverse(title) LIKE reverse('odissey%');Reference
PostgreSQL documentation: