Re: Question on Trigram GIST indexes

On Tue, Jan 22, 2013 at 8:07 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> 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...

oops -- heh -- I guess you *can* do that (after further documentation
review). hm...it works for me...

merlin