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

Commit9d36b88

Browse files
committed
Disable run condition optimization for some WindowFuncs
94985c2 added code to detect when WindowFuncs were monotonic andallowed additional quals to be "pushed down" into the subquery to beused as WindowClause runConditions in order to short-circuit executionin nodeWindowAgg.c.The Node representation of runConditions wasn't well selected andbecause we do qual pushdown before planning the subquery, the planningof the subquery could perform subquery pull-up of nested subqueries.For WindowFuncs with args, the arguments could be changed after pushingthe qual down to the subquery.This was made more difficult by the fact that the code duplicated theWindowFunc inside an OpExpr to include in the WindowClauses runConditionfield. This could result in duplication of subqueries and a pull-up ofsuch a subquery could result in another initplan parameter being issuedfor the 2nd version of the subplan. This could result in errors such as:ERROR: WindowFunc not found in subplan target listsHere in the backbranches, we don't have the flexibility to improve theNode representation to resolve this, so instead we just disable therunCondition optimization for ntile() unless the argument is a Const,(v16 only) and likewise for count(expr) (both v15 and v16). count(*) isunaffected. All other window functions which support this optimizationall take zero arguments and therefore are unaffected.Bug: #18170Reported-by: Zuming JiangDiscussion:https://postgr.es/m/18170-f1d17bf9a0d58b24@postgresql.orgBackpatch-through 15 (master will be fixed independently)
1 parentf199436 commit9d36b88

File tree

4 files changed

+116
-65
lines changed

4 files changed

+116
-65
lines changed

‎src/backend/utils/adt/int8.c‎

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -833,6 +833,21 @@ int8inc_support(PG_FUNCTION_ARGS)
833833
SupportRequestWFuncMonotonic*req= (SupportRequestWFuncMonotonic*)rawreq;
834834
MonotonicFunctionmonotonic=MONOTONICFUNC_NONE;
835835
intframeOptions=req->window_clause->frameOptions;
836+
WindowFunc*wfunc=req->window_func;
837+
838+
if (list_length(wfunc->args)==1)
839+
{
840+
Node*expr=eval_const_expressions(NULL,linitial(wfunc->args));
841+
842+
/*
843+
* Due to the Node representation of WindowClause runConditions in
844+
* version prior to v17, we need to insist that the count arg is
845+
* Const to allow safe application of the runCondition
846+
* optimization.
847+
*/
848+
if (!IsA(expr,Const))
849+
PG_RETURN_POINTER(NULL);
850+
}
836851

837852
/* No ORDER BY clause then all rows are peers */
838853
if (req->window_clause->orderClause==NIL)

‎src/backend/utils/adt/windowfuncs.c‎

Lines changed: 23 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -14,6 +14,7 @@
1414
#include"postgres.h"
1515

1616
#include"nodes/supportnodes.h"
17+
#include"optimizer/optimizer.h"
1718
#include"utils/builtins.h"
1819
#include"windowapi.h"
1920

@@ -486,13 +487,29 @@ window_ntile_support(PG_FUNCTION_ARGS)
486487
if (IsA(rawreq,SupportRequestWFuncMonotonic))
487488
{
488489
SupportRequestWFuncMonotonic*req= (SupportRequestWFuncMonotonic*)rawreq;
490+
WindowFunc*wfunc=req->window_func;
489491

490-
/*
491-
* ntile() is monotonically increasing as the number of buckets cannot
492-
* change after the first call
493-
*/
494-
req->monotonic=MONOTONICFUNC_INCREASING;
495-
PG_RETURN_POINTER(req);
492+
if (list_length(wfunc->args)==1)
493+
{
494+
Node*expr=eval_const_expressions(NULL,linitial(wfunc->args));
495+
496+
/*
497+
* Due to the Node representation of WindowClause runConditions in
498+
* version prior to v17, we need to insist that ntile arg is Const
499+
* to allow safe application of the runCondition optimization.
500+
*/
501+
if (IsA(expr,Const))
502+
{
503+
/*
504+
* ntile() is monotonically increasing as the number of
505+
* buckets cannot change after the first call
506+
*/
507+
req->monotonic=MONOTONICFUNC_INCREASING;
508+
PG_RETURN_POINTER(req);
509+
}
510+
}
511+
512+
PG_RETURN_POINTER(NULL);
496513
}
497514

498515
if (IsA(rawreq,SupportRequestOptimizeWindowClause))

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

