Message-ID: <430F8699.3030400@tweakers.net>
Date: Fri, 26 Aug 2005 23:16:09 +0200
From: Arjen van der Meijden <acmmailing@tweakers.net>
User-Agent: Mozilla Thunderbird 1.0RC1 (Windows/20041201)
MIME-Version: 1.0
To: Richard Huxton <dev@archonet.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Inefficient queryplan for query with intersectable
References: <430EF844.70905@tweakers.net> <430F1393.5040704@archonet.com>
In-Reply-To: <430F1393.5040704@archonet.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 26-8-2005 15:05, Richard Huxton wrote:
> Arjen van der Meijden wrote:
> 
>>
>> I left all the configuration-stuff to the defaults since changing 
>> values didn't seem to impact much. Apart from the buffers and 
>> effective cache, increasing those made the performance worse.
> 
> 
> I've not looked at the rest of your problem in detail, but using the 
> default configuration values is certainly not going to help things. In 
> particular effective_cache is supposed to tell PG how much memory your 
> OS is using to cache data.
> 
> Read this through and make sure your configuration settings are sane, 
> then it might be worthwhile looking in detail at this particular query.
>   http://www.powerpostgresql.com/PerfList

Thanks for the advice. But as said, I tried such things. Adjusting 
shared buffers to 5000, 10000 or 15000 made minor improvements.
But adjusting the effective_cache was indeed very dramatic, to make 
matters worse!
Changing the random_page_cost to 2.0 also made it choose another plan, 
but still not the fast plan.

The machine has 1GB of memory, so I tested for effective cache size 
10000, 20000, 40000, 60000 and 80000. The "600ms"-plan I'm talking about 
will not come up with an effective cache set to 60000 or above and for 
the lower values there was no improvement in performance over that 
already very fast plan.
As said, it chooses sequential scans or "the wrong index plans" over a 
perfectly good plan that is just not selected when the parameters are 
"too well tuned" or sequential scanning of the table is allowed.

So I'm still looking for a way to get it to use the fast plan, instead 
of the slower ones that appear when I tend to tune the database...