Re: An Idea for planner hints

In article <i-5CD819(dot)08313809082006(at)news(dot)hub(dot)org>,
Perez <i(at)donotexist(dot)com> wrote:

> In article <14860(dot)1155090146(at)sss(dot)pgh(dot)pa(dot)us>,
> tgl(at)sss(dot)pgh(dot)pa(dot)us (Tom Lane) wrote:
>
> > Martijn van Oosterhout <kleptog(at)svana(dot)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