Message-ID: <45238085.3000407@archonet.com>
Date: Wed, 04 Oct 2006 10:36:05 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 1.5.0.7 (X11/20060909)
MIME-Version: 1.0
To: simon godden <sgodden@gmail.com>
Cc: Heikki Linnakangas <heikki@enterprisedb.com>,
	pgsql-performance@postgresql.org
Subject: Re: simple case using index on windows but not on linux
References: <168519350610040048m7c4031e3t1a24b3fd4d9265a3@mail.gmail.com>	
	<45237AD0.50604@enterprisedb.com>
	<168519350610040219n648a282hf159ff272e0f672a@mail.gmail.com>
In-Reply-To: <168519350610040219n648a282hf159ff272e0f672a@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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