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