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?
Attachment:
signature.asc
Description: OpenPGP digital signature
