Re: Support run-time partition pruning for hash join

On Fri, 6 Sept 2024 at 19:19, Richard Guo <guofenglinux(at)gmail(dot)com> wrote:
> * Currently, the costing code does not take run-time pruning into
> consideration. How should we calculate the costs of the parameterized
> paths on partitioned tables?

Couldn't we assume total_cost = total_cost / n_apppend_children for
equality conditions and do something with DEFAULT_INEQ_SEL and
DEFAULT_RANGE_INEQ_SEL for more complex cases. I understand we
probably need to do something about this to have the planner have any
chance of actually choose these Paths, so hacking something in there
to test the idea is sound before going to the trouble of refining the
cost model seems like a good idea.

> * This approach generates additional paths at the scan level, which
> may not be easily compared with regular scan paths. As a result, we
> might need to retain these paths at every level of the join tree. I'm
> afraid this could lead to a significant increase in planning time in
> some cases. We need to find a way to avoid regressions in planning
> time.

How about just creating these Paths for partitioned tables (and
partitions) when there's an EquivalenceClass containing multiple
relids on the partition key? I think those are about the only cases
that could benefit, so I think it makes sense to restrict making the
additional Paths for that case.

David