Message-ID: <4E811441.6060604@ringerc.id.au>
Date: Tue, 27 Sep 2011 08:09:37 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
	rv:6.0.2) Gecko/20110902 Thunderbird/6.0.2
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: Craig James <craig_james@emolecules.com>, pgsql-performance@postgresql.org
Subject: Re: Performance Anomaly with "col in (A,B)" vs. "col =
	A OR col = B" ver. 9.0.3
References: 
 <CAPcyiQ1U1k=mcpKT2ecy02Vr=2Eqpee3tnQLpfvW-aViSp=Big@mail.gmail.com>
	<9594.1317056871@sss.pgh.pa.us> <4E80B3F9.8070001@emolecules.com>
	<10130.1317058511@sss.pgh.pa.us>
In-Reply-To: <10130.1317058511@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 27/09/2011 1:35 AM, Tom Lane wrote:
> Craig James<craig_james@emolecules.com>  writes:
>> On 9/26/11 10:07 AM, Tom Lane wrote:
>>> Cranking up the statistics target for the hts_code_id column (and re-ANALYZEing) ought to fix it. If all your tables are this large you might want to just increase default_statistics_target across the board. regards, tom lane
>> This is common advice in this forum ....  but what's the down side to increasing statistics?  With so many questions coming to this forum that are due to insufficient statistics, why not just increase the default_statistics_target?  I assume there is a down side, but I've never seen it discussed.  Does it increase planning time?  Analyze time?  Take lots of space?
> Yes, yes, and yes.  We already did crank up the default
> default_statistics_target once (in 8.4), so I'm hesitant to do it again.

This has me wondering about putting together a maintenance/analysis tool 
that generates and captures stats from several ANALYZE runs and compares 
them to see if they're reasonably consistent. It then re-runs with 
higher targets as a one-off, again to see if the stats agree, before 
restoring the targets to defaults. The tool could crunch comparisons of 
the resulting stats and warn about tables or columns where the default 
stats targets aren't sufficient.

In the long run this might even be something it'd be good to have Pg do 
automatically behind the scenes (like autovacuum) - auto-raise stats 
targets where repeat samplings are inconsistent.

Thoughts? Is this reasonable to explore, or a totally bogus idea? I'll 
see if I can have a play if there's any point to trying it out.

--
Craig Ringer