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

Commit3e9abd2

Browse files
committed
Teach remove_unused_subquery_outputs about window run conditions
9d9c02c added code to allow the executor to take shortcuts when qualson monotonic window functions guaranteed that once the qual became falseit could never become true again. When possible, baserestrictinfo qualsare converted to become these quals, which we call run conditions.Unfortunately, in9d9c02c, I forgot to updateremove_unused_subquery_outputs to teach it about these run conditions.This could cause a WindowFunc column which was unused in the target listbut referenced by an upper-level WHERE clause to be removed from thesubquery when the qual in the WHERE clause was converted into a window runcondition. Because of this, the entire WindowClause would be removed fromthe query resulting in additional rows making it into the resultset whenthey should have been filtered out by the WHERE clause.Here we fix this by recording which target list items in the subquery haverun conditions. That gets passed along to remove_unused_subquery_outputsto tell it not to remove these items from the target list.Bug: #17495Reported-by: Jeremy EvansReviewed-by: Richard GuoDiscussion:https://postgr.es/m/17495-7ffe2fa0b261b9fa@postgresql.org
1 parent2b65de7 commit3e9abd2

File tree

4 files changed

+69
-11
lines changed

4 files changed

+69
-11
lines changed

‎src/backend/optimizer/path/allpaths.c

Lines changed: 39 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -142,7 +142,8 @@ static void subquery_push_qual(Query *subquery,
142142
RangeTblEntry*rte,Indexrti,Node*qual);
143143
staticvoidrecurse_push_qual(Node*setOp,Query*topquery,
144144
RangeTblEntry*rte,Indexrti,Node*qual);
145-
staticvoidremove_unused_subquery_outputs(Query*subquery,RelOptInfo*rel);
145+
staticvoidremove_unused_subquery_outputs(Query*subquery,RelOptInfo*rel,
146+
Bitmapset*extra_used_attrs);
146147

147148

148149
/*
@@ -2177,14 +2178,16 @@ has_multiple_baserels(PlannerInfo *root)
21772178
* the run condition will handle all of the required filtering.
21782179
*
21792180
* Returns true if 'opexpr' was found to be useful and was added to the
2180-
* WindowClauses runCondition. We also set *keep_original accordingly.
2181+
* WindowClauses runCondition. We also set *keep_original accordingly and add
2182+
* 'attno' to *run_cond_attrs offset by FirstLowInvalidHeapAttributeNumber.
21812183
* If the 'opexpr' cannot be used then we set *keep_original to true and
21822184
* return false.
21832185
*/
21842186
staticbool
21852187
find_window_run_conditions(Query*subquery,RangeTblEntry*rte,Indexrti,
21862188
AttrNumberattno,WindowFunc*wfunc,OpExpr*opexpr,
2187-
boolwfunc_left,bool*keep_original)
2189+
boolwfunc_left,bool*keep_original,
2190+
Bitmapset**run_cond_attrs)
21882191
{
21892192
Oidprosupport;
21902193
Expr*otherexpr;
@@ -2356,6 +2359,9 @@ find_window_run_conditions(Query *subquery, RangeTblEntry *rte, Index rti,
23562359

23572360
wclause->runCondition=lappend(wclause->runCondition,newexpr);
23582361

2362+
/* record that this attno was used in a run condition */
2363+
*run_cond_attrs=bms_add_member(*run_cond_attrs,
2364+
attno-FirstLowInvalidHeapAttributeNumber);
23592365
return true;
23602366
}
23612367

@@ -2369,13 +2375,17 @@ find_window_run_conditions(Query *subquery, RangeTblEntry *rte, Index rti,
23692375
*WindowClause as a 'runCondition' qual. These, when present, allow
23702376
*some unnecessary work to be skipped during execution.
23712377
*
2378+
* 'run_cond_attrs' will be populated with all targetlist resnos of subquery
2379+
* targets (offset by FirstLowInvalidHeapAttributeNumber) that we pushed
2380+
* window quals for.
2381+
*
23722382
* Returns true if the caller still must keep the original qual or false if
23732383
* the caller can safely ignore the original qual because the WindowAgg node
23742384
* will use the runCondition to stop returning tuples.
23752385
*/
23762386
staticbool
23772387
check_and_push_window_quals(Query*subquery,RangeTblEntry*rte,Indexrti,
2378-
Node*clause)
2388+
Node*clause,Bitmapset**run_cond_attrs)
23792389
{
23802390
OpExpr*opexpr= (OpExpr*)clause;
23812391
boolkeep_original= true;
@@ -2403,7 +2413,8 @@ check_and_push_window_quals(Query *subquery, RangeTblEntry *rte, Index rti,
24032413
WindowFunc*wfunc= (WindowFunc*)tle->expr;
24042414

24052415
if (find_window_run_conditions(subquery,rte,rti,tle->resno,wfunc,
2406-
opexpr, true,&keep_original))
2416+
opexpr, true,&keep_original,
2417+
run_cond_attrs))
24072418
returnkeep_original;
24082419
}
24092420

@@ -2415,7 +2426,8 @@ check_and_push_window_quals(Query *subquery, RangeTblEntry *rte, Index rti,
24152426
WindowFunc*wfunc= (WindowFunc*)tle->expr;
24162427

24172428
if (find_window_run_conditions(subquery,rte,rti,tle->resno,wfunc,
2418-
opexpr, false,&keep_original))
2429+
opexpr, false,&keep_original,
2430+
run_cond_attrs))
24192431
returnkeep_original;
24202432
}
24212433

@@ -2444,6 +2456,7 @@ set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel,
24442456
pushdown_safety_infosafetyInfo;
24452457
doubletuple_fraction;
24462458
RelOptInfo*sub_final_rel;
2459+
Bitmapset*run_cond_attrs=NULL;
24472460
ListCell*lc;
24482461

24492462
/*
@@ -2526,7 +2539,8 @@ set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel,
25262539
* it might be useful to use for the WindowAgg's runCondition.
25272540
*/
25282541
if (!subquery->hasWindowFuncs||
2529-
check_and_push_window_quals(subquery,rte,rti,clause))
2542+
check_and_push_window_quals(subquery,rte,rti,clause,
2543+
&run_cond_attrs))
25302544
{
25312545
/*
25322546
* subquery has no window funcs or the clause is not a
@@ -2545,9 +2559,11 @@ set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel,
25452559

25462560
/*
25472561
* The upper query might not use all the subquery's output columns; if
2548-
* not, we can simplify.
2562+
* not, we can simplify. Pass the attributes that were pushed down into
2563+
* WindowAgg run conditions to ensure we don't accidentally think those
2564+
* are unused.
25492565
*/
2550-
remove_unused_subquery_outputs(subquery,rel);
2566+
remove_unused_subquery_outputs(subquery,rel,run_cond_attrs);
25512567

25522568
/*
25532569
* We can safely pass the outer tuple_fraction down to the subquery if the
@@ -3945,16 +3961,28 @@ recurse_push_qual(Node *setOp, Query *topquery,
39453961
* compute expressions, but because deletion of output columns might allow
39463962
* optimizations such as join removal to occur within the subquery.
39473963
*
3964+
* extra_used_attrs can be passed as non-NULL to mark any columns (offset by
3965+
* FirstLowInvalidHeapAttributeNumber) that we should not remove. This
3966+
* parameter is modifed by the function, so callers must make a copy if they
3967+
* need to use the passed in Bitmapset after calling this function.
3968+
*
39483969
* To avoid affecting column numbering in the targetlist, we don't physically
39493970
* remove unused tlist entries, but rather replace their expressions with NULL
39503971
* constants. This is implemented by modifying subquery->targetList.
39513972
*/
39523973
staticvoid
3953-
remove_unused_subquery_outputs(Query*subquery,RelOptInfo*rel)
3974+
remove_unused_subquery_outputs(Query*subquery,RelOptInfo*rel,
3975+
Bitmapset*extra_used_attrs)
39543976
{
3955-
Bitmapset*attrs_used=NULL;
3977+
Bitmapset*attrs_used;
39563978
ListCell*lc;
39573979

3980+
/*
3981+
* Just point directly to extra_used_attrs. No need to bms_copy as none of
3982+
* the current callers use the Bitmapset after calling this function.
3983+
*/
3984+
attrs_used=extra_used_attrs;
3985+
39583986
/*
39593987
* Do nothing if subquery has UNION/INTERSECT/EXCEPT: in principle we
39603988
* could update all the child SELECTs' tlists, but it seems not worth the

‎src/backend/parser/parse_clause.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2831,6 +2831,7 @@ transformWindowDefinitions(ParseState *pstate,
28312831
rangeopfamily,rangeopcintype,
28322832
&wc->endInRangeFunc,
28332833
windef->endOffset);
2834+
wc->runCondition=NIL;
28342835
wc->winref=winref;
28352836

28362837
result=lappend(result,wc);

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

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3589,6 +3589,25 @@ WHERE rn < 3;
35893589
3 | sales | 2
35903590
(6 rows)
35913591

3592+
-- ensure that "unused" subquery columns are not removed when the column only
3593+
-- exists in the run condition
3594+
EXPLAIN (COSTS OFF)
3595+
SELECT empno, depname FROM
3596+
(SELECT empno,
3597+
depname,
3598+
row_number() OVER (PARTITION BY depname ORDER BY empno) rn
3599+
FROM empsalary) emp
3600+
WHERE rn < 3;
3601+
QUERY PLAN
3602+
------------------------------------------------------------
3603+
Subquery Scan on emp
3604+
-> WindowAgg
3605+
Run Condition: (row_number() OVER (?) < 3)
3606+
-> Sort
3607+
Sort Key: empsalary.depname, empsalary.empno
3608+
-> Seq Scan on empsalary
3609+
(6 rows)
3610+
35923611
-- likewise with count(empno) instead of row_number()
35933612
EXPLAIN (COSTS OFF)
35943613
SELECT * FROM

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

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1121,6 +1121,16 @@ SELECT * FROM
11211121
FROM empsalary) emp
11221122
WHERE rn<3;
11231123

1124+
-- ensure that "unused" subquery columns are not removed when the column only
1125+
-- exists in the run condition
1126+
EXPLAIN (COSTS OFF)
1127+
SELECT empno, depnameFROM
1128+
(SELECT empno,
1129+
depname,
1130+
row_number() OVER (PARTITION BY depnameORDER BY empno) rn
1131+
FROM empsalary) emp
1132+
WHERE rn<3;
1133+
11241134
-- likewise with count(empno) instead of row_number()
11251135
EXPLAIN (COSTS OFF)
11261136
SELECT*FROM

0 commit comments

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