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

Commit62e221e

Browse files
committed
Allow incremental sorts for windowing functions
This expands on the work done ind2d8a22 and allows incremental sortto be considered during create_window_paths().Author: David RowleyReviewed-by: Daniel Gustafsson, Tomas VondraDiscussion:https://postgr.es/m/CAApHDvoOHobiA2x13NtWnWLcTXYj9ddpCkv9PnAJQBMegYf_xw%40mail.gmail.com
1 parentfe4f36b commit62e221e

File tree

3 files changed

+100
-7
lines changed

3 files changed

+100
-7
lines changed

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

Lines changed: 34 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -4582,14 +4582,17 @@ create_window_paths(PlannerInfo *root,
45824582
/*
45834583
* Consider computing window functions starting from the existing
45844584
* cheapest-total path (which will likely require a sort) as well as any
4585-
* existing paths that satisfy root->window_pathkeys (which won't).
4585+
* existing paths that satisfyor partially satisfyroot->window_pathkeys.
45864586
*/
45874587
foreach(lc,input_rel->pathlist)
45884588
{
45894589
Path*path= (Path*)lfirst(lc);
4590+
intpresorted_keys;
45904591

45914592
if (path==input_rel->cheapest_total_path||
4592-
pathkeys_contained_in(root->window_pathkeys,path->pathkeys))
4593+
pathkeys_count_contained_in(root->window_pathkeys,path->pathkeys,
4594+
&presorted_keys)||
4595+
presorted_keys>0)
45934596
create_one_window_path(root,
45944597
window_rel,
45954598
path,
@@ -4664,18 +4667,42 @@ create_one_window_path(PlannerInfo *root,
46644667
{
46654668
WindowClause*wc=lfirst_node(WindowClause,l);
46664669
List*window_pathkeys;
4670+
intpresorted_keys;
4671+
boolis_sorted;
46674672

46684673
window_pathkeys=make_pathkeys_for_window(root,
46694674
wc,
46704675
root->processed_tlist);
46714676

4677+
is_sorted=pathkeys_count_contained_in(window_pathkeys,
4678+
path->pathkeys,
4679+
&presorted_keys);
4680+
46724681
/* Sort if necessary */
4673-
if (!pathkeys_contained_in(window_pathkeys,path->pathkeys))
4682+
if (!is_sorted)
46744683
{
4675-
path= (Path*)create_sort_path(root,window_rel,
4676-
path,
4677-
window_pathkeys,
4678-
-1.0);
4684+
/*
4685+
* No presorted keys or incremental sort disabled, just perform a
4686+
* complete sort.
4687+
*/
4688+
if (presorted_keys==0|| !enable_incremental_sort)
4689+
path= (Path*)create_sort_path(root,window_rel,
4690+
path,
4691+
window_pathkeys,
4692+
-1.0);
4693+
else
4694+
{
4695+
/*
4696+
* Since we have presorted keys and incremental sort is
4697+
* enabled, just use incremental sort.
4698+
*/
4699+
path= (Path*)create_incremental_sort_path(root,
4700+
window_rel,
4701+
path,
4702+
window_pathkeys,
4703+
presorted_keys,
4704+
-1.0);
4705+
}
46794706
}
46804707

46814708
if (lnext(activeWindows,l))

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

Lines changed: 44 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3200,6 +3200,50 @@ FROM empsalary;
32003200
-> Seq Scan on empsalary
32013201
(5 rows)
32023202

3203+
-- Test incremental sorting
3204+
EXPLAIN (COSTS OFF)
3205+
SELECT * FROM
3206+
(SELECT depname,
3207+
empno,
3208+
salary,
3209+
enroll_date,
3210+
row_number() OVER (PARTITION BY depname ORDER BY enroll_date) AS first_emp,
3211+
row_number() OVER (PARTITION BY depname ORDER BY enroll_date DESC) AS last_emp
3212+
FROM empsalary) emp
3213+
WHERE first_emp = 1 OR last_emp = 1;
3214+
QUERY PLAN
3215+
-----------------------------------------------------------------------------------
3216+
Subquery Scan on emp
3217+
Filter: ((emp.first_emp = 1) OR (emp.last_emp = 1))
3218+
-> WindowAgg
3219+
-> Incremental Sort
3220+
Sort Key: empsalary.depname, empsalary.enroll_date
3221+
Presorted Key: empsalary.depname
3222+
-> WindowAgg
3223+
-> Sort
3224+
Sort Key: empsalary.depname, empsalary.enroll_date DESC
3225+
-> Seq Scan on empsalary
3226+
(10 rows)
3227+
3228+
SELECT * FROM
3229+
(SELECT depname,
3230+
empno,
3231+
salary,
3232+
enroll_date,
3233+
row_number() OVER (PARTITION BY depname ORDER BY enroll_date) AS first_emp,
3234+
row_number() OVER (PARTITION BY depname ORDER BY enroll_date DESC) AS last_emp
3235+
FROM empsalary) emp
3236+
WHERE first_emp = 1 OR last_emp = 1;
3237+
depname | empno | salary | enroll_date | first_emp | last_emp
3238+
-----------+-------+--------+-------------+-----------+----------
3239+
develop | 8 | 6000 | 10-01-2006 | 1 | 5
3240+
develop | 7 | 4200 | 01-01-2008 | 5 | 1
3241+
personnel | 2 | 3900 | 12-23-2006 | 1 | 2
3242+
personnel | 5 | 3500 | 12-10-2007 | 2 | 1
3243+
sales | 1 | 5000 | 10-01-2006 | 1 | 3
3244+
sales | 4 | 4800 | 08-08-2007 | 3 | 1
3245+
(6 rows)
3246+
32033247
-- cleanup
32043248
DROP TABLE empsalary;
32053249
-- test user-defined window function with named args and default args

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

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -936,6 +936,28 @@ SELECT
936936
lag(1) OVER (PARTITION BY depnameORDER BY salary,enroll_date,empno)
937937
FROM empsalary;
938938

939+
-- Test incremental sorting
940+
EXPLAIN (COSTS OFF)
941+
SELECT*FROM
942+
(SELECT depname,
943+
empno,
944+
salary,
945+
enroll_date,
946+
row_number() OVER (PARTITION BY depnameORDER BY enroll_date)AS first_emp,
947+
row_number() OVER (PARTITION BY depnameORDER BY enroll_dateDESC)AS last_emp
948+
FROM empsalary) emp
949+
WHERE first_emp=1OR last_emp=1;
950+
951+
SELECT*FROM
952+
(SELECT depname,
953+
empno,
954+
salary,
955+
enroll_date,
956+
row_number() OVER (PARTITION BY depnameORDER BY enroll_date)AS first_emp,
957+
row_number() OVER (PARTITION BY depnameORDER BY enroll_dateDESC)AS last_emp
958+
FROM empsalary) emp
959+
WHERE first_emp=1OR last_emp=1;
960+
939961
-- cleanup
940962
DROPTABLE empsalary;
941963

0 commit comments

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