Queries which use "WHERE "TST_PAYLOAD" LIKE 'SEAT%'" go to the btree index as it should.Queries which use "WHERE "TST_PAYLOAD" LIKE '%EAT%'" *should* use the GIST index but do a full table scan instead.(I am looking for names like 'SEATTLE' in this example)I also tried dropping the btree index but that has no influence on the behavior.
I have the same problem:
Index:
CREATE INDEX akb_art_abstract_trgm
ON akb_articles
USING gin
(abstract gin_trgm_ops);
and
SELECT title, SIMILARITY(abstract, 'skeef') FROM akb_articles WHERE SIMILARITY (abstract, 'water') > 0
results in a full sequential scan:
"Seq Scan on public.akb_articles (cost=0.00..45751.67 rows=107025 width=666) (actual time=0.236..63153.268 rows=169265 loops=1)"
" Output: title, similarity(abstract, 'skeef'::text)"
" Filter: (similarity(akb_articles.abstract, 'water'::text) > 0::double precision)"
" Buffers: shared hit=39000 read=46460"
"Total runtime: 63173.663 ms"
Regards
Johann
Because experiencing your loyal love is better than life itself,
my lips will praise you. (Psalm 63:3)
