How to optimize inverse pattern matching in Postgresql?

user_5151542 0
Viewed 57

I have Pg version 13.

CREATE TABLE test_schemes (
    pattern  TEXT  NOT NULL,
    some_code   TEXT  NOT NULL
);

Example data

----------- | -----------
pattern     | some_code
----------- | -----------
__3_        | c1
__34        | c2
1_3_        | a12
_7__        | a10
7138        | a19
_123|123_   | a20
___253      | a28
253         | a29
2_1         | a30

This table have about 300k rows. I want to optimize simple query like

SELECT * FROM test_schemes where '1234' SIMILAR TO pattern

----------- | -----------
pattern     | some_code
----------- | -----------
__3_        | c1
__34        | c2
1_3_        | a12
_123|123_   | a20

The problem is that this simple query will do a full scan of 300k rows to find all the matches. Given this design, how can I make the query faster (any use of special index)?

postgresql
2 Answers

Internally, SIMILAR TO works similar to regexes, which would be evident by running an EXPLAIN on the query. You may want to just switch to regexes straight up, but it is also worth looking at text_pattern_ops indexes to see if you can improve the performance.

user_1082470

If the pipe is the only feature of SIMILAR TO (other than those present in LIKE) which you use, then you could process it into a form you can use with the much faster LIKE.

SELECT * FROM test_schemes where '1234' LIKE any(string_to_array(pattern,'|'))

In my hands this is about 25 times faster, and gives the same answer as your example on your example data (augmented with a few hundred thousand rows of garbage to get the table row count up to about where you indicated). It does assume there is no escaping of any pipes.

If you store the data already broken apart, it is about 3 times faster yet, but of course give cosmetically different answers.

create table test_schemes2 as select unnest as pattern, somecode from test_schemes, unnest(string_to_array(pattern,'|'));
SELECT * FROM test_schemes2 where '1234' LIKE pattern;
user_17212390
Related