With Hash Join, it seems to me that the pruning must take place for every row that makes it into the hash table. There will be maybe cases where the unioned set of partitions simply yields every partition and all the work results in no savings. Pruning on a scalar value seems much more likely to be able to prune away unneeded Append/MergeAppend subnodes.
Yeah, you're right. If we have 'pt HashJoin t', for a subnode of 'pt' to be pruned, it needs every row of 't' to be able to prune that subnode. The situation may improve if we have more than 2-way hash joins, because the final surviving subnodes would be the intersection of matching subnodes in each Hash.
With parameterized nestloop I agree that it's more likely to be able to prune subnodes at rescan of Append/MergeAppend nodes based on scalar values.
Sometimes we may just not generate parameterized nestloop as final plan, such as when there are no indexes and no lateral references in the Append/MergeAppend node. In this case I think it would be great if we can still do some partition prunning. So I think this new 'join partition prunning mechanism' (maybe this is not a proper name) should be treated as a supplement to, not a substitute for, the current run-time partition prunning based on parameterized nestloop, and it is so implemented in the patch.
Perhaps there can be something adaptive in Hash Join which stops trying to prune when all partitions must be visited. On a quick glance of the patch, I don't see any code in ExecJoinPartitionPrune() which gives up trying to prune when the number of members in part_prune_result is equal to the prunable Append/MergeAppend subnodes.
Yeah, we can do that.
But run-time pruning already works for Nested Loops... I must be missing something here.
Here I mean nestloop with non-parameterized inner path. As I explained upthread, we need to have a Material node on the outer side for that to work, which seems not possible in real world.