Trigram (pg_trgm) GIN index not used
От | Ivan Voras |
---|---|
Тема | Trigram (pg_trgm) GIN index not used |
Дата | |
Msg-id | kg51ot$3l8$1@ger.gmane.org обсуждение исходный текст |
Ответы |
Re: Trigram (pg_trgm) GIN index not used
Re: Trigram (pg_trgm) GIN index not used |
Список | pgsql-general |
Hello, I have a table with the following structure: nn=3D> \d documents Table "public.documents" Column | Type | Modifiers ---------------+----------+----------------------------------------------= ---------- id | integer | not null default nextval('documents_id_seq'::regclass) ctime | integer | not null default unix_ts(now()) dtime | integer | not null title | citext | not null html_filename | text | not null raw_data | citext | not null fts_data | tsvector | not null tags | text[] | flags | integer | not null default 0 dtype | integer | not null default 0 Indexes: "documents_pkey" PRIMARY KEY, btree (id) "documents_html_filename" UNIQUE, btree (html_filename) "documents_raw_data_trgm" gin (raw_data gin_trgm_ops) "documents_title_trgm" gin (title gin_trgm_ops) I'd like to use pg_trgm for matching substrings case-insensitively, but it doesn't seem to use the index: nn=3D> explain select id,title from documents where raw_data ilike '%zagr= eb%'; QUERY PLAN --------------------------------------------------------------- Seq Scan on documents (cost=3D0.00..6648.73 rows=3D180 width=3D98) Filter: (raw_data ~~* '%zagreb%'::citext) (2 rows) nn=3D> explain select id,title from documents where raw_data like '%zagre= b%'; QUERY PLAN --------------------------------------------------------------- Seq Scan on documents (cost=3D0.00..6692.71 rows=3D181 width=3D98) Filter: (raw_data ~~ '%zagreb%'::citext) (2 rows) When I try to create a GIST index as advised by the comment at: http://www.postgresonline.com/journal/archives/212-PostgreSQL-9.1-Trigram= s-teaching-LIKE-and-ILIKE-new-tricks.html I get the following error: ERROR: index row requires 10488 bytes, maximum size is 8191 What am I doing wrong?
В списке pgsql-general по дате отправления: