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.