Re: Hypothetical suggestions for planner, indexing improvement

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> THE IDEA: The planner should keep statistics on the correlation of foreign
> keys and apply them to the expected row counts for EXISTS clause limitations,
> and possibly for other query types as well.

It's a thought. Keeping complete cross-column correlation stats (for
every combination of columns in the DB) is obviously out of the
question. If you're gonna do it you need a heuristic to tell you which
combinations of columns are worth keeping track of --- and foreign-key
relationships seem like a reasonable guide to the interesting
combinations.

I'm not sure about the long-term usefulness of optimizing EXISTS per se.
Seems to me that a lot of the present uses of EXISTS are workarounds
for Postgres' historic mistreatment of IN ... which we've attacked more
directly for 7.4. But cross-column correlations are certainly useful
for estimating join sizes in general.

regards, tom lane