* 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 <t...@sss.pgh.pa.us> wrote: > ERR ORR <rd0...@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 >
