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

Commit456fa63

Browse files
committed
Teach planner about more monotonic window functions
9d9c02c introduced runConditions for window functions to allowmonotonic window function evaluation to be made more efficient when thewindow function value went beyond some value that it would never go backfrom due to its monotonic nature. That commit added prosupport functionsto inform the planner that row_number(), rank(), dense_rank() and someforms of count(*) were monotonic. Here we add support for ntile(),cume_dist() and percent_rank().Reviewed-by: Melanie PlagemanDiscussion:https://postgr.es/m/CAApHDvqR+VqB8s+xR-24bzJbU8xyFrBszJ17qKgECf7cWxLCaA@mail.gmail.com
1 parent783d8ab commit456fa63

File tree

3 files changed

+50
-16
lines changed

3 files changed

+50
-16
lines changed

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

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -288,6 +288,15 @@ window_percent_rank_support(PG_FUNCTION_ARGS)
288288
{
289289
Node*rawreq= (Node*)PG_GETARG_POINTER(0);
290290

291+
if (IsA(rawreq,SupportRequestWFuncMonotonic))
292+
{
293+
SupportRequestWFuncMonotonic*req= (SupportRequestWFuncMonotonic*)rawreq;
294+
295+
/* percent_rank() is monotonically increasing */
296+
req->monotonic=MONOTONICFUNC_INCREASING;
297+
PG_RETURN_POINTER(req);
298+
}
299+
291300
if (IsA(rawreq,SupportRequestOptimizeWindowClause))
292301
{
293302
SupportRequestOptimizeWindowClause*req= (SupportRequestOptimizeWindowClause*)rawreq;
@@ -362,6 +371,15 @@ window_cume_dist_support(PG_FUNCTION_ARGS)
362371
{
363372
Node*rawreq= (Node*)PG_GETARG_POINTER(0);
364373

374+
if (IsA(rawreq,SupportRequestWFuncMonotonic))
375+
{
376+
SupportRequestWFuncMonotonic*req= (SupportRequestWFuncMonotonic*)rawreq;
377+
378+
/* cume_dist() is monotonically increasing */
379+
req->monotonic=MONOTONICFUNC_INCREASING;
380+
PG_RETURN_POINTER(req);
381+
}
382+
365383
if (IsA(rawreq,SupportRequestOptimizeWindowClause))
366384
{
367385
SupportRequestOptimizeWindowClause*req= (SupportRequestOptimizeWindowClause*)rawreq;
@@ -465,6 +483,18 @@ window_ntile_support(PG_FUNCTION_ARGS)
465483
{
466484
Node*rawreq= (Node*)PG_GETARG_POINTER(0);
467485

486+
if (IsA(rawreq,SupportRequestWFuncMonotonic))
487+
{
488+
SupportRequestWFuncMonotonic*req= (SupportRequestWFuncMonotonic*)rawreq;
489+
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);
496+
}
497+
468498
if (IsA(rawreq,SupportRequestOptimizeWindowClause))
469499
{
470500
SupportRequestOptimizeWindowClause*req= (SupportRequestOptimizeWindowClause*)rawreq;

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

Lines changed: 14 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -3766,19 +3766,20 @@ SELECT * FROM
37663766
count(salary) OVER (PARTITION BY depname || '') c1, -- w1
37673767
row_number() OVER (PARTITION BY depname) rn, -- w2
37683768
count(*) OVER (PARTITION BY depname) c2, -- w2
3769-
count(*) OVER (PARTITION BY '' || depname) c3 -- w3
3769+
count(*) OVER (PARTITION BY '' || depname) c3, -- w3
3770+
ntile(2) OVER (PARTITION BY depname) nt -- w2
37703771
FROM empsalary
3771-
) e WHERE rn <= 1 AND c1 <= 3;
3772-
QUERY PLAN
3773-
-------------------------------------------------------------------------------------------
3772+
) e WHERE rn <= 1 AND c1 <= 3 AND nt < 2;
3773+
QUERY PLAN
3774+
-----------------------------------------------------------------------------------------------
37743775
Subquery Scan on e
37753776
-> WindowAgg
3776-
Filter: ((row_number() OVER (?)) <= 1)
3777+
Filter: (((row_number() OVER (?)) <= 1) AND ((ntile(2) OVER (?)) < 2))
37773778
Run Condition: (count(empsalary.salary) OVER (?) <= 3)
37783779
-> Sort
37793780
Sort Key: (((empsalary.depname)::text || ''::text))
37803781
-> WindowAgg
3781-
Run Condition: (row_number() OVER (?) <= 1)
3782+
Run Condition: ((row_number() OVER (?) <= 1) AND (ntile(2) OVER (?) < 2))
37823783
-> Sort
37833784
Sort Key: empsalary.depname
37843785
-> WindowAgg
@@ -3793,13 +3794,14 @@ SELECT * FROM
37933794
count(salary) OVER (PARTITION BY depname || '') c1, -- w1
37943795
row_number() OVER (PARTITION BY depname) rn, -- w2
37953796
count(*) OVER (PARTITION BY depname) c2, -- w2
3796-
count(*) OVER (PARTITION BY '' || depname) c3 -- w3
3797+
count(*) OVER (PARTITION BY '' || depname) c3, -- w3
3798+
ntile(2) OVER (PARTITION BY depname) nt -- w2
37973799
FROM empsalary
3798-
) e WHERE rn <= 1 AND c1 <= 3;
3799-
depname | empno | salary | enroll_date | c1 | rn | c2 | c3
3800-
-----------+-------+--------+-------------+----+----+----+----
3801-
personnel | 5 | 3500 | 12-10-2007 | 2 | 1 | 2 | 2
3802-
sales | 3 | 4800 | 08-01-2007 | 3 | 1 | 3 | 3
3800+
) e WHERE rn <= 1 AND c1 <= 3 AND nt < 2;
3801+
depname | empno | salary | enroll_date | c1 | rn | c2 | c3| nt
3802+
-----------+-------+--------+-------------+----+----+----+----+----
3803+
personnel | 5 | 3500 | 12-10-2007 | 2 | 1 | 2 | 2 | 1
3804+
sales | 3 | 4800 | 08-01-2007 | 3 | 1 | 3 | 3 | 1
38033805
(2 rows)
38043806

38053807
-- Tests to ensure we don't push down the run condition when it's not valid to

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

Lines changed: 6 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1220,19 +1220,21 @@ SELECT * FROM
12201220
count(salary) OVER (PARTITION BY depname||'') c1,-- w1
12211221
row_number() OVER (PARTITION BY depname) rn,-- w2
12221222
count(*) OVER (PARTITION BY depname) c2,-- w2
1223-
count(*) OVER (PARTITION BY''|| depname) c3-- w3
1223+
count(*) OVER (PARTITION BY''|| depname) c3,-- w3
1224+
ntile(2) OVER (PARTITION BY depname) nt-- w2
12241225
FROM empsalary
1225-
) eWHERE rn<=1AND c1<=3;
1226+
) eWHERE rn<=1AND c1<=3AND nt<2;
12261227

12271228
-- Ensure we correctly filter out all of the run conditions from each window
12281229
SELECT*FROM
12291230
(SELECT*,
12301231
count(salary) OVER (PARTITION BY depname||'') c1,-- w1
12311232
row_number() OVER (PARTITION BY depname) rn,-- w2
12321233
count(*) OVER (PARTITION BY depname) c2,-- w2
1233-
count(*) OVER (PARTITION BY''|| depname) c3-- w3
1234+
count(*) OVER (PARTITION BY''|| depname) c3,-- w3
1235+
ntile(2) OVER (PARTITION BY depname) nt-- w2
12341236
FROM empsalary
1235-
) eWHERE rn<=1AND c1<=3;
1237+
) eWHERE rn<=1AND c1<=3AND nt<2;
12361238

12371239
-- Tests to ensure we don't push down the run condition when it's not valid to
12381240
-- do so.

0 commit comments

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