Question on Trigram GIST indexes

From: ERR ORR <rd0002(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Question on Trigram GIST indexes
Date: 2012-12-22 15:24:27
Message-ID: CALtFtE+hwg0_EYODUr9oLV1AT9FwzFnd-DB02nnH1nzt0T9YBg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I was trying to make Postgresql use a trigram gist index on a varchar
field, but to no avail.

Specifically, I was trying to replicate what is done in this blog post:
http://www.postgresonline.com/journal/archives/212-PostgreSQL-9.1-Trigrams-teaching-LIKE-and-ILIKE-new-tricks.html

I use Postgresql 9.1.7 on Linux FC17 64bit, my locale is UTF8.

My full table definition is

CREATE TABLE "TEST"
(
"RECID" bigint NOT NULL DEFAULT next_id(),
"TST_PAYLOAD" character varying(255),
CONSTRAINT "PK_TEST" PRIMARY KEY ("RECID")
USING INDEX TABLESPACE local
)
WITH (
OIDS=FALSE
);

CREATE INDEX "TEST_PAYLOAD_PATTERN_1_IDX"
ON "TEST"
USING btree
("TST_PAYLOAD" COLLATE pg_catalog."default" varchar_pattern_ops)
TABLESPACE local;

CREATE INDEX "TEST_PAYLOAD_TRIGRAM_GIST_1_IDX"
ON "TEST"
USING gist
("TST_PAYLOAD" COLLATE pg_catalog."default" gist_trgm_ops)
TABLESPACE local;

CREATE INDEX "TEST_PAYLOAD_TRIGRAM_GIN_1_IDX"
ON "TEST"
USING gin
("TST_PAYLOAD" COLLATE pg_catalog."default" gin_trgm_ops)
TABLESPACE local;

The field "TST_PAYLOAD" contains 26389 names of cities, all in uppercase.

I have pg_tgrm installed - actually all extensions are present.

Queries which use "WHERE "TST_PAYLOAD" LIKE 'SEAT%'" go to the btree index
as it should.
Queries which use "WHERE "TST_PAYLOAD" LIKE '%EAT%'" *should* use the GIST
index but do a full table scan instead.
(I am looking for names like 'SEATTLE')

I also tried dropping the btree index but that has no influence on the
behavior.

I'd be grateful if anybody could explain to me what I am doing wrong.

Thanks in advance.

Browse pgsql-general by date

  From Date Subject
Next Message Philipp Kraus 2012-12-22 15:32:20 rule / trigger definition
Previous Message hubert depesz lubaczewski 2012-12-22 13:13:18 Re: [COMMITTERS] pgsql: Allow a streaming replication standby to follow a timeline switc