Re: Support run-time partition pruning for hash join - Mailing list pgsql-hackers

From Alexander Lakhin
Subject Re: Support run-time partition pruning for hash join
Date
Msg-id 600a153e-191b-952d-7090-cad5097b798f@gmail.com
Whole thread Raw
In response to Re: Support run-time partition pruning for hash join  (Richard Guo <guofenglinux@gmail.com>)
Responses Re: Support run-time partition pruning for hash join
List pgsql-hackers
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

pgsql-hackers by date:

Previous
From: Dilip Kumar
Date:
Subject: Re: Parallel Bitmap Heap Scan reports per-worker stats in EXPLAIN ANALYZE
Next
From: Vik Fearing
Date:
Subject: Re: [PATCH] Add XMLText function (SQL/XML X038)