From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: Greg Stark <gsstark@mit.edu>
Subject: Re: More thoughts about planner's cost estimates
Date: Thu, 1 Jun 2006 11:32:21 -0700
User-Agent: KMail/1.8
Cc: Tom Lane <tgl@sss.pgh.pa.us>,
 pgsql-hackers@postgresql.org
References: <14821.1149104747@sss.pgh.pa.us> <447F23A9.1090503@agliodbs.com>
	<8764jkhgwb.fsf@stark.xeocode.com>
In-Reply-To: <8764jkhgwb.fsf@stark.xeocode.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200606011132.22066.josh@agliodbs.com>

Greg,

> I'm convinced these two are more connected than you believe.

Actually, I think they are inseparable.

> I might be interested in implementing that algorithm that was posted a
> while back that involved generating good unbiased samples of discrete
> values. The algorithm was quite clever and well described and paper
> claimed impressively good results.
>
> However it will only make sense if people are willing to accept that
> analyze will need a full table scan -- at least for tables where the DBA
> knows that good n_distinct estimates are necessary.

What about block-based sampling?   Sampling 1 in 20 disk pages, rather than 
1 in 20 rows, should require siginificantly less scanning, and yet give us 
a large enough sample for reasonable accuracy.

> > 3. We don't have any method to analyze inter-column correlation within
> > a table;
> >
> > 4. We don't keep statistics on foriegn key correlation;
>
> Gosh these would be nice but they sound like hard problems. Has anybody
> even made any headway in brainstorming how to tackle them?

There's no time like the present!

Actually, these  both seem like fairly straightforward problems 
storage-wise.  The issue is deriving the statistics, for tables with many 
columns or FKs.  

> > 5. random_page_cost (as previously discussed) is actually a funciton
> > of relatively immutable hardware statistics, and as such should not
> > need to exist as a GUC once the cost model is fixed.
>
> I don't think that's true at all. Not all hardware is the same.
>
> Certainly the need to twiddle this GUC should be drastically reduced if
> the cache effects are modelled properly and the only excuses left are
> legitimate hardware differences.

OK ... but still, it should become a "little knob" rather than the "big 
knob" it is currently.


-- 
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco