On Tue, Nov 21, 2017 at 7:29 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> Hash joins are a place where we could have a smoother cost function
> than we do.
Yes, it definitely is.
> When we run out of memory, instead of switching from
> (say) a single batch to two batches, switch to 64 batches, but
> initially keep 63 of them in memory and only write the very last one
> to disk. Every time we again run out of memory, dump another batch to
> disk. If we end up dumping more than half or so of the batches to
> disk, switch to an even larger number of batches to make it even more
> fine-grained.
That could work.
> That having been said, I think the place where our plans most commonly
> go wrong is where we incorrectly estimate the number of tuples by
> multiple orders of magnitude - 100x is common, 1000x is common, a
> million x is not uncommon, even a billion x is not unheard-of. And I
> don't think there's any way to make a hash join happy if it thinks
> it's going to need 1 batch and it ends up needing a million batches.
What about dynamic role reversal? That could make a big difference.
> At that, even if the cost function is very smooth, you've moved so far
> along the curve that you're probably not in a good place. So, while I
> think that smoothing out the cost functions is a good idea, I think we
> also need to consider what more can be done to improve the estimates -
> and especially to avoid estimates that are off by huge multiples.
I agree that it would be enormously valuable if we could make
estimates much better, so I think that I understand why you emphasize
it. But, I don't think that there are any good ideas for improving
join selectivity that don't involve expert DBA knowledge, or
novel/risky techniques for feedback to the system about column
redundancy/correlation, etc. These do not seem like scalable
approaches, and so they don't particularly appeal to me as projects.
I'd be happy to be shown to be wrong about this.
OTOH, techniques like dynamic role reversal, for when there are many
batches and it's faster to flip the outer and inner side do seem
promising. It's probably possible to come up with a more or less
unambiguous improvement, without layering complexity. I suspect that
this technique is widely implemented, and will cut down on cases
leading to terrible performance to a significant degree. I should try
to talk Thomas into working on it.
--
Peter Geoghegan
