Thread
-
Make transformAExprIn() return a flattened bool expression directly
cca5507 <cca5507@qq.com> — 2026-04-24T06:06:24Z
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
-
Re: Make transformAExprIn() return a flattened bool expression directly
Chao Li <li.evan.chao@gmail.com> — 2026-04-30T08:42:37Z
> 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 Part 3)), 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 for tracking. Best regards, -- Chao Li (Evan) HighGo Software Co., Ltd. https://www.highgo.com/ -
Re: Make transformAExprIn() return a flattened bool expression directly
cca5507 <cca5507@qq.com> — 2026-04-30T10:16:35Z
Hi Chao, Thanks for your comments. I have created a CF entry: https://commitfest.postgresql.org/patch/6723/ -- Regards, ChangAo Chen
-
Re: Make transformAExprIn() return a flattened bool expression directly
ZizhuanLiu X-MAN <44973863@qq.com> — 2026-05-31T11:39:06Z
>From: cca5507 <cca5507@qq.com> >Date: 2026-04-30 18:16 >To: Chao Li <li.evan.chao@gmail.com> >Cc: pgsql-hackers <pgsql-hackers@lists.postgresql.org> >Subject: Re: Make transformAExprIn() return a flattened bool expression directly > > > Hi Chao, > >Thanks for your comments. I have created a CF entry: > >https://commitfest.postgresql.org/patch/6723/ > >-- >Regards, >ChangAo Chen Hi, ChangAo, Chao, Hackers, `` Thanks a lot for the suggestion to avoid nested structures in transformAExprIn(). I also agree that handling this kind of simplification during parsing is cleaner and more appropriate, even though the planner can achieve a similar effect later via preprocess_qual_conditions(). `` I further refined and polished this implementation based on v1 patch. I intentionally keep all the core logic inside the existing foreach(l, rexprs) loop. This way better preserves the integrity of the function, minimizes code intrusion, and makes the overall logic easier to follow. I put careful thought into this implementation, and more details are documented in the code comments. `` I have updated the patch accordingly and prepared corresponding test cases to cover the modified scenarios, ensuring the correctness and stability of the optimization. `` Any feedback and further suggestions are highly appreciated. create table t1(id int); create table t2(id int); create table t3(id int); explain select t1.* from t1, t2, t3 where t1.id in (1); explain select t1.* from t1, t2, t3 where t1.id in (1,1); explain select t1.* from t1, t2, t3 where t1.id in (t2.id); explain select t1.* from t1, t2, t3 where t1.id in (t2.id, t2.id); explain select t1.* from t1, t2, t3 where t1.id in (1, t2.id, t2.id, 1); explain select t1.* from t1, t2, t3 where (t1.id,t2.id) in ((1, 11)); explain select t1.* from t1, t2, t3 where (t1.id,t2.id) in ((1,11),(1,11)); explain select t1.* from t1, t2, t3 where (t1.id,t2.id) in ((t2.id, t2.id)); explain select t1.* from t1, t2, t3 where (t1.id,t2.id) in ((t2.id, t2.id),(t2.id, t2.id)); explain select t1.* from t1, t2, t3 where (t1.id,t2.id) in ((1,11), (t2.id, t2.id),(t2.id, t2.id), (1,11)); Thanks, -- ZizhuanLiu (X-MAN) 44973863@qq.com
-
Re: Make transformAExprIn() return a flattened bool expression directly
Tom Lane <tgl@sss.pgh.pa.us> — 2026-07-01T22:21:15Z
"=?utf-8?B?Wml6aHVhbkxpdSBYLU1BTg==?=" <44973863@qq.com> writes: > Thanks a lot for the suggestion to avoid nested structures in transformAExprIn(). > I also agree that handling this kind of simplification during parsing is cleaner and more appropriate, > even though the planner can achieve a similar effect later via preprocess_qual_conditions(). Just for the record, I think this is a bad idea that we should reject. In general, it is not the business of the parser to perform optimizations. Where we have done that, we've often regretted it. In the specific case at hand, doing this is presupposing that all parse- and rewrite-time logic is capable of dealing with non-binary AND and OR nodes. That seems like a significant risk to me. At the very least it seems likely to lead to unexpected changes in view dump output. In exchange for taking that nonzero risk, what are we buying? You've offered no evidence of a meaningful or even measurable improvement. regards, tom lane