Matching performance with pattern from table column

Question

I have a query which looks like:

SELECT *
FROM my_table
WHERE 'some_string' LIKE mytable.some_column || '%%'

How can I index some_column to improve this query performance?
Or is the a better way to filter this?

Answer 1

This predicate searches for all prefixes for a given string:

WHERE 'some_string' LIKE mytable.some_column || '%'

If % has special meaning in your client, escape with another %. Else '%%' is just noise and can be replaced with '%'.

The most efficient solution should be a recursive CTE (or similar) that matches to every prefix exactly, starting with some_column = left('some_string', 1), up to some_column = left('some_string', length('some_string')) (= 'some_string').

You only need a plain B-tree index on the column for this. Depending on details of your implementation, partial expression indexes might improve performance ...

Related:

• Reverse pattern matching: find the longest prefix
• Algorithm for finding the longest prefix
• PostgreSQL LIKE query performance variations
• Pattern matching with LIKE, SIMILAR TO or regular expressions

Answer 2

I believe you intend to write the following query:

SELECT *
FROM my_table
WHERE mytable.some_column LIKE 'some_string%';

In other words, you want to find records where some column begins with some_string followed by anything, possibly nothing at all.

As far as I know, a regular B-tree index on some_column will be effective, to a point, in your query. The reason is that Postgres can traverse the tree looking for the prefix some_string. Once it has found that entry, beyond that the index might not help. But an index on some_column should give you some performance benefit here.

A condition where an index would not help would be the following:

WHERE mutable.some_column LIKE '%some_string';

In this case, the index is rendered mostly useless, because we have no idea with what prefix the column value should begin.