Re: Support run-time partition pruning for hash join

On Mon, Aug 21, 2023 at 8:34 PM Andy Fan <zhihui.fan1213@gmail.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.