Postgres Pro
Downloads
Home   >   mailing lists

Re: Fwd: Question on Trigram GIST indexes - Mailing list pgsql-general

From ERR ORR
Subject Re: Fwd: Question on Trigram GIST indexes
Date
Msg-id CALtFtE+_LMm1jwCCv8j4GSR28OzOARa4kgDi-mXeH2syTvqq4w@mail.gmail.com
Whole thread Raw
In response to Re: Fwd: Question on Trigram GIST indexes  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
Tree view
* I think it "should" use that index based on trying to follow that exercise.
* The part about changing the collation was an idea in the course of trying out different things.
* enable_seqscan is off, and the sharedmem and temp_buffers are set so high that most things happen in RAM.

I wonder what it that the other gentleman, Merlin, found out in the documentation and if he would share that.

I've also tried this on another table I have, with and without other indexes, but no success :-(

Wondering ...


On 23 January 2013 04:05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
ERR ORR <rd0002@gmail.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

pgsql-general by date:

Previous
From: "Gauthier, Dave"
Date:
Subject: Re: DB alias ?
Next
From: "Gauthier, Dave"
Date:
Subject: Re: DB alias ?