trigram indexing for LIKE ALL filters

Question

I find myself looking for a way to dynamically restrict search results so that all searches must be present:

["A TE","SOME"] search array:

"MS A TEXT B SOMETHING" --> matches

"MS A B SOMETHING" --> doesnt match

so I'm using a GIN index and LIKE ALL:

CREATE INDEX ix_gin_code ON ilike_test USING GIN(code gin_trgm_ops);

select * from ilike_test where code ilike ALL (:search_terms::text[]);

however, the index doesn't get used when filtering by ALL, only with direct LIKE and LIKE ANY.

Is there any reason for this? Or what's an alternative (fast) way to do this search?

Actual database:

• 500k rows
• text column median row size of 4kb

Fiddles with explains:

http://sqlfiddle.com/#!17/cc555/14 -- disable seq scan, yielding an strange LIKE ALL explain

http://sqlfiddle.com/#!17/cc555/15

Answer 1

I don't know why it works this way. It seems to be a property of the core PostgreSQL index machinery, not part of the pg_trgm module itself. If you really want to dig into, one of the PostgreSQL self-hosted mailing lists is probably a better place to ask.

Since you asked for alternative ways to do the search, one would be to separate the array and combine the expressions with AND:

select * from ilike_test where code ilike '%n/a%"' and code ilike '%03/02/2021#A%';

This will use the index, even though it is the "same thing" as the one that won't. The need to rewrite the query to account for the size of the array is not ideal.

Note that %n/a% has no useful trigrams, but I don't see a very feasible way to use that info in an automated fashion. (it actually does have one usable trigram, a. But it is likely to be a very common one in your data)

Answer 2

After some in-depth search in the mailing list, as of now the only workaround I've seen is from this thread.

In essence, you can do:

body ilike all (@q::text[]) and body ilike (@q::text[])[1]

to force it to use the index:

Bitmap Heap Scan on test_table  (cost=12.01..16.02 rows=1 width=746) (actual time=0.328..1.366 rows=2 loops=1)
  Recheck Cond: (body ~~* '%n/a%'::text)
  Rows Removed by Index Recheck: 10
  Filter: (body ~~* ALL ('{%n/a%,%03/02/2021#A%}'::text[]))
  Rows Removed by Filter: 6
  Heap Blocks: exact=3
  ->  Bitmap Index Scan on ix_gin_body  (cost=0.00..12.01 rows=1 width=0) (actual time=0.009..0.009 rows=18 loops=1)
        Index Cond: (body ~~* '%n/a%'::text)
Planning Time: 0.072 ms
Execution Time: 1.379 ms