Message-ID: <4DAE3BF0.2060806@agliodbs.com>
Date: Tue, 19 Apr 2011 18:50:40 -0700
From: Josh Berkus <josh@agliodbs.com>
Organization: PostgreSQL Experts Inc.
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.5; en-US;
	rv:1.9.2.15) Gecko/20110303 Thunderbird/3.1.9
MIME-Version: 1.0
To: Robert Haas <robertmhaas@gmail.com>
CC: Tom Lane <tgl@sss.pgh.pa.us>, Claudio Freire <klaussfreire@gmail.com>,
	postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Shouldn't we have a way to avoid "risky" plans?
References: <4D8A29F2.9080105@agliodbs.com>
	<AANLkTi=GAz7gFBCoXQeRDN3PUA0fXxRyP_=DxS4Y1tJU@mail.gmail.com>
	<4D8A581D.5080704@agliodbs.com>
	<AANLkTi=RXUSrCxqz3=BkAiixuenu2gf-K+W15Wz4McXH@mail.gmail.com>
	<4286.1300914004@sss.pgh.pa.us>	<4D8A8AB8.7040401@agliodbs.com>
	<3210.1301063084@sss.pgh.pa.us>
	<BANLkTimcUqk4UQEQGpocGsRoQTk-S+R3mw@mail.gmail.com>
In-Reply-To: <BANLkTimcUqk4UQEQGpocGsRoQTk-S+R3mw@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

On 4/19/11 7:29 AM, Robert Haas wrote:
> Another thought is that we might want to consider reducing
> autovacuum_analyze_scale_factor.  The root of the original problem
> seems to be that the table had some data churn but not enough to cause
> an ANALYZE.  Now, if the data churn is random, auto-analyzing after
> 10% churn might be reasonable, but a lot of data churn is non-random,
> and ANALYZE is fairly cheap.

I wouldn't reduce the defaults for PostgreSQL; this is something you do
on specific tables.

For example, on very large tables I've been known to set
analyze_scale_factor to 0 and analyze_threshold to 5000.

And don't assume that analyzing is always cheap.  If you have an 800GB
table, most of which is very cold data, and have statistics set to 5000
for some columns, accessing many of the older blocks could take a while.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com