Re: An Idea for planner hints

In article <20060813180053(dot)GP27928(at)pervasive(dot)com>,
jnasby(at)pervasive(dot)com ("Jim C. Nasby") wrote:

> On Wed, Aug 09, 2006 at 08:31:42AM -0400, Perez wrote:
> > Every once in a while people talk about collecting better statistics,
> > correlating multi-column correlations etc. But there never seems to be
> > a way to collect that data/statistics.
> >
> > Would it be possible to determine the additional statistics the planner
> > needs, modify the statistics table to have them and document how to
> > insert data there? We wouldn't have a good automated way to determine
> > the information but a properly educated DBA could tweak things until
> > they are satisfied.
> >
> > At worse if this new information is unpopulated then things would be as
> > they are now. But if a human can insert the right information then some
> > control over the planner would be possible.
> >
> > Is this a viable idea? Would this satisfy those that need to control
> > the planner immediately without code changes?
>
> Sure, it's a Simple Matter of Code.
>
> The real issue is figuring out what to do with these stats. I think all
> the estimator fucntions could use improvement, but no one's taken that
> on yet.

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?

For example, there is the discussion around multi-column correlation.
I got the impression that we (you all <grin>) knew what to do with the
stats but that there was no reliable way to get them.

So, the situation is that we need better stats, but we don't know how to
collect them AND we don't know what they are either? If we did know
what to do then my idea and SMC would prevail?

If that's the case then it sounds to me like we should figure out the
statistics we wish we had that the planner could work with. Something
for the 8.5 timeframe I guess :-)

-arturo