>> 1. When the hash is not expected to spill to disk, it preserves the
>> pathkeys of the outer side of the join. If the optimizer were allowed
>> to assume that, it could produce significantly more efficient query
>> plans in some cases.
>
> This is definitely possible, but you will have to dynamically modify the
> execution path if the hash join ends up to be more than one batch.
Yeah, this item seems to be another case where having a "conditional"
branch in the plan would be valuable. What's interesting is that it's
branching based on whether the hash has spilled into batches rather
than whether the number of rows is greater or less than some breakeven
point. It looks like these branche nodes are going to need to know
more than just the generic plan parameters. They're going to need to
know about specifics like "sort has spilled to disk" or "hash has
spilled into batches" etc.
I like the idea of coalescing hash tables. I'm not sure the order in
which the planner decides on things is conducive to being able to make
good decisions about it though. Even if we did it afterwards without
adjusting the planner it might still be worthwhile though.
Incidentally a similar optimization is possible for materialize or
even sorts. They may not come up nearly so often since you would
normally want to go around adding indexes if you're repeatedly sorting
on the same columns. Biut it might not be any harder to get them all
in one swoop.
--
greg