It would be good to see some performance comparisons of the worst case to see how much overhead the pruning code adds to Hash Join. It may well be that we need to consider two Hash Join paths, one with and one without run-time pruning. It's pretty difficult to meaningfully cost, as I already mentioned, however.
I performed some performance comparisons of the worst case with two tables as below:
1. The partitioned table has 1000 children, and 100,000 tuples in total.
2. The other table is designed that a) its tuples occupy every partition of the partitioned table so that no partitions can be pruned during execution, b) tuples belong to the same partition are placed together so that we need to scan all its tuples before we could know that no pruning would happen and we could stop trying to prune, c) the tuples are unique on the hash key so as to minimize the cost of hash probe, so that we can highlight the impact of the pruning codes.
Here is the execution time (ms) I get with different sizes of the other table.
So the overhead the pruning code adds to Hash Join is too large to be accepted :(. I think we need to solve this problem first before we can make this new partition pruning mechanism some useful in practice, but how? Some thoughts currently in my mind include
1) we try our best to estimate the cost of this partition pruning when creating hash join paths, and decide based on the cost whether to use it or not. But this does not seem to be an easy task.
2) we use some heuristics when executing hash join, such as when we notice that a $threshold percentage of the partitions must be visited we just abort the pruning and assume that no partitions can be pruned.