Re: turn off caching for performance test

On Fri, Aug 27, 2010 at 1:57 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
> On Thu, Aug 26, 2010 at 4:32 AM, Willy-Bas Loos <willybas(at)gmail(dot)com> wrote:
>> Hi,
>>
>> I have a colleague that is convinced that the website is faster if
>> enable_seqscan is turned OFF.
>> I'm convinced of the opposite (better to leave it ON), but i would like to
>> show it, prove it to him.
>
> Stop, you're both doing it wrong.  The issue isn't whether or not
> turning off seq scans will make a few things faster here and there,
> it's why is the query planner choosing sequential scans when it should
> be choosing index scans.
>
> So, what are your non-default settings in postgresql.conf?
> Have you increased effective_cache_size yet?
> Lowered random_page_cost?
> Raised default stats target and re-analyzed?
>
> Have you been looking at the problem queries with explain analyze?
> What does it have to say about the planners choices?

[a bit behind on my email]

This was exactly my thought on first reading this post. If the
indexes are faster and PG thinks they are slower, it's a good bet that
there are some parameters that need tuning. Specifically,
effective_cache_size may be too low, and random_page_cost and
seq_page_cost are almost certainly too high.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company