Re: Support run-time partition pruning for hash join

On Mon, Aug 21, 2023 at 8:34 PM Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> wrote:

> This feature looks good, but is it possible to know if we can prune
> any subnodes before we pay the extra effort (building the Hash
> table, for each row... stuff)?
>

It might be possible if we take the partition prunning into
consideration when estimating costs. But it seems not easy to calculate
the costs accurately.

> Maybe at least, if we have found no subnodes can be skipped
> during the hashing, we can stop doing such work anymore.
>

Yeah, this is what we can do.

> In my current knowledge, we have to build the inner table first for this
> optimization? so hash join and sort merge should be OK, but nestloop
> should
> be impossible unless I missed something.
>

For nestloop and mergejoin, we'd always execute the outer side first.
So the Append/MergeAppend nodes need to be on the inner side for the
join partition prunning to take effect. For a mergejoin that will
explicitly sort the outer side, the sort node would process all the
outer rows before scanning the inner side, so we can do the join
partition prunning with that. For a nestloop, if we have a Material
node on the outer side, we can do that too, but I wonder if we'd have
such a plan in real world, because we only add Material to the inner
side of nestloop.

Thanks
Richard