Subject: Re: Support run-time partition pruning for hash join

From: Alexander Lakhin <[email-masked]>
Date: Sat, 4 Nov 2023 13:00:00 +0300

Hello Richard,

02.11.2023 14:19, Richard Guo wrote:
>
> 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

I've managed to reproduce that failure on my Ubuntu with:
CPPFLAGS="-Og -DWRITE_READ_PARSE_PLAN_TREES -DCOPY_PARSE_PLAN_TREES" ./configure ... make check
...
  SELECT t1, t2 FROM prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
-                    QUERY PLAN
---------------------------------------------------
- Sort
-   Sort Key: t1.a, t2.b
-   ->  Hash Right Join
-         Hash Cond: (t2.b = t1.a)
-         ->  Append
-               ->  Seq Scan on prt2_p1 t2_1
-               ->  Seq Scan on prt2_p2 t2_2
-               ->  Seq Scan on prt2_p3 t2_3
-         ->  Hash
-               ->  Append
-                     ->  Seq Scan on prt1_p1 t1_1
-                           Filter: (b = 0)
-                     ->  Seq Scan on prt1_p2 t1_2
-                           Filter: (b = 0)
-                     ->  Seq Scan on prt1_p3 t1_3
-                           Filter: (b = 0)
-(16 rows)
-
+ERROR:  unrecognized node type: -1465804424
...

As far as I can see from https://cirrus-ci.com/task/6642692659085312,
the FreeBSD host has the following CPPFLAGS specified:
-DRELCACHE_FORCE_RELEASE
-DCOPY_PARSE_PLAN_TREES
-DWRITE_READ_PARSE_PLAN_TREES
-DRAW_EXPRESSION_COVERAGE_TEST
-DENFORCE_REGRESSION_TEST_NAME_RESTRICTIONS

Best regards,
Alexander

---

From: Richard Guo <[email-masked]>
Date: Mon, 6 Nov 2023 11:05:24 +0800

> ./configure ... make check

Wow, thank you so much.  You saved me a lot of time.  It turns out that
it was caused by me not making JoinPartitionPruneInfo a node.  The same
issue can also exist for JoinPartitionPruneCandidateInfo - if you
pprint(root) at some point you'll see 'could not dump unrecognized node
type' warning.

Fixed this issue in v4.

Thanks
Richard

---

From: Alexander Lakhin <[email-masked]>
Date: Mon, 6 Nov 2023 18:00:00 +0300

Hello Richard,

> Fixed this issue in v4.

Please look at a warning and an assertion failure triggered by the
following script:
set parallel_setup_cost = 0;
set parallel_tuple_cost = 0;
set min_parallel_table_scan_size = '1kB';

create table t1 (i int) partition by range (i);
create table t1_1 partition of t1 for values from (1) to (2);
create table t1_2 partition of t1 for values from (2) to (3);
insert into t1 values (1), (2);

create table t2(i int);
insert into t2 values (1), (2);
analyze t1, t2;

select * from t1 right join t2 on t1.i = t2.i;

2023-11-06 14:11:37.398 UTC|law|regression|6548f419.392cf5|WARNING: Join partition pruning $0 has not been performed yet.
TRAP: failed Assert("node->as_prune_state"), File: "nodeAppend.c", Line: 846, PID: 3747061

Best regards,
Alexander