Re: Make transformAExprIn() return a flattened bool expression directly - Mailing list pgsql-hackers

> On Apr 24, 2026, at 14:06, cca5507 <cca5507@qq.com> wrote:
>
> Hi,
>
> Now transformAExprIn() returns a bool expression tree. Attach a patch
> to make it return a flattened bool expression directly without extra cost.
> This can reduce some work of the planner.
>
> --
> Regards,
> ChangAo Chen
> <v1-0001-Make-transformAExprIn-return-a-flattened-bool-exp.patch>

Hi ChangAo,

Thanks for the patch. In the first impression, this patch helps. It changes a deep-tree OR, like (Part1 OR (Part2 OR
Part3)), to a flat OR list, like OR [Part1, Part2, Part3]. 

I tried a SQL: select t1.* from t1, t2, t3 where t1.id in (1, 2, t2.id, t3.id, 3);

From the dumped query tree, I can see the difference clearly:

Before patch:
```
          :quals
             {BOOLEXPR
             :boolop or
             :args (
                {BOOLEXPR
                :boolop or
                :args (
                   {SCALARARRAYOPEXPR
                   :opno 96
                   :opfuncid 65
                   :useOr true
                   :args (
                      {VAR
                      :varno 1
                      }
                      {ARRAYEXPR
                      :array_typeid 1007
                      :array_collid 0
                      :element_typeid 23
                      :elements (
                         {CONST
                         :consttype 23
                         :constvalue 4 [ 1 0 0 0 0 0 0 0 ]
                         }
                         {CONST
                         :consttype 23
                         :constvalue 4 [ 2 0 0 0 0 0 0 0 ]
                         }
                         {CONST
                         :consttype 23
                         :constvalue 4 [ 3 0 0 0 0 0 0 0 ]
                         }
                      )
                      }
                   )
                   :location 40
                   }
                   {OPEXPR
                   :opno 96
                   :opfuncid 65
                   :opresulttype 16
                   :args (
                      {VAR
                      :varno 1
                      }
                      {VAR
                      :varno 2
                      :varattno 1
                      }
                   )
                   :location 40
                   }
                )
                :location 40
                }
                {OPEXPR
                :opno 96
                :opfuncid 65
                :opresulttype 16
                :args (
                   {VAR
                   :varno 1
                   }
                   {VAR
                   :varno 3
                   }
                )
                :location 40
                }
             )
             :location 40
             }
          }
```

After the patch:
```
          :quals
             {BOOLEXPR
             :boolop or
             :args (
                {SCALARARRAYOPEXPR
                :opno 96
                :opfuncid 65
                :args (
                   {VAR
                   :varno 1
                   }
                   {ARRAYEXPR
                   :array_typeid 1007
                   :array_collid 0
                   :element_typeid 23
                   :elements (
                      {CONST
                      :consttype 23
                      :constvalue 4 [ 1 0 0 0 0 0 0 0 ]
                      }
                      {CONST
                      :consttype 23
                      :constvalue 4 [ 2 0 0 0 0 0 0 0 ]
                      }
                      {CONST
                      :consttype 23
                      :constvalue 4 [ 3 0 0 0 0 0 0 0 ]
                      }
                   )
                   }
                )
                :location 40
                }
                {OPEXPR
                :opno 96
                :opfuncid 65
                :args (
                   {VAR
                   :varno 1
                   }
                   {VAR
                   :varno 2
                   }
                )
                :location 40
                }
                {OPEXPR
                :opno 96
                :opfuncid 65
                :args (
                   {VAR
                   :varno 1
                   }
                   {VAR
                   :varno 3
                   }
                )
                :location 40
                }
             )
             :location 40
             }
          }

```

After the patch, the qual tree is flat and shorter.

However, the final execution plan is the same before and after patching, which shows the planner has been smart enough:
```
evantest=# explain select t1.* from t1, t2, t3 where t1.id in (1, 2, t2.id, t3.id, 3);
                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..352435619.25 rows=411638852 width=4)
   Join Filter: ((t1.id = ANY ('{1,2,3}'::integer[])) OR (t1.id = t2.id) OR (t1.id = t3.id))
   ->  Nested Loop  (cost=0.00..81358.62 rows=6502500 width=8)
         ->  Seq Scan on t1  (cost=0.00..35.50 rows=2550 width=4)
         ->  Materialize  (cost=0.00..48.25 rows=2550 width=4)
               ->  Seq Scan on t2  (cost=0.00..35.50 rows=2550 width=4)
   ->  Materialize  (cost=0.00..48.25 rows=2550 width=4)
         ->  Seq Scan on t3  (cost=0.00..35.50 rows=2550 width=4)
(8 rows)
```

I am not an expert on planner, but I guess, in general, processing a flat OR list is cheaper than dealing with a tree.
So,I still agree this is an improvement for v20 unless I miss some regression case. You may add this patch to the CF
fortracking. 

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/