Re: An Idea for planner hints

On Wed, Aug 09, 2006 at 03:33:21PM -0400, Joshua Reich wrote:
> >(Not sure how we'd implement that, seeing that ANALYZE currently works
> >on one table at a time, but it's probably doable --- and it'd fix the
> >fundamental problem for correlation statistics, which is how not to try
> >to collect stats about an exponential number of combinations ...)
>
> An exponential number of combinations? Is it possible to use FK
> relationships to determine what tables & columns are likely to be used
> in future joins.

Sure, except FKs are the degenerate case. You know the target column is
unique and the source column only contains values in the target column,
so the ratio of number of rows is good.

No, the interesting stats are in other columns, which have no
explicitly declared relationship, except perhaps that they are both
foreign keys to another table.

Once you've got the basic infrastructure, you could make a tool that
would scan the queries and tables that look for column combinations
which are often joined and have an unusual correlation (unusually high
or unusually low).

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.