search

ossc-db / pg_hint_plan

Extension adding support for optimizer hints in PostgreSQL
Other
703 stars 103 forks source link

make installcheck fails on PostgreSQL 17 built with --with-llvm option #199

Closed shinyaaa closed 2 weeks ago

shinyaaa commented 2 weeks ago

make installcheck fails on PostgreSQL 17 (6b1f78d90b5f2475d968e16febee8f9d43730d63) built with --with-llvm option.

In previous releases, the test seemed to succeed even when using LLVM.

16- 17
no option passed passed
--with-llvm option passed failed

Error logs are below.

regression.out ``` # using postmaster on Unix socket, port 5417 ok 1 - init 3565 ms ok 2 - base_plan 20 ms ok 3 - pg_hint_plan 7729 ms ok 4 - ut-init 465 ms ok 5 - ut-A 294 ms not ok 6 - ut-S 585 ms not ok 7 - ut-J 597 ms ok 8 - ut-L 300 ms ok 9 - ut-G 96 ms not ok 10 - ut-R 417 ms ok 11 - ut-fdw 21 ms ok 12 - ut-W 1942 ms ok 13 - ut-T 17 ms ok 14 - ut-fini 14 ms ok 15 - plpgsql 44 ms ok 16 - hint_table 33 ms ok 17 - oldextversions 83 ms 1..17 # 3 of 17 tests failed. # The differences that caused some tests to fail can be viewed in the file "/home/shinya/pgsql/17/postgresql/contrib/pg_hint_plan/regression.diffs". # A copy of the test summary that you see above is saved in the file "/home/shinya/pgsql/17/postgresql/contrib/pg_hint_plan/regression.out". ```
regression.diffs ``` diff -U3 /home/shinya/pgsql/17/postgresql/contrib/pg_hint_plan/expected/ut-S.out /home/shinya/pgsql/17/postgresql/contrib/pg_hint_plan/results/ut-S.out --- /home/shinya/pgsql/17/postgresql/contrib/pg_hint_plan/expected/ut-S.out 2024-08-23 16:17:58.047311683 +0900 +++ /home/shinya/pgsql/17/postgresql/contrib/pg_hint_plan/results/ut-S.out 2024-08-23 17:43:41.903915128 +0900 @@ -4444,11 +4444,14 @@ duplication hint: error hint: - explain_filter -------------------------------------------------------- + explain_filter +------------------------------------------------------------------------------- Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx) Filter: (c1 = 100) -(2 rows) + JIT: + Functions: 2 + Options: Inlining true, Optimization true, Expressions true, Deforming true +(5 rows) -- No. S-3-5-2 SELECT explain_filter(' @@ -4463,11 +4466,14 @@ duplication hint: error hint: - explain_filter -------------------------------------------------------- + explain_filter +------------------------------------------------------------------------------- Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx) Filter: (c1 = 100) -(2 rows) + JIT: + Functions: 2 + Options: Inlining true, Optimization true, Expressions true, Deforming true +(5 rows) -- No. S-3-5-3 SELECT explain_filter(' @@ -4482,11 +4488,14 @@ duplication hint: error hint: - explain_filter -------------------------------------------------------- + explain_filter +------------------------------------------------------------------------------- Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx) Filter: (c1 = 100) -(2 rows) + JIT: + Functions: 4 + Options: Inlining true, Optimization true, Expressions true, Deforming true +(5 rows) -- No. S-3-5-4 SELECT explain_filter(' @@ -4564,11 +4573,14 @@ duplication hint: error hint: - explain_filter ------------------------------------------------------- + explain_filter +------------------------------------------------------------------------------- Seq Scan on t1 (cost={inf}..{inf} rows=1 width=xxx) Filter: (c1 = 1) -(2 rows) + JIT: + Functions: 2 + Options: Inlining true, Optimization true, Expressions true, Deforming true +(5 rows) ---- ---- No. S-3-6 query structure diff -U3 /home/shinya/pgsql/17/postgresql/contrib/pg_hint_plan/expected/ut-J.out /home/shinya/pgsql/17/postgresql/contrib/pg_hint_plan/results/ut-J.out --- /home/shinya/pgsql/17/postgresql/contrib/pg_hint_plan/expected/ut-J.out 2024-08-23 16:17:58.046311671 +0900 +++ /home/shinya/pgsql/17/postgresql/contrib/pg_hint_plan/results/ut-J.out 2024-08-23 17:43:42.504921957 +0900 @@ -835,7 +835,10 @@ Index Cond: (c1 = (InitPlan 1).col1) -> Seq Scan on t2 (cost=xxx..xxx rows=1 width=xxx) Filter: (c1 = (InitPlan 1).col1) -(11 rows) + JIT: + Functions: 11 + Options: Inlining true, Optimization true, Expressions true, Deforming true +(14 rows) -- -- There are cases where difference in the measured value and predicted value @@ -4680,14 +4683,17 @@ duplication hint: error hint: - explain_filter ----------------------------------------------------------------- + explain_filter +------------------------------------------------------------------------------- Hash Full Join (cost={inf}..{inf} rows=1000 width=xxx) Hash Cond: (t1.c1 = t2.c1) -> Seq Scan on t1 (cost=xxx..xxx rows=1000 width=xxx) -> Hash (cost=xxx..xxx rows=100 width=xxx) -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx) -(5 rows) + JIT: + Functions: 10 + Options: Inlining true, Optimization true, Expressions true, Deforming true +(8 rows) -- Memoize EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.val = t2.val and t2.id = t3.id; diff -U3 /home/shinya/pgsql/17/postgresql/contrib/pg_hint_plan/expected/ut-R.out /home/shinya/pgsql/17/postgresql/contrib/pg_hint_plan/results/ut-R.out --- /home/shinya/pgsql/17/postgresql/contrib/pg_hint_plan/expected/ut-R.out 2024-08-23 16:17:58.046311671 +0900 +++ /home/shinya/pgsql/17/postgresql/contrib/pg_hint_plan/results/ut-R.out 2024-08-23 17:43:43.338931434 +0900 @@ -2603,7 +2603,10 @@ Filter: (c1 <> (InitPlan 2).col1) -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx..xxx rows=1 width=xxx) Index Cond: (c1 = b1t1.c1) -(28 rows) + JIT: + Functions: 25 + Options: Inlining true, Optimization true, Expressions true, Deforming true +(31 rows) SELECT explain_filter(' /*+ @@ -2686,7 +2689,10 @@ Filter: (c1 <> (InitPlan 2).col1) -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx..xxx rows=1 width=xxx) Index Cond: (c1 = b1t1.c1) -(28 rows) + JIT: + Functions: 25 + Options: Inlining true, Optimization true, Expressions true, Deforming true +(31 rows) -- No. R-2-2-3 SELECT explain_filter(' @@ -2804,7 +2810,10 @@ Index Cond: (c1 = b1t3.c1) -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1 width=xxx) Index Cond: (c1 = bmt3.c1) -(58 rows) + JIT: + Functions: 65 + Options: Inlining true, Optimization true, Expressions true, Deforming true +(61 rows) SELECT explain_filter(' /*+ @@ -2947,7 +2956,10 @@ Index Cond: (c1 = b1t3.c1) -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1 width=xxx) Index Cond: (c1 = bmt3.c1) -(58 rows) + JIT: + Functions: 65 + Options: Inlining true, Optimization true, Expressions true, Deforming true +(61 rows) -- No. R-2-2-4 SELECT explain_filter(' @@ -3025,7 +3037,10 @@ Index Cond: (c1 = b1t3.c1) -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1 width=xxx) Index Cond: (c1 = bmt3.c1) -(34 rows) + JIT: + Functions: 31 + Options: Inlining true, Optimization true, Expressions true, Deforming true +(37 rows) SELECT explain_filter(' /*+ @@ -3116,7 +3131,10 @@ Index Cond: (c1 = b1t3.c1) -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1 width=xxx) Index Cond: (c1 = bmt3.c1) -(34 rows) + JIT: + Functions: 31 + Options: Inlining true, Optimization true, Expressions true, Deforming true +(37 rows) ---- ---- No. R-2-3 RULE or VIEW ```
michaelpq commented 2 weeks ago

We don't care much about JIT and just about the plan stability through the hints, so it would be better to just disable JIT in the tests where we get the diffs and forget about its existence. It is surprising that we don't have more of that, actually.

michaelpq commented 2 weeks ago

Yes, I have been able to reproduce it down to 17, and got the same diffs as you. It does not change the plans generated, just generates extra information for what has been jitted, so I've applied a workaround to disable JIT in these tests.