In-Reply-To: <23385219-5252-468A-BBC9-69516DA81C2A@blighty.com>
References: 
 <8C5B026B51B6854CBE88121DBF097A86C3A30D@ehost010-33.exch010.intermedia.net>
	<27828.1180055291@sss.pgh.pa.us>
	<20070525023922.GV4320@alvh.no-ip.org>
	<29662.1180061117@sss.pgh.pa.us>
	<46565086.2040705@commandprompt.com> <115.1180063568@sss.pgh.pa.us>
	<23385219-5252-468A-BBC9-69516DA81C2A@blighty.com>
Mime-Version: 1.0 (Apple Message framework v752.2)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <CB2207B5-1E40-4C9B-9ABF-49AB813BFD51@mitre.org>
Content-Transfer-Encoding: 7bit
From: "John D. Burger" <john@mitre.org>
Subject: Re: index vs. seq scan choice? 
Date: Fri, 25 May 2007 08:55:24 -0400
To: PostgreSQL General <pgsql-general@postgresql.org>

Steve Atkins wrote:

> Would it be possible to look at a much larger number of samples  
> during analyze,
> then look at the variation in those to generate a reasonable number of
> pg_statistic "samples" to represent our estimate of the actual  
> distribution?
> More datapoints for tables where the planner might benefit from it,  
> fewer
> where it wouldn't.

You could definitely try to measure the variance of the statistics  
(using, say, bootstrap resampling), and change the target 'til you  
got a "good" tradeoff between small sample size and adequate  
representation of the distribution.  Unfortunately, I think the  
definition of "good" depends strongly on the kinds of queries that  
get run.  Basically, you want the statistics target to be just big  
enough that more stats wouldn't change the plans for common queries.   
Remember, too, that this is not just one number, it'd be different  
for each column (perhaps zero for most).

I could imagine hillclimbing the stats targets by storing common  
queries and then replaying them, while varying the sample size.   
There was a discussion last year related to all of this, see:

   http://archives.postgresql.org/pgsql-general/2006-10/msg00526.php

- John D. Burger
   MITRE