Skip to content

Commit a14a583

Browse files
david-rowleydavid-rowley
committed
Add additional regression tests for select_active_windows
During the development of 728202b, which was aimed at reducing the number of sorts required to evaluate multiple window functions with different WindowClause definitions, the code written sorted the WindowClauses in reverse tleSortGroupRef order. There appears to be no discussion in the thread which was opened to discuss the development of this patch and no comments mentioning the fact that having the WindowClauses in reverse tleSortGroupRef order makes it more likely that the final WindowClause to be evaluated will provide presorted input to the query's DISTINCT or ORDER BY clause. The reason for this is that the tleSortGroupRef indexes are assigned for the DISTINCT and ORDER BY clauses before they are for the WindowClauses PARTITION BY and ORDER BY clauses. Putting the WindowClause with the lowest tleSortGroupRef last means that it's more likely that no additional sorting is required for the query's DISTINCT or ORDER BY clause. All we're doing here is adding some tests and a comment to help ensure that remains true and that we don't accidentally forget to consider this again should we ever rewrite that code. Author: Ankit Kumar Pandey, David Rowley Discussion: https://postgr.es/m/CAApHDvq=g2=ny59f1bvwRVvupsgPHK-KjLPBsSL25fVuGZ4idQ@mail.gmail.com
1 parent c6e1f62 commit a14a583

File tree

3 files changed

+156
-0
lines changed

3 files changed

+156
-0
lines changed

‎src/backend/optimizer/plan/planner.c

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5672,6 +5672,14 @@ select_active_windows(PlannerInfo *root, WindowFuncLists *wflists)
56725672
* Sort the windows by the required sorting clauses. First, compare the sort
56735673
* clauses themselves. Second, if one window's clauses are a prefix of another
56745674
* one's clauses, put the window with more sort clauses first.
5675+
*
5676+
* We purposefully sort by the highest tleSortGroupRef first. Since
5677+
* tleSortGroupRefs are assigned for the query's DISTINCT and ORDER BY first
5678+
* and because here we sort the lowest tleSortGroupRefs last, if a
5679+
* WindowClause is sharing a tleSortGroupRef with the query's DISTINCT or
5680+
* ORDER BY clause, this makes it more likely that the final WindowAgg will
5681+
* provide presorted input for the query's DISTINCT or ORDER BY clause, thus
5682+
* reducing the total number of sorts required for the query.
56755683
*/
56765684
static int
56775685
common_prefix_cmp(const void *a, const void *b)

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

Lines changed: 98 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3885,6 +3885,104 @@ WHERE depname = 'sales';
38853885
Filter: ((depname)::text = 'sales'::text)
38863886
(7 rows)
38873887

3888+
-- Ensure that the evaluation order of the WindowAggs results in the WindowAgg
3889+
-- with the same sort order that's required by the ORDER BY is evaluated last.
3890+
EXPLAIN (COSTS OFF)
3891+
SELECT empno,
3892+
enroll_date,
3893+
depname,
3894+
sum(salary) OVER (PARTITION BY depname order by empno) depsalary,
3895+
min(salary) OVER (PARTITION BY depname order by enroll_date) depminsalary
3896+
FROM empsalary
3897+
ORDER BY depname, empno;
3898+
QUERY PLAN
3899+
----------------------------------------------------
3900+
WindowAgg
3901+
-> Incremental Sort
3902+
Sort Key: depname, empno
3903+
Presorted Key: depname
3904+
-> WindowAgg
3905+
-> Sort
3906+
Sort Key: depname, enroll_date
3907+
-> Seq Scan on empsalary
3908+
(8 rows)
3909+
3910+
-- As above, but with an adjusted ORDER BY to ensure the above plan didn't
3911+
-- perform only 2 sorts by accident.
3912+
EXPLAIN (COSTS OFF)
3913+
SELECT empno,
3914+
enroll_date,
3915+
depname,
3916+
sum(salary) OVER (PARTITION BY depname order by empno) depsalary,
3917+
min(salary) OVER (PARTITION BY depname order by enroll_date) depminsalary
3918+
FROM empsalary
3919+
ORDER BY depname, enroll_date;
3920+
QUERY PLAN
3921+
-----------------------------------------------
3922+
WindowAgg
3923+
-> Incremental Sort
3924+
Sort Key: depname, enroll_date
3925+
Presorted Key: depname
3926+
-> WindowAgg
3927+
-> Sort
3928+
Sort Key: depname, empno
3929+
-> Seq Scan on empsalary
3930+
(8 rows)
3931+
3932+
SET enable_hashagg TO off;
3933+
-- Ensure we don't get a sort for both DISTINCT and ORDER BY. We expect the
3934+
-- sort for the DISTINCT to provide presorted input for the ORDER BY.
3935+
EXPLAIN (COSTS OFF)
3936+
SELECT DISTINCT
3937+
empno,
3938+
enroll_date,
3939+
depname,
3940+
sum(salary) OVER (PARTITION BY depname order by empno) depsalary,
3941+
min(salary) OVER (PARTITION BY depname order by enroll_date) depminsalary
3942+
FROM empsalary
3943+
ORDER BY depname, enroll_date;
3944+
QUERY PLAN
3945+
-----------------------------------------------------------------------------------------------
3946+
Unique
3947+
-> Sort
3948+
Sort Key: depname, enroll_date, empno, (sum(salary) OVER (?)), (min(salary) OVER (?))
3949+
-> WindowAgg
3950+
-> Incremental Sort
3951+
Sort Key: depname, enroll_date
3952+
Presorted Key: depname
3953+
-> WindowAgg
3954+
-> Sort
3955+
Sort Key: depname, empno
3956+
-> Seq Scan on empsalary
3957+
(11 rows)
3958+
3959+
-- As above but adjust the ORDER BY clause to help ensure the plan with the
3960+
-- minimum amount of sorting wasn't a fluke.
3961+
EXPLAIN (COSTS OFF)
3962+
SELECT DISTINCT
3963+
empno,
3964+
enroll_date,
3965+
depname,
3966+
sum(salary) OVER (PARTITION BY depname order by empno) depsalary,
3967+
min(salary) OVER (PARTITION BY depname order by enroll_date) depminsalary
3968+
FROM empsalary
3969+
ORDER BY depname, empno;
3970+
QUERY PLAN
3971+
-----------------------------------------------------------------------------------------------
3972+
Unique
3973+
-> Sort
3974+
Sort Key: depname, empno, enroll_date, (sum(salary) OVER (?)), (min(salary) OVER (?))
3975+
-> WindowAgg
3976+
-> Incremental Sort
3977+
Sort Key: depname, empno
3978+
Presorted Key: depname
3979+
-> WindowAgg
3980+
-> Sort
3981+
Sort Key: depname, enroll_date
3982+
-> Seq Scan on empsalary
3983+
(11 rows)
3984+
3985+
RESET enable_hashagg;
38883986
-- Test Sort node reordering
38893987
EXPLAIN (COSTS OFF)
38903988
SELECT

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

