From: | Ivan Voras <ivoras(at)freebsd(dot)org> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Trigram (pg_trgm) GIN index not used |
Date: | 2013-02-21 11:52:00 |
Message-ID: | kg51ot$3l8$1@ger.gmane.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello,
I have a table with the following structure:
nn=> \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=> explain select id,title from documents where raw_data ilike '%zagreb%';
QUERY PLAN
---------------------------------------------------------------
Seq Scan on documents (cost=0.00..6648.73 rows=180 width=98)
Filter: (raw_data ~~* '%zagreb%'::citext)
(2 rows)
nn=> explain select id,title from documents where raw_data like '%zagreb%';
QUERY PLAN
---------------------------------------------------------------
Seq Scan on documents (cost=0.00..6692.71 rows=181 width=98)
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-Trigrams-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?
From | Date | Subject | |
---|---|---|---|
Next Message | Victor Yegorov | 2013-02-21 11:58:27 | Re: PostgreSQL Live CD for 9.2.3 released |
Previous Message | Devrim GÜNDÜZ | 2013-02-21 11:11:25 | Re: PostgreSQL Live CD for 9.2.3 released |