From: Josh Berkus <josh@agliodbs.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Claudio Freire <klaussfreire@gmail.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Shouldn't we have a way to avoid "risky" plans?
Date: Tue, 19 Apr 2011 18:50:40 -0700
Message-ID: <4DAE3BF0.2060806@agliodbs.com> (raw)
In-Reply-To: <BANLkTimcUqk4UQEQGpocGsRoQTk-S+R3mw@mail.gmail.com>
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: josh@agliodbs.com, robertmhaas@gmail.com, tgl@sss.pgh.pa.us, klaussfreire@gmail.com
Subject: Re: Shouldn't we have a way to avoid "risky" plans?
In-Reply-To: <4DAE3BF0.2060806@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox