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  (Ivan Voras <ivoras@freebsd.org>)
Re: Trigram (pg_trgm) GIN index not used  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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 по дате отправления:

Предыдущее
От: Devrim GÜNDÜZ
Дата:
Сообщение: Re: PostgreSQL Live CD for 9.2.3 released
Следующее
От: Victor Yegorov
Дата:
Сообщение: Re: PostgreSQL Live CD for 9.2.3 released