On Tue, Aug 08, 2006 at 04:14:45PM -0400, Tom Lane wrote:
> Martijn van Oosterhout <kleptog@svana.org> writes:
> > ISTM theat the easiest way would be to introduce a sort of predicate
> > like so:
>
> > SELECT * FROM foo, bar WHERE pg_selectivity(foo.a = bar.a, 0.1);
>
> The one saving grace of Florian's proposal was that you could go hack
> the statistics *without* changing your queries. This throws that away
> again.
Well, that true. I was thinking of the easy way.
To run with something suggested in this thread, do you think it would
be more reasonable to be able to provide statistics information for
joins, which currently we have no grip on at all. Something like:
CREATE STATISTIC foo
ON table1 a, table2 b
WHERE a.x = b.x
AS SELECTIVITY < 0.1;
The idea being that if the planner see those tables being joined on
those fields, that it will do its guess on the number of rows, but caps
the selectivity to less than 0.1.
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).
2. That on average there is a N:1 ratio of results between a.x and b.x.
So if you take a value of a.x and look it up in b, on average you'll
get N results. This can be a valid measurement for any two columns, not
just ones related by a foreign key.
For either of those, selectivity is the wrong variable, but I'll be
damned if I can think of a better way of expressing it...
The interesting case would be joins across a number of tables and be
able to tell the planner information about that, but that's an even
harder problem.
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.
