Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit931766a

Browse files
committed
Simplify COALESCE() with one surviving argument.
If, after removal of useless null-constant arguments, a CoalesceExprhas exactly one remaining argument, we can just take that argument asthe result, without bothering to wrap a new CoalesceExpr around it.This isn't likely to produce any great improvement in runtime per se,but it can lead to better plans since the planner no longer has totreat the expression as non-strict.However, there were a few regression test cases that intentionallywrote COALESCE(x) as a shorthand way of creating a non-strictsubexpression. To avoid ruining the intent of those tests, writeCOALESCE(x,x) instead. (If anyone ever proposes de-duplicatingCOALESCE arguments, we'll need another iteration of this arms race.But it seems pretty unlikely that such an optimization would beworthwhile.)Author: Maksim Milyutin <maksim.milyutin@tantorlabs.ru>Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>Discussion:https://postgr.es/m/8e8573c3-1411-448d-877e-53258b7b2be0@tantorlabs.ru
1 parentfc89682 commit931766a

File tree

5 files changed

+55
-48
lines changed

5 files changed

+55
-48
lines changed

‎src/backend/optimizer/util/clauses.c‎

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3333,6 +3333,13 @@ eval_const_expressions_mutator(Node *node,
33333333
-1,
33343334
coalesceexpr->coalescecollid);
33353335

3336+
/*
3337+
* If there's exactly one surviving argument, we no longer
3338+
* need COALESCE at all: the result is that argument
3339+
*/
3340+
if (list_length(newargs)==1)
3341+
return (Node*)linitial(newargs);
3342+
33363343
newcoalesce=makeNode(CoalesceExpr);
33373344
newcoalesce->coalescetype=coalesceexpr->coalescetype;
33383345
newcoalesce->coalescecollid=coalesceexpr->coalescecollid;

‎src/test/regress/expected/join.out‎

Lines changed: 16 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -5626,14 +5626,14 @@ select * from
56265626
(select 1 as id) as xx
56275627
left join
56285628
(tenk1 as a1 full join (select 1 as id) as yy on (a1.unique1 = yy.id))
5629-
on (xx.id = coalesce(yy.id));
5630-
QUERY PLAN
5631-
---------------------------------------
5629+
on (xx.id = coalesce(yy.id, yy.id));
5630+
QUERY PLAN
5631+
------------------------------------------
56325632
Nested Loop Left Join
56335633
-> Result
56345634
-> Hash Full Join
56355635
Hash Cond: (a1.unique1 = (1))
5636-
Filter: (1 = COALESCE((1)))
5636+
Filter: (1 = COALESCE((1), (1)))
56375637
-> Seq Scan on tenk1 a1
56385638
-> Hash
56395639
-> Result
@@ -5643,7 +5643,7 @@ select * from
56435643
(select 1 as id) as xx
56445644
left join
56455645
(tenk1 as a1 full join (select 1 as id) as yy on (a1.unique1 = yy.id))
5646-
on (xx.id = coalesce(yy.id));
5646+
on (xx.id = coalesce(yy.id, yy.id));
56475647
id | unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 | id
56485648
----+---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------+----
56495649
1 | 1 | 2838 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | EFEAAA | OOOOxx | 1
@@ -8411,20 +8411,20 @@ select * from int4_tbl i left join
84118411

84128412
explain (verbose, costs off)
84138413
select * from int4_tbl i left join
8414-
lateral (select coalesce(i) from int2_tbl j where i.f1 = j.f1) k on true;
8415-
QUERY PLAN
8416-
-------------------------------------
8414+
lateral (select coalesce(i, i) from int2_tbl j where i.f1 = j.f1) k on true;
8415+
QUERY PLAN
8416+
------------------------------------------
84178417
Nested Loop Left Join
8418-
Output: i.f1, (COALESCE(i.*))
8418+
Output: i.f1, (COALESCE(i.*, i.*))
84198419
-> Seq Scan on public.int4_tbl i
84208420
Output: i.f1, i.*
84218421
-> Seq Scan on public.int2_tbl j
8422-
Output: j.f1, COALESCE(i.*)
8422+
Output: j.f1, COALESCE(i.*, i.*)
84238423
Filter: (i.f1 = j.f1)
84248424
(7 rows)
84258425

84268426
select * from int4_tbl i left join
8427-
lateral (select coalesce(i) from int2_tbl j where i.f1 = j.f1) k on true;
8427+
lateral (select coalesce(i, i) from int2_tbl j where i.f1 = j.f1) k on true;
84288428
f1 | coalesce
84298429
-------------+----------
84308430
0 | (0)
@@ -9593,14 +9593,14 @@ CREATE STATISTICS group_tbl_stat (ndistinct) ON a, b FROM group_tbl;
95939593
ANALYZE group_tbl;
95949594
EXPLAIN (COSTS OFF)
95959595
SELECT 1 FROM group_tbl t1
9596-
LEFT JOIN (SELECT a c1, COALESCE(a) c2 FROM group_tbl t2) s ON TRUE
9596+
LEFT JOIN (SELECT a c1, COALESCE(a, a) c2 FROM group_tbl t2) s ON TRUE
95979597
GROUP BY s.c1, s.c2;
9598-
QUERY PLAN
9599-
--------------------------------------------
9598+
QUERY PLAN
9599+
------------------------------------------------
96009600
Group
9601-
Group Key: t2.a, (COALESCE(t2.a))
9601+
Group Key: t2.a, (COALESCE(t2.a, t2.a))
96029602
-> Sort
9603-
Sort Key: t2.a, (COALESCE(t2.a))
9603+
Sort Key: t2.a, (COALESCE(t2.a, t2.a))
96049604
-> Nested Loop Left Join
96059605
-> Seq Scan on group_tbl t1
96069606
-> Seq Scan on group_tbl t2

‎src/test/regress/expected/subselect.out‎

