Re: Fwd: Question on Trigram GIST indexes

ERR ORR <rd0002(at)gmail(dot)com> writes:
>> 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.

Are you sure it "should" use the index for that? That query doesn't
look very selective to me --- it might well be deciding that a seqscan
is cheaper. You could try forcing the issue with enable_seqscan = off
to see if the query is really unable to match the index, or it just
doesn't like the cost estimate.

> Would it help to `ALTER DATABASE set lc_collate = 'C'`,supposing that is
> possible? (Oracle doesn't allow that iirc)

FWIW, I think you do want the index to have the database's default
collation, otherwise it could only match LIKE clauses that explicitly
specify the same non-default collation.

regards, tom lane