Skip site navigation (1) Skip section navigation (2)

Re: turn off caching for performance test

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
Cc: Willy-Bas Loos <willybas(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: turn off caching for performance test
Date: 2010-09-26 03:36:06
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
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
The Enterprise Postgres Company

In response to

pgsql-performance by date

Next:From: Robert HaasDate: 2010-09-26 04:02:27
Subject: Re: Is disableing nested_loops a bad idea ?
Previous:From: Tobias BroxDate: 2010-09-25 10:29:30
Subject: Re: Memory usage - indexes

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group