Re: An Idea for planner hints

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