From: Richard Huxton <dev@archonet.com>
To: simon godden <sgodden@gmail.com>
Cc: Heikki Linnakangas <heikki@enterprisedb.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: simple case using index on windows but not on linux
Date: Wed, 04 Oct 2006 10:36:05 +0100
Message-ID: <45238085.3000407@archonet.com> (raw)
In-Reply-To: <168519350610040219n648a282hf159ff272e0f672a@mail.gmail.com>
References: <168519350610040048m7c4031e3t1a24b3fd4d9265a3@mail.gmail.com>
<45237AD0.50604@enterprisedb.com>
<168519350610040219n648a282hf159ff272e0f672a@mail.gmail.com>
simon godden wrote:
> (Sending again because I forgot to reply to all)
>
> On 10/4/06, Heikki Linnakangas <heikki@enterprisedb.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dev@archonet.com, sgodden@gmail.com, heikki@enterprisedb.com
Subject: Re: simple case using index on windows but not on linux
In-Reply-To: <45238085.3000407@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox