Re: Support run-time partition pruning for hash join

On Fri, Sep 6, 2024 at 9:22 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
> Maybe instead of inventing a very pessimistic part prune Hash Join, it
> might be better to make the above work without the LATERAL + OFFSET 0
> by creating the parameterized paths Seq Scan paths. That's going to be
> an immense help when the non-partitioned relation just has a small
> number of rows, which I think your costing favoured anyway.
>
> What do you think?

This approach seems promising. It reminds me of the discussion about
pushing join clauses into a seqscan [1]. But I think there are two
problems that we need to address to make it work.

* 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?

* 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.

[1] https://postgr.es/m/3478841.1724878067@sss.pgh.pa.us

Thanks
Richard