8000 Simplify COALESCE() with one surviving argument. · postgres/postgres@931766a · GitHub
[go: up one dir, main page]
Skip to content

Commit 931766a

Browse files
tglsfdctglsfdc
committed
Simplify COALESCE() with one surviving argument.
If, after removal of useless null-constant arguments, a CoalesceExpr has exactly one remaining argument, we can just take that argument as the 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 to treat the expression as non-strict. However, there were a few regression test cases that intentionally wrote COALESCE(x) as a shorthand way of creating a non-strict subexpression. To avoid ruining the intent of those tests, write COALESCE(x,x) instead. (If anyone ever proposes de-duplicating COALESCE arguments, we'll need another iteration of this arms race. But it seems pretty unlikely that such an optimization would be worthwhile.) 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 parent fc89682 commit 931766a

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
< 10000 td data-grid-cell-id="diff-5c35ab30189e6faaf7d3636d12490b94e00e6c3da2482ad5f196fbe89d76a37b-2168-2168-0" data-selected="false" role="gridcell" style="background-color:var(--bgColor-default);text-align:center" tabindex="-1" valign="top" class="focusable-grid-cell diff-line-number position-relative diff-line-number-neutral left-side">2168
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
2168
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
(select 1 as id) as xx
19781978
left join
19791979
(tenk1 as a1 full join (select 1 as id) as yy on (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
(select 1 as id) as xx
19841984
left join
19851985
(tenk1 as a1 full join (select 1 as id) as yy on (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 j where i.f1 = j.f1) k on true;
31703170
explain (verbose, costs off)
31713171
select * from int4_tbl i left join
3172-
lateral (select coalesce(i) from int2_tbl j where i.f1 = j.f1) k on true;
3172+
lateral (select coalesce(i, i) from int2_tbl j where i.f1 = j.f1) k on true;
31733173
select * from int4_tbl i left join
3174-
lateral (select coalesce(i) from int2_tbl j where i.f1 = j.f1) k on true;
3174+
lateral (select coalesce(i, i) from int2_tbl j where i.f1 = j.f1) k on 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
SELECT 1 FROM group_tbl t1
3640-
LEFT JOIN (SELECT a c1, COALESCE(a) c2 FROM group_tbl t2) s ON TRUE
3640+
LEFT JOIN (SELECT a c1, COALESCE(a, a) c2 FROM group_tbl t2) s ON TRUE
36413641
GROUP BY s.c1, s.c2;
36423642

36433643
DROP TABLE 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 t1 left join
10431043
(int8_tbl t2 left join
1044-
(select coalesce(q1) as x, * from int8_tbl t3) ss1 on t2.q1 = ss1.q2 inner join
1044+
(select coalesce(q1, q1) as x, * from int8_tbl t3) ss1 on t2.q1 = ss1.q2 inner join
10451045
lateral (select ss1.x as y, * from int8_tbl t4) ss2 on t2.q2 = ss2.q1)
10461046
on t1.q2 = ss2.q1
10471047
order by 1, 2, 3;
10481048

10491049
select ss2.* from
10501050
int8_tbl t1 left join
10511051
(int8_tbl t2 left join
1052-
(select coalesce(q1) as x, * from int8_tbl t3) ss1 on t2.q1 = ss1.q2 inner join
1052+
(select coalesce(q1, q1) as x, * from int8_tbl t3) ss1 on t2.q1 = ss1.q2 inner join
10531053
lateral (select ss1.x as y, * from int8_tbl t4) ss2 on t2.q2 = ss2.q1)
10541054
on t1.q2 = ss2.q1
10551055
order by 1, 2, 3;
@@ -1059,15 +1059,15 @@ explain (verbose, costs off)
10591059
select ss2.* from
10601060
int8_tbl t1 left join
10611061
(int8_tbl t2 left join
1062-
(select coalesce(q1) as x, * from int8_tbl t3) ss1 on t2.q1 = ss1.q2 left join
1062+
(select coalesce(q1, q1) as x, * from int8_tbl t3) ss1 on t2.q1 = ss1.q2 left join
10631063
lateral (select ss1.x as y, * from int8_tbl t4) ss2 on t2.q2 = ss2.q1)
10641064
on t1.q2 = ss2.q1
10651065
order by 1, 2, 3;
10661066

10671067
select ss2.* from
10681068
int8_tbl t1 left join
10691069
(int8_tbl t2 left join
1070-
(select coalesce(q1) as x, * from int8_tbl t3) ss1 on t2.q1 = ss1.q2 left join
1070+
(select coalesce(q1, q1) as x, * from int8_tbl t3) ss1 on t2.q1 = ss1.q2 left join
10711071
lateral (select ss1.x as y, * from int8_tbl t4) ss2 on t2.q2 = ss2.q1)
10721072
on t1.q2 = ss2.q1
10731073
order by 1, 2, 3;

0 commit comments

Comments
 (0)
0