On Wed, May 24, 2023 at 4:35 PM Pavel Horal <pavel.horal@xxxxxxxxxxxx> wrote:
I didn't see your email when first sent, and stumbled upon it while searching for something else. But it still might be worthwhile commenting even after all of this time.
Is my understanding correct that this happens only because pg_trgm is not able to actually determine if the matched item from the index search is actually much much longer than the query? Is there any way how the performance can be improved in this case? I thought that I can store number of trigrams in the index, but that is not being used by the query planner:CREATE INDEX test_idx2 ON test USING GIN (value gin_trgm_ops, array_length(show_trgm(value), 1));EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM test WHERE value % 'lorem' AND array_length(show_trgm(value), 1) < array_length(show_trgm('lorem'), 1) / 0.5;
The main problem here is of _expression_ type. You have an index using an _expression_ returning an int, while you are comparing it to an _expression_ returning a numeric. That inhibits the use of the index over that _expression_.
Just casting the type when creating the index is enough (given your test case) to get this to do what you want:
CREATE INDEX test_idx2 ON test USING GIN (value gin_trgm_ops, (array_length(show_trgm(value), 1)::numeric));
However, it would probably be more efficient to partition the table on the trigram count, rather than adding that count to the index. Then it could just skip any partition with too many trigrams.
Cheers,
Jeff
