On 10/4/06, Richard Huxton <dev@archonet.com> wrote:
>
> Issue "set enable_seqscan=false" and then run your explain analyse. If
> your query uses the index, what is the estimated cost? If the estimated
> cost is larger than a seq-scan that would indicate your configuration
> settings are badly out-of-range.
I did that and it still used seq-scan.
>
> If the index isn't used, then we have problem #3. I think this is what
> you are actually seeing. Your locale is something other than "C" and PG
> doesn't know how to use like with indexes. Read up on operator classes
> or change your locale.
> http://www.postgresql.org/docs/8.1/static/indexes-opclass.html
>
Aha - that sounds like it - this is the output from locale
LANG=en_US.UTF-8
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=
I guess it cannot determine the collating sequence?
I'm not too familiar with unix locale issues - does this output match
your problem description?
Can you explain how to change my locale to 'C'? (I'm quite happy for
you to tell me to RTFM, as I know this is not a linux user mailing
list :)
--
Simon Godden