Trigram (pg_trgm) GIN index not used

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?

Responses

Browse pgsql-general by date

  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