Re: An Idea for planner hints

In article <i-5CD819.08313809082006@news.hub.org>,Perez <i@donotexist.com> wrote:

> In article <14860.1155090146@sss.pgh.pa.us>,
>  tgl@sss.pgh.pa.us (Tom Lane) wrote:
> 
> > Martijn van Oosterhout <kleptog@svana.org> writes:
> > > My main problem is that selectivity is the wrong measurement. What
> > > users really want to be able to communicate is:
> > 
> > > 1. If you join tables a and b on x, the number of resulting rows will be
> > > the number of roows selected from b (since b.x id a foreign key
> > > referencing a.x).
> > 
> > FWIW, I believe the planner already gets that case right, because a.x
> > will be unique and it should know that.  (Maybe not if the FK is across
> > a multi-column key, but in principle it should get it right.)
> > 
> > I agree though that meta-knowledge like this is important, and that
> > standard SQL frequently doesn't provide any adequate way to declare it.
> > 
> >             regards, tom lane
> 
> 
> 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?
> 
> -arturo

I didn't see any response to this idea so I thought I'd try again with a 
real email.

-arturo