Re: simple case using index on windows but not on linux

simon godden wrote:
> (Sending again because I forgot to reply to all)
>
> On 10/4/06, Heikki Linnakangas <heikki(at)enterprisedb(dot)com> wrote:
>> You can increase the max shared memory size if you have root access. See
>>
>> http://www.postgresql.org/docs/8.1/interactive/kernel-resources.html#SYSVIPC-PARAMETERS
>>
>>
>> Scroll down for Linux-specific instructions.
>
> Thanks for the link.
>
> Are you saying that the shared memory size is the issue here? Please
> can you explain how it would cause a seq scan rather than an index
> scan.
>
> I would like to understand the issue before making changes.

It *might* be shared-memory settings. It's almost certainly something to
do with setup. If you have the same data and the same query and can
reliably produce different results then something else must be different.

If you look at the explain output from both, PG knows the seq-scan is
going to be expensive (cost=20835) so the Linux box either
1. Doesn't have the index (and you say it does, so it's not this).
2. Thinks the index will be even more expensive.
3. Can't use the index at all.

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.

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

--
Richard Huxton
Archonet Ltd