Search code examples
postgresqlindexingpattern-matchingpostgresql-performance

Matching performance with pattern from table column

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?

Solution

  • 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: