Re: Question on Trigram GIST indexes
| От | Johann Spies |
|---|---|
| Тема | Re: Question on Trigram GIST indexes |
| Дата | |
| Msg-id | CAGZ55DQqi4sKHAEUPJR7VMx-rexSdc1wFd2tLR6fqkaju7pbPA@mail.gmail.com обсуждение исходный текст |
| Ответ на | Question on Trigram GIST indexes (ERR ORR <rd0002@gmail.com>) |
| Список | pgsql-general |
On 5 January 2013 20:20, ERR ORR <rd0002@gmail.com> wrote:
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)
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)
В списке pgsql-general по дате отправления: