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

Commit9626068

Browse files
author
Richard Guo
committed
Avoid unnecessary post-sort projection
When generating paths for the ORDER BY clause, one thing we need toensure is that the output paths project the correct final_target. Toachieve this, in create_ordered_paths, we compare the pathtarget ofeach generated path with the given 'target', and add a post-sortprojection step if the two targets do not match.Currently we perform a simple pointer comparison between the twotargets. It turns out that this is not sufficient. Each sorted_pathgenerated in create_ordered_paths initially projects the correcttarget required by the preceding steps of sort. If it is the samepointer as sort_input_target, pointer comparison suffices, becausesort_input_target is always identical to final_target when nopost-sort projection is needed.However, sorted_path's initial pathtarget may not be the same pointeras sort_input_target, because in apply_scanjoin_target_to_paths, ifthe target to be applied has the same expressions as the existingreltarget, we only inject the sortgroupref info into the existingpathtargets, rather than create new projection paths. As a result,pointer comparison in create_ordered_paths is not reliable.Instead, we can compare PathTarget.exprs to determine whether aprojection step is needed. If the expressions match, we can beconfident that a post-sort projection is not required.It could be argued that this change adds extra check cost each time wedecide whether a post-sort projection is needed. However, asexplained in apply_scanjoin_target_to_paths, by avoiding the creationof projection paths, we save effort both immediately and at plancreation time. This, I think, justifies the extra check cost.There are two ensuing plan changes in the regression tests, but theylook reasonable and are exactly what we are fixing here. So noadditional test cases are added.No backpatch as this could result in plan changes.Author: Richard GuoReviewed-by: Peter Eisentraut, David Rowley, Tom LaneDiscussion:https://postgr.es/m/CAMbWs48TosSvmnz88663_2yg3hfeOFss-J2PtnENDH6J_rLnRQ@mail.gmail.com
1 parent4f11245 commit9626068

File tree

2 files changed

+22
-18
lines changed

2 files changed

+22
-18
lines changed

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

Lines changed: 10 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -5300,8 +5300,11 @@ create_ordered_paths(PlannerInfo *root,
53005300
limit_tuples);
53015301
}
53025302

5303-
/* Add projection step if needed */
5304-
if (sorted_path->pathtarget!=target)
5303+
/*
5304+
* If the pathtarget of the result path has different expressions from
5305+
* the target to be applied, a projection step is needed.
5306+
*/
5307+
if (!equal(sorted_path->pathtarget->exprs,target->exprs))
53055308
sorted_path=apply_projection_to_path(root,ordered_rel,
53065309
sorted_path,target);
53075310

@@ -5378,8 +5381,11 @@ create_ordered_paths(PlannerInfo *root,
53785381
root->sort_pathkeys,NULL,
53795382
&total_groups);
53805383

5381-
/* Add projection step if needed */
5382-
if (sorted_path->pathtarget!=target)
5384+
/*
5385+
* If the pathtarget of the result path has different expressions
5386+
* from the target to be applied, a projection step is needed.
5387+
*/
5388+
if (!equal(sorted_path->pathtarget->exprs,target->exprs))
53835389
sorted_path=apply_projection_to_path(root,ordered_rel,
53845390
sorted_path,target);
53855391

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

Lines changed: 12 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -81,13 +81,12 @@ select distinct on (1) floor(random()) as r, f1 from int4_tbl order by 1,2;
8181
EXPLAIN (COSTS OFF)
8282
SELECT DISTINCT ON (four) four,two
8383
FROM tenk1 WHERE four = 0 ORDER BY 1;
84-
QUERY PLAN
85-
----------------------------------
86-
Result
87-
-> Limit
88-
-> Seq Scan on tenk1
89-
Filter: (four = 0)
90-
(4 rows)
84+
QUERY PLAN
85+
----------------------------
86+
Limit
87+
-> Seq Scan on tenk1
88+
Filter: (four = 0)
89+
(3 rows)
9190

9291
-- and check the result of the above query is correct
9392
SELECT DISTINCT ON (four) four,two
@@ -115,11 +114,10 @@ SELECT DISTINCT ON (four) four,two
115114
EXPLAIN (COSTS OFF)
116115
SELECT DISTINCT ON (four) four,hundred
117116
FROM tenk1 WHERE four = 0 ORDER BY 1,2;
118-
QUERY PLAN
119-
-----------------------------------------------------
120-
Result
121-
-> Limit
122-
-> Index Scan using tenk1_hundred on tenk1
123-
Filter: (four = 0)
124-
(4 rows)
117+
QUERY PLAN
118+
-----------------------------------------------
119+
Limit
120+
-> Index Scan using tenk1_hundred on tenk1
121+
Filter: (four = 0)
122+
(3 rows)
125123

0 commit comments

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