Skip to content

Commit 456fa63

Browse files
david-rowleydavid-rowley
committed
Teach planner about more monotonic window functions
9d9c02c introduced runConditions for window functions to allow monotonic window function evaluation to be made more efficient when the window function value went beyond some value that it would never go back from due to its monotonic nature. That commit added prosupport functions to inform the planner that row_number(), rank(), dense_rank() and some forms of count(*) were monotonic. Here we add support for ntile(), cume_dist() and percent_rank(). Reviewed-by: Melanie Plageman Discussion: https://postgr.es/m/CAApHDvqR+VqB8s+xR-24bzJbU8xyFrBszJ17qKgECf7cWxLCaA@mail.gmail.com
1 parent 783d8ab commit 456fa63

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-
) e WHERE rn <= 1 AND c1 <= 3;
1226+
) e WHERE rn <= 1 AND c1 <= 3 AND 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-
) e WHERE rn <= 1 AND c1 <= 3;
1237+
) e WHERE rn <= 1 AND c1 <= 3 AND 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)