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.