* 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@xxxxxxxxxxxxx> wrote:
ERR ORR <rd0002@xxxxxxxxx> writes:Are you sure it "should" use the index for that? That query doesn't
>> 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.
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.
FWIW, I think you do want the index to have the database's default
> Would it help to `ALTER DATABASE set lc_collate = 'C'`,supposing that is
> possible? (Oracle doesn't allow that iirc)
collation, otherwise it could only match LIKE clauses that explicitly
specify the same non-default collation.
regards, tom lane