Lines changed: 52 additions & 42 deletions
Original file line numberDiff line numberDiff line change
@@ -3577,13 +3577,13 @@ EXPLAIN (COSTS OFF)
35773577
SELECT * FROM
35783578
(SELECT empno,
35793579
salary,
3580-
count(empno) OVER (ORDER BY salary DESC) c
3580+
count(1) OVER (ORDER BY salary DESC) c
35813581
FROM empsalary) emp
35823582
WHERE c <= 3;
3583-
QUERY PLAN
3584-
---------------------------------------------------------
3583+
QUERY PLAN
3584+
-------------------------------------------
35853585
WindowAgg
3586-
Run Condition: (count(empsalary.empno) OVER (?) <= 3)
3586+
Run Condition: (count(1) OVER (?) <= 3)
35873587
-> Sort
35883588
Sort Key: empsalary.salary DESC
35893589
-> Seq Scan on empsalary
@@ -3592,7 +3592,7 @@ WHERE c <= 3;
35923592
SELECT * FROM
35933593
(SELECT empno,
35943594
salary,
3595-
count(empno) OVER (ORDER BY salary DESC) c
3595+
count(1) OVER (ORDER BY salary DESC) c
35963596
FROM empsalary) emp
35973597
WHERE c <= 3;
35983598
empno | salary | c
@@ -3704,19 +3704,19 @@ WHERE rn < 3;
37043704
-> Seq Scan on empsalary
37053705
(6 rows)
37063706

3707-
-- likewise with count(empno) instead of row_number()
3707+
-- likewise with count(1) instead of row_number()
37083708
EXPLAIN (COSTS OFF)
37093709
SELECT * FROM
37103710
(SELECT empno,
37113711
depname,
37123712
salary,
3713-
count(empno) OVER (PARTITION BY depname ORDER BY salary DESC) c
3713+
count(1) OVER (PARTITION BY depname ORDER BY salary DESC) c
37143714
FROM empsalary) emp
37153715
WHERE c <= 3;
37163716
QUERY PLAN
37173717
------------------------------------------------------------
37183718
WindowAgg
3719-
Run Condition: (count(empsalary.empno) OVER (?) <= 3)
3719+
Run Condition: (count(1) OVER (?) <= 3)
37203720
-> Sort
37213721
Sort Key: empsalary.depname, empsalary.salary DESC
37223722
-> Seq Scan on empsalary
@@ -3727,7 +3727,7 @@ SELECT * FROM
37273727
(SELECT empno,
37283728
depname,
37293729
salary,
3730-
count(empno) OVER (PARTITION BY depname ORDER BY salary DESC) c
3730+
count(1) OVER (PARTITION BY depname ORDER BY salary DESC) c
37313731
FROM empsalary) emp
37323732
WHERE c <= 3;
37333733
empno | depname | salary | c
@@ -3749,21 +3749,21 @@ SELECT * FROM
37493749
(SELECT empno,
37503750
depname,
37513751
salary,
3752-
count(empno) OVER () c
3752+
count(1) OVER () c
37533753
FROM empsalary) emp
37543754
WHERE c = 1;
3755-
QUERY PLAN
3756-
--------------------------------------------------------
3755+
QUERY PLAN
3756+
------------------------------------------
37573757
WindowAgg
3758-
Run Condition: (count(empsalary.empno) OVER (?) = 1)
3758+
Run Condition: (count(1) OVER (?) = 1)
37593759
-> Seq Scan on empsalary
37603760
(3 rows)
37613761

37623762
-- Some more complex cases with multiple window clauses
37633763
EXPLAIN (COSTS OFF)
37643764
SELECT * FROM
37653765
(SELECT *,
3766-
count(salary) OVER (PARTITION BY depname || '') c1, -- w1
3766+
count(1) OVER (PARTITION BY depname || '') c1, -- w1
37673767
row_number() OVER (PARTITION BY depname) rn, -- w2
37683768
count(*) OVER (PARTITION BY depname) c2, -- w2
37693769
count(*) OVER (PARTITION BY '' || depname) c3, -- w3
@@ -3775,7 +3775,7 @@ SELECT * FROM
37753775
Subquery Scan on e
37763776
-> WindowAgg
37773777
Filter: (((row_number() OVER (?)) <= 1) AND ((ntile(2) OVER (?)) < 2))
3778-
Run Condition: (count(empsalary.salary) OVER (?) <= 3)
3778+
Run Condition: (count(1) OVER (?) <= 3)
37793779
-> Sort
37803780
Sort Key: (((empsalary.depname)::text || ''::text))
37813781
-> WindowAgg
@@ -3791,7 +3791,7 @@ SELECT * FROM
37913791
-- Ensure we correctly filter out all of the run conditions from each window
37923792
SELECT * FROM
37933793
(SELECT *,
3794-
count(salary) OVER (PARTITION BY depname || '') c1, -- w1
3794+
count(1) OVER (PARTITION BY depname || '') c1, -- w1
37953795
row_number() OVER (PARTITION BY depname) rn, -- w2
37963796
count(*) OVER (PARTITION BY depname) c2, -- w2
37973797
count(*) OVER (PARTITION BY '' || depname) c3, -- w3
@@ -3804,32 +3804,6 @@ SELECT * FROM
38043804
sales | 3 | 4800 | 08-01-2007 | 3 | 1 | 3 | 3 | 1
38053805
(2 rows)
38063806

3807-
-- Ensure we remove references to reduced outer joins as nulling rels in run
3808-
-- conditions
3809-
EXPLAIN (COSTS OFF)
3810-
SELECT 1 FROM
3811-
(SELECT ntile(e2.salary) OVER (PARTITION BY e1.depname) AS c
3812-
FROM empsalary e1 LEFT JOIN empsalary e2 ON TRUE
3813-
WHERE e1.empno = e2.empno) s
3814-
WHERE s.c = 1;
3815-
QUERY PLAN
3816-
---------------------------------------------------------
3817-
Subquery Scan on s
3818-
Filter: (s.c = 1)
3819-
-> WindowAgg
3820-
Run Condition: (ntile(e2.salary) OVER (?) <= 1)
3821-
-> Sort
3822-
Sort Key: e1.depname
3823-
-> Merge Join
3824-
Merge Cond: (e1.empno = e2.empno)
3825-
-> Sort
3826-
Sort Key: e1.empno
3827-
-> Seq Scan on empsalary e1
3828-
-> Sort
3829-
Sort Key: e2.empno
3830-
-> Seq Scan on empsalary e2
3831-
(14 rows)
3832-
38333807
-- Tests to ensure we don't push down the run condition when it's not valid to
38343808
-- do so.
38353809
-- Ensure we don't push down when the frame options show that the window
@@ -3889,6 +3863,42 @@ WHERE c = 1;
38893863
-> Seq Scan on empsalary
38903864
(6 rows)
38913865

3866+
-- Ensure we don't use a run condition when the WindowFunc arg contains a Var
3867+
EXPLAIN (COSTS OFF)
3868+
SELECT * FROM
3869+
(SELECT empno,
3870+
salary,
3871+
count(empno) OVER (ORDER BY empno DESC) c
3872+
FROM empsalary) emp
3873+
WHERE c = 1;
3874+
QUERY PLAN
3875+
----------------------------------------------
3876+
Subquery Scan on emp
3877+
Filter: (emp.c = 1)
3878+
-> WindowAgg
3879+
-> Sort
3880+
Sort Key: empsalary.empno DESC
3881+
-> Seq Scan on empsalary
3882+
(6 rows)
3883+
3884+
-- As above but with ntile().
3885+
EXPLAIN (COSTS OFF)
3886+
SELECT * FROM
3887+
(SELECT empno,
3888+
salary,
3889+
ntile(empno::int) OVER (ORDER BY empno DESC) nt
3890+
FROM empsalary) emp
3891+
WHERE nt = 1;
3892+
QUERY PLAN
3893+
----------------------------------------------
3894+
Subquery Scan on emp
3895+
Filter: (emp.nt = 1)
3896+
-> WindowAgg
3897+
-> Sort
3898+
Sort Key: empsalary.empno DESC
3899+
-> Seq Scan on empsalary
3900+
(6 rows)
3901+
38923902
-- Ensure we don't use a run condition when the WindowFunc contains subplans
38933903
EXPLAIN (COSTS OFF)
38943904
SELECT * FROM

‎src/test/regress/sql/window.sql‎

Lines changed: 26 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -1120,14 +1120,14 @@ EXPLAIN (COSTS OFF)
11201120
SELECT*FROM
11211121
(SELECT empno,
11221122
salary,
1123-
count(empno) OVER (ORDER BY salaryDESC) c
1123+
count(1) OVER (ORDER BY salaryDESC) c
11241124
FROM empsalary) emp
11251125
WHERE c<=3;
11261126

11271127
SELECT*FROM
11281128
(SELECT empno,
11291129
salary,
1130-
count(empno) OVER (ORDER BY salaryDESC) c
1130+
count(1) OVER (ORDER BY salaryDESC) c
11311131
FROM empsalary) emp
11321132
WHERE c<=3;
11331133

@@ -1183,13 +1183,13 @@ SELECT empno, depname FROM
11831183
FROM empsalary) emp
11841184
WHERE rn<3;
11851185

1186-
-- likewise with count(empno) instead of row_number()
1186+
-- likewise with count(1) instead of row_number()
11871187
EXPLAIN (COSTS OFF)
11881188
SELECT*FROM
11891189
(SELECT empno,
11901190
depname,
11911191
salary,
1192-
count(empno) OVER (PARTITION BY depnameORDER BY salaryDESC) c
1192+
count(1) OVER (PARTITION BY depnameORDER BY salaryDESC) c
11931193
FROM empsalary) emp
11941194
WHERE c<=3;
11951195

@@ -1198,7 +1198,7 @@ SELECT * FROM
11981198
(SELECT empno,
11991199
depname,
12001200
salary,
1201-
count(empno) OVER (PARTITION BY depnameORDER BY salaryDESC) c
1201+
count(1) OVER (PARTITION BY depnameORDER BY salaryDESC) c
12021202
FROM empsalary) emp
12031203
WHERE c<=3;
12041204

@@ -1209,15 +1209,15 @@ SELECT * FROM
12091209
(SELECT empno,
12101210
depname,
12111211
salary,
1212-
count(empno) OVER () c
1212+
count(1) OVER () c
12131213
FROM empsalary) emp
12141214
WHERE c=1;
12151215

12161216
-- Some more complex cases with multiple window clauses
12171217
EXPLAIN (COSTS OFF)
12181218
SELECT*FROM
12191219
(SELECT*,
1220-
count(salary) OVER (PARTITION BY depname||'') c1,-- w1
1220+
count(1) OVER (PARTITION BY depname||'') c1,-- w1
12211221
row_number() OVER (PARTITION BY depname) rn,-- w2
12221222
count(*) OVER (PARTITION BY depname) c2,-- w2
12231223
count(*) OVER (PARTITION BY''|| depname) c3,-- w3
@@ -1228,23 +1228,14 @@ SELECT * FROM
12281228
-- Ensure we correctly filter out all of the run conditions from each window
12291229
SELECT*FROM
12301230
(SELECT*,
1231-
count(salary) OVER (PARTITION BY depname||'') c1,-- w1
1231+
count(1) OVER (PARTITION BY depname||'') c1,-- w1
12321232
row_number() OVER (PARTITION BY depname) rn,-- w2
12331233
count(*) OVER (PARTITION BY depname) c2,-- w2
12341234
count(*) OVER (PARTITION BY''|| depname) c3,-- w3
12351235
ntile(2) OVER (PARTITION BY depname) nt-- w2
12361236
FROM empsalary
12371237
) eWHERE rn<=1AND c1<=3AND nt<2;
12381238

1239-
-- Ensure we remove references to reduced outer joins as nulling rels in run
1240-
-- conditions
1241-
EXPLAIN (COSTS OFF)
1242-
SELECT1FROM
1243-
(SELECT ntile(e2.salary) OVER (PARTITION BYe1.depname)AS c
1244-
FROM empsalary e1LEFT JOIN empsalary e2ON TRUE
1245-
WHEREe1.empno=e2.empno) s
1246-
WHEREs.c=1;
1247-
12481239
-- Tests to ensure we don't push down the run condition when it's not valid to
12491240
-- do so.
12501241

@@ -1278,6 +1269,24 @@ SELECT * FROM
12781269
FROM empsalary) emp
12791270
WHERE c=1;
12801271

1272+
-- Ensure we don't use a run condition when the WindowFunc arg contains a Var
1273+
EXPLAIN (COSTS OFF)
1274+
SELECT*FROM
1275+
(SELECT empno,
1276+
salary,
1277+
count(empno) OVER (ORDER BY empnoDESC) c
1278+
FROM empsalary) emp
1279+
WHERE c=1;
1280+
1281+
-- As above but with ntile().
1282+
EXPLAIN (COSTS OFF)
1283+
SELECT*FROM
1284+
(SELECT empno,
1285+
salary,
1286+
ntile(empno::int) OVER (ORDER BY empnoDESC) nt
1287+
FROM empsalary) emp
1288+
WHERE nt=1;
1289+
12811290
-- Ensure we don't use a run condition when the WindowFunc contains subplans
12821291
EXPLAIN (COSTS OFF)
12831292
SELECT*FROM

0 commit comments

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