Re: An Idea for planner hints

On Mon, Aug 14, 2006 at 11:41:29PM +0300, Hannu Krosing wrote:
> ??hel kenal p??eval, E, 2006-08-14 kell 18:21, kirjutas Peter Eisentraut:
> > Perez wrote:
> > > I thought, from watching the list for a while, that the planner
> > > statistics needed were known but that how to gather the statistics
> > > was not?
> >
> > I think over the course of the discussion we have figured out that we
> > would like to have cross-column correlation statistics. The precise
> > mathematical incarnation hasn't been determined yet, as far as I can
> > see. Collecting the statistics thereafter isn't that hard, but there
> > needs to be a way to not collect an exponential volume of statistics on
> > all column combinations.
>
> I understood that the proposal was to collect only the stats where
> needed (determined by user/dba) and use some rule-of-thumb values if no
> collected stats were available.

Yeah, unless someone comes up with some kind of 'magic', I think trying
to handle every cross-column possibility is a non-starter. IIRC, that
argument is what's stalled cross-column stats every time in the past. It
makes a lot more sense to allow defining what combinations of columns we
need stats for.

After that's done, it'd be easy to then write a script that will tell
the database to collect stats on all multi-column indexes, RI, etc. Down
the road, the planner could even be made to log (in a machine-readable
format) every time it needs cross-column stats, and that data could be
used to add stats that are needed.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461