On Tue, Jan 22, 2013 at 8:07 AM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote: > 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... oops -- heh -- I guess you *can* do that (after further documentation review). hm...it works for me... merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
