How to use many LIKE operators and use index

Question

In my query I want find rows that match one of many LIKE operators. I know 3 ways of doing that but only one of them can use index.

Lets start with table:

CREATE TABLE dir (
    id BIGSERIAL PRIMARY KEY,
    path TEXT NOT NULL
);

CREATE INDEX path_idx ON dir(path TEXT_pattern_ops);

After inserting sample data I can do:

EXPLAIN ANALYZE 
SELECT id, path FROM dir
   WHERE path LIKE 'A%'
      OR path LIKE 'B%'
      OR path LIKE 'C%';

Above query use index correctly.

Second way:

EXPLAIN ANALYZE 
SELECT id, path FROM dir
  WHERE path LIKE ANY(ARRAY['A%', 'B%', 'C%']::TEXT[]);

This query will not use index. Last approach I know:

CREATE TABLE patterns (pattern) AS VALUES
('A%'),
('B%'),
('C%');

EXPLAIN ANALYZE 
SELECT id, path FROM dir
  JOIN patterns ON (dir.path LIKE patterns.pattern);

This query like the previous one will not use index.

Here is SQL Fiddle for those how want to play with those queries: http://sqlfiddle.com/#!17/24031/2

Problem: Query with path LIKE X OR path LIKE Y is completely unreadable for many patterns (number of patterns may vary from few to hundreds or small thousands) and I am afraid that large query may be slow to parse or even to hit 1GB limit of query length (some patterns may have very long prefixes).

Question: is there any oder method returning same results that would not require putting all patterns directly in query (like in this option with join)?

Answer 1

You can create a trigram index that will support your query.

For that you need the pg_trgm extension; run the following as superuser:

CREATE EXTENSION pg_trgm;

Then you can create a GIN index:

CREATE INDEX ON dir USING gin (path gin_trgm_ops);

This index can be used with your second and third approach, so it should do the trick for you.

With short patterns like the one in your examples, the index will not be very effective.

You can also use a GiST index, that will probably be smaller, but slower to search.

Note that you can use that index also with patterns that start with %.