Re: Support run-time partition pruning for hash join

On Tue, Aug 29, 2023 at 6:41 PM Richard Guo <guofenglinux(at)gmail(dot)com> wrote:

> So it seems that the new costing logic is quite crude and tends to be
> very conservative, but it can help avoid the large overhead in the worst
> cases. I think this might be a good start to push this patch forward.
>
> Any thoughts or comments?
>

I rebased this patch over the latest master. Nothing changed except
that I revised the new added test case to make it more stable.

However, the cfbot indicates that there are test cases that fail on
FreeBSD [1] (no failure on other platforms). So I set up a FreeBSD-13
locally but just cannot reproduce the failure. I must be doing
something wrong. Can anyone give me some hints or suggestions?

FYI. The failure looks like:

explain (costs off)
select p2.a, p1.c from permtest_parent p1 inner join permtest_parent p2
on p1.a = p2.a and left(p1.c, 3) ~ 'a1$';
- QUERY PLAN
-----------------------------------------------------
- Hash Join
- Hash Cond: (p2.a = p1.a)
- -> Seq Scan on permtest_grandchild p2
- -> Hash
- -> Seq Scan on permtest_grandchild p1
- Filter: ("left"(c, 3) ~ 'a1$'::text)
-(6 rows)
-
+ERROR: unrecognized node type: 1130127496

[1]
https://api.cirrus-ci.com/v1/artifact/task/5334808075698176/testrun/build/testrun/regress/regress/regression.diffs

Thanks
Richard