Lines changed: 50 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1276,6 +1276,56 @@ SELECT * FROM
12761276
FROM empsalary) emp
12771277
WHERE depname = 'sales';
12781278

1279+
-- Ensure that the evaluation order of the WindowAggs results in the WindowAgg
1280+
-- with the same sort order that's required by the ORDER BY is evaluated last.
1281+
EXPLAIN (COSTS OFF)
1282+
SELECT empno,
1283+
enroll_date,
1284+
depname,
1285+
sum(salary) OVER (PARTITION BY depname order by empno) depsalary,
1286+
min(salary) OVER (PARTITION BY depname order by enroll_date) depminsalary
1287+
FROM empsalary
1288+
ORDER BY depname, empno;
1289+
1290+
-- As above, but with an adjusted ORDER BY to ensure the above plan didn't
1291+
-- perform only 2 sorts by accident.
1292+
EXPLAIN (COSTS OFF)
1293+
SELECT empno,
1294+
enroll_date,
1295+
depname,
1296+
sum(salary) OVER (PARTITION BY depname order by empno) depsalary,
1297+
min(salary) OVER (PARTITION BY depname order by enroll_date) depminsalary
1298+
FROM empsalary
1299+
ORDER BY depname, enroll_date;
1300+
1301+
SET enable_hashagg TO off;
1302+
1303+
-- Ensure we don't get a sort for both DISTINCT and ORDER BY. We expect the
1304+
-- sort for the DISTINCT to provide presorted input for the ORDER BY.
1305+
EXPLAIN (COSTS OFF)
1306+
SELECT DISTINCT
1307+
empno,
1308+
enroll_date,
1309+
depname,
1310+
sum(salary) OVER (PARTITION BY depname order by empno) depsalary,
1311+
min(salary) OVER (PARTITION BY depname order by enroll_date) depminsalary
1312+
FROM empsalary
1313+
ORDER BY depname, enroll_date;
1314+
1315+
-- As above but adjust the ORDER BY clause to help ensure the plan with the
1316+
-- minimum amount of sorting wasn't a fluke.
1317+
EXPLAIN (COSTS OFF)
1318+
SELECT DISTINCT
1319+
empno,
1320+
enroll_date,
1321+
depname,
1322+
sum(salary) OVER (PARTITION BY depname order by empno) depsalary,
1323+
min(salary) OVER (PARTITION BY depname order by enroll_date) depminsalary
1324+
FROM empsalary
1325+
ORDER BY depname, empno;
1326+
1327+
RESET enable_hashagg;
1328+
12791329
-- Test Sort node reordering
12801330
EXPLAIN (COSTS OFF)
12811331
SELECT

0 commit comments

Comments
 (0)