Search Postgresql Archives

Re: Question on Trigram GIST indexes

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 5 January 2013 20:20, ERR ORR <rd0002@xxxxxxxxx> 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)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux