Re: Fwd: Question on Trigram GIST indexes

* 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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> 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
>