Re: Hypothetical suggestions for planner, indexing improvement

Tom,

> 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.

Yes. It would also make FKs something more than just an annoying (and slow)
constraint in PostgreSQL. And it would be a performance feature that most
other RDBMSs don't have ;-)

> 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.

EXISTS is more flexible than IN; how can you do a 3-column corellation on an
IN clause?

The reason that I mention EXISTS is because that's where the lack of
cross-column corellation is most dramatic; the planner seems to estimate a
flat 50% for EXISTS clauses regardless of the content.

--
Josh Berkus
Aglio Database Solutions
San Francisco