Lines changed: 23 additions & 23 deletions
Original file line numberDiff line numberDiff line change
@@ -2127,30 +2127,30 @@ explain (verbose, costs off)
21272127
select ss2.* from
21282128
int8_tbl t1 left join
21292129
(int8_tbl t2 left join
2130-
(select coalesce(q1) as x, * from int8_tbl t3) ss1 on t2.q1 = ss1.q2 inner join
2130+
(select coalesce(q1, q1) as x, * from int8_tbl t3) ss1 on t2.q1 = ss1.q2 inner join
21312131
lateral (select ss1.x as y, * from int8_tbl t4) ss2 on t2.q2 = ss2.q1)
21322132
on t1.q2 = ss2.q1
21332133
order by 1, 2, 3;
2134-
QUERY PLAN
2135-
----------------------------------------------------------------
2134+
QUERY PLAN
2135+
-----------------------------------------------------------------------
21362136
Sort
2137-
Output: (COALESCE(t3.q1)), t4.q1, t4.q2
2138-
Sort Key: (COALESCE(t3.q1)), t4.q1, t4.q2
2137+
Output: (COALESCE(t3.q1, t3.q1)), t4.q1, t4.q2
2138+
Sort Key: (COALESCE(t3.q1, t3.q1)), t4.q1, t4.q2
21392139
-> Hash Right Join
2140-
Output: (COALESCE(t3.q1)), t4.q1, t4.q2
2140+
Output: (COALESCE(t3.q1, t3.q1)), t4.q1, t4.q2
21412141
Hash Cond: (t4.q1 = t1.q2)
21422142
-> Hash Join
2143-
Output: (COALESCE(t3.q1)), t4.q1, t4.q2
2143+
Output: (COALESCE(t3.q1, t3.q1)), t4.q1, t4.q2
21442144
Hash Cond: (t2.q2 = t4.q1)
21452145
-> Hash Left Join
2146-
Output: t2.q2, (COALESCE(t3.q1))
2146+
Output: t2.q2, (COALESCE(t3.q1, t3.q1))
21472147
Hash Cond: (t2.q1 = t3.q2)
21482148
-> Seq Scan on public.int8_tbl t2
21492149
Output: t2.q1, t2.q2
21502150
-> Hash
2151-
Output: t3.q2, (COALESCE(t3.q1))
2151+
Output: t3.q2, (COALESCE(t3.q1, t3.q1))
21522152
-> Seq Scan on public.int8_tbl t3
2153-
Output: t3.q2, COALESCE(t3.q1)
2153+
Output: t3.q2, COALESCE(t3.q1, t3.q1)
21542154
-> Hash
21552155
Output: t4.q1, t4.q2
21562156
-> Seq Scan on public.int8_tbl t4
@@ -2164,7 +2164,7 @@ order by 1, 2, 3;
21642164
select ss2.* from
21652165
int8_tbl t1 left join
21662166
(int8_tbl t2 left join
2167-
(select coalesce(q1) as x, * from int8_tbl t3) ss1 on t2.q1 = ss1.q2 inner join
2167+
(select coalesce(q1, q1) as x, * from int8_tbl t3) ss1 on t2.q1 = ss1.q2 inner join
21682168
lateral (select ss1.x as y, * from int8_tbl t4) ss2 on t2.q2 = ss2.q1)
21692169
on t1.q2 = ss2.q1
21702170
order by 1, 2, 3;
@@ -2201,32 +2201,32 @@ explain (verbose, costs off)
22012201
select ss2.* from
22022202
int8_tbl t1 left join
22032203
(int8_tbl t2 left join
2204-
(select coalesce(q1) as x, * from int8_tbl t3) ss1 on t2.q1 = ss1.q2 left join
2204+
(select coalesce(q1, q1) as x, * from int8_tbl t3) ss1 on t2.q1 = ss1.q2 left join
22052205
lateral (select ss1.x as y, * from int8_tbl t4) ss2 on t2.q2 = ss2.q1)
22062206
on t1.q2 = ss2.q1
22072207
order by 1, 2, 3;
2208-
QUERY PLAN
2209-
----------------------------------------------------------------
2208+
QUERY PLAN
2209+
-----------------------------------------------------------------------
22102210
Sort
2211-
Output: ((COALESCE(t3.q1))), t4.q1, t4.q2
2212-
Sort Key: ((COALESCE(t3.q1))), t4.q1, t4.q2
2211+
Output: ((COALESCE(t3.q1, t3.q1))), t4.q1, t4.q2
2212+
Sort Key: ((COALESCE(t3.q1, t3.q1))), t4.q1, t4.q2
22132213
-> Hash Right Join
2214-
Output: ((COALESCE(t3.q1))), t4.q1, t4.q2
2214+
Output: ((COALESCE(t3.q1, t3.q1))), t4.q1, t4.q2
22152215
Hash Cond: (t4.q1 = t1.q2)
22162216
-> Nested Loop
2217-
Output: t4.q1, t4.q2, ((COALESCE(t3.q1)))
2217+
Output: t4.q1, t4.q2, ((COALESCE(t3.q1, t3.q1)))
22182218
Join Filter: (t2.q2 = t4.q1)
22192219
-> Hash Left Join
2220-
Output: t2.q2, (COALESCE(t3.q1))
2220+
Output: t2.q2, (COALESCE(t3.q1, t3.q1))
22212221
Hash Cond: (t2.q1 = t3.q2)
22222222
-> Seq Scan on public.int8_tbl t2
22232223
Output: t2.q1, t2.q2
22242224
-> Hash
2225-
Output: t3.q2, (COALESCE(t3.q1))
2225+
Output: t3.q2, (COALESCE(t3.q1, t3.q1))
22262226
-> Seq Scan on public.int8_tbl t3
2227-
Output: t3.q2, COALESCE(t3.q1)
2227+
Output: t3.q2, COALESCE(t3.q1, t3.q1)
22282228
-> Seq Scan on public.int8_tbl t4
2229-
Output: t4.q1, t4.q2, (COALESCE(t3.q1))
2229+
Output: t4.q1, t4.q2, (COALESCE(t3.q1, t3.q1))
22302230
-> Hash
22312231
Output: t1.q2
22322232
-> Seq Scan on public.int8_tbl t1
@@ -2236,7 +2236,7 @@ order by 1, 2, 3;
22362236
select ss2.* from
22372237
int8_tbl t1 left join
22382238
(int8_tbl t2 left join
2239-
(select coalesce(q1) as x, * from int8_tbl t3) ss1 on t2.q1 = ss1.q2 left join
2239+
(select coalesce(q1, q1) as x, * from int8_tbl t3) ss1 on t2.q1 = ss1.q2 left join
22402240
lateral (select ss1.x as y, * from int8_tbl t4) ss2 on t2.q2 = ss2.q1)
22412241
on t1.q2 = ss2.q1
22422242
order by 1, 2, 3;

‎src/test/regress/sql/join.sql‎

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1977,13 +1977,13 @@ select * from
19771977
(select1as id)as xx
19781978
left join
19791979
(tenk1as a1 fulljoin (select1as id)as yyon (a1.unique1=yy.id))
1980-
on (xx.id= coalesce(yy.id));
1980+
on (xx.id= coalesce(yy.id,yy.id));
19811981

