On Sat, Jan 5, 2013 at 12:20 PM, ERR ORR <rd0002@xxxxxxxxx> wrote: > > @Moderators: I am reposting this because the original from 22 December > apparently didn't arrive on the list. > > 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 COLLATE pg_catalog."default" clause is inserted by the DB (default is > "Unicode"). I also tried to define the Trigram index with COLLATE > pg_catalog."C" but the behavior did not change. I did vacuum and analyze > after creating each index. > > 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' in this example) where did you determine that pg_trgm should optimize like expressions? pg_trgm provides new operators that are used to index on string similarity... merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
