Re: Question on Trigram GIST indexes

On 5 January 2013 20:20, ERR ORR <rd0002(at)gmail(dot)com> wrote:

>
>
>
> 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)