19821982
select*from
19831983
(select1as id)as xx
19841984
left join
19851985
(tenk1as a1 fulljoin (select1as id)as yyon (a1.unique1=yy.id))
1986-
on (xx.id= coalesce(yy.id));
1986+
on (xx.id= coalesce(yy.id,yy.id));
19871987

19881988
--
19891989
-- test ability to push constants through outer join clauses
@@ -3169,9 +3169,9 @@ select * from int4_tbl i left join
31693169
lateral (select*from int2_tbl jwherei.f1=j.f1) kon true;
31703170
explain (verbose, costs off)
31713171
select*from int4_tbl ileft join
3172-
lateral (select coalesce(i)from int2_tbl jwherei.f1=j.f1) kon true;
3172+
lateral (select coalesce(i, i)from int2_tbl jwherei.f1=j.f1) kon true;
31733173
select*from int4_tbl ileft join
3174-
lateral (select coalesce(i)from int2_tbl jwherei.f1=j.f1) kon true;
3174+
lateral (select coalesce(i, i)from int2_tbl jwherei.f1=j.f1) kon true;
31753175
explain (verbose, costs off)
31763176
select*from int4_tbl a,
31773177
lateral (
@@ -3637,7 +3637,7 @@ ANALYZE group_tbl;
36373637

36383638
EXPLAIN (COSTS OFF)
36393639
SELECT1FROM group_tbl t1
3640-
LEFT JOIN (SELECT a c1, COALESCE(a) c2FROM group_tbl t2) sON TRUE
3640+
LEFT JOIN (SELECT a c1, COALESCE(a, a) c2FROM group_tbl t2) sON TRUE
36413641
GROUP BYs.c1,s.c2;
36423642

36433643
DROPTABLE group_tbl;

‎src/test/regress/sql/subselect.sql‎

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1041,15 +1041,15 @@ explain (verbose, costs off)
10411041
select ss2.*from
10421042
int8_tbl t1left join
10431043
(int8_tbl t2left join
1044-
(select coalesce(q1)as x,*from int8_tbl t3) ss1ont2.q1=ss1.q2inner join
1044+
(select coalesce(q1, q1)as x,*from int8_tbl t3) ss1ont2.q1=ss1.q2inner join
10451045
lateral (selectss1.xas y,*from int8_tbl t4) ss2ont2.q2=ss2.q1)
10461046
ont1.q2=ss2.q1
10471047
order by1,2,3;
10481048

10491049
select ss2.*from
10501050
int8_tbl t1left join
10511051
(int8_tbl t2left join
1052-
(select coalesce(q1)as x,*from int8_tbl t3) ss1ont2.q1=ss1.q2inner join
1052+
(select coalesce(q1, q1)as x,*from int8_tbl t3) ss1ont2.q1=ss1.q2inner join
10531053
lateral (selectss1.xas y,*from int8_tbl t4) ss2ont2.q2=ss2.q1)
10541054
ont1.q2=ss2.q1
10551055
order by1,2,3;
@@ -1059,15 +1059,15 @@ explain (verbose, costs off)
10591059
select ss2.*from
10601060
int8_tbl t1left join
10611061
(int8_tbl t2left join
1062-
(select coalesce(q1)as x,*from int8_tbl t3) ss1ont2.q1=ss1.q2left join
1062+
(select coalesce(q1, q1)as x,*from int8_tbl t3) ss1ont2.q1=ss1.q2left join
10631063
lateral (selectss1.xas y,*from int8_tbl t4) ss2ont2.q2=ss2.q1)
10641064
ont1.q2=ss2.q1
10651065
order by1,2,3;
10661066

10671067
select ss2.*from
10681068
int8_tbl t1left join
10691069
(int8_tbl t2left join
1070-
(select coalesce(q1)as x,*from int8_tbl t3) ss1ont2.q1=ss1.q2left join
1070+
(select coalesce(q1, q1)as x,*from int8_tbl t3) ss1ont2.q1=ss1.q2left join
10711071
lateral (selectss1.xas y,*from int8_tbl t4) ss2ont2.q2=ss2.q1)
10721072
ont1.q2=ss2.q1
10731073
order by1,2,3;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp