Re: Question on Trigram GIST indexes

On Sat, Jan 5, 2013 at 12:20 PM, ERR ORR <rd0002(at)gmail(dot)com> 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