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

Commitd222585

Browse files
committed
Allow pushdown of WHERE quals into subqueries with window functions.
We can allow this even without any specific knowledge of the semanticsof the window function, so long as pushed-down quals will either acceptevery row in a given window partition, or reject every such row. Becausewindow functions act only within a partition, such a case can't resultin changing the window functions' outputs for any surviving row.Eliminating entire partitions in this way obviously can reduce the costof the window-function computations substantially.The fly in the ointment is that it's hard to be entirely sure whetherthis is true for an arbitrary qual condition. This patch allows pushdownif (a) the qual references only partitioning columns, and (b) the qualcontains no volatile functions. We are at risk of incorrect results ifthe qual can produce different answers for values that the partitioningequality operator sees as equal. While it's not hard to invent casesfor which that can happen, it seems to seldom be a problem in practice,since no one has complained about a similar assumption that we've hadfor many years with respect to DISTINCT. The potential performancegains seem to be worth the risk.David Rowley, reviewed by Vik Fearing; some credit is due also toThomas Mayer who did considerable preliminary investigation.
1 parentf741300 commitd222585

File tree

3 files changed

+128
-12
lines changed

3 files changed

+128
-12
lines changed

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

Lines changed: 67 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -103,6 +103,7 @@ static void check_output_expressions(Query *subquery,
103103
pushdown_safety_info*safetyInfo);
104104
staticvoidcompare_tlist_datatypes(List*tlist,List*colTypes,
105105
pushdown_safety_info*safetyInfo);
106+
staticbooltargetIsInAllPartitionLists(TargetEntry*tle,Query*query);
106107
staticboolqual_is_pushdown_safe(Query*subquery,Indexrti,Node*qual,
107108
pushdown_safety_info*safetyInfo);
108109
staticvoidsubquery_push_qual(Query*subquery,
@@ -1688,20 +1689,23 @@ standard_join_search(PlannerInfo *root, int levels_needed, List *initial_rels)
16881689
* 1. If the subquery has a LIMIT clause, we must not push down any quals,
16891690
* since that could change the set of rows returned.
16901691
*
1691-
* 2. If the subquery contains any window functions, we can't push quals
1692-
* into it, because that could change the results.
1693-
*
1694-
* 3. If the subquery contains EXCEPT or EXCEPT ALL set ops we cannot push
1692+
* 2. If the subquery contains EXCEPT or EXCEPT ALL set ops we cannot push
16951693
* quals into it, because that could change the results.
16961694
*
1697-
*4. If the subquery uses DISTINCT, we cannot push volatile quals into it.
1695+
*3. If the subquery uses DISTINCT, we cannot push volatile quals into it.
16981696
* This is because upper-level quals should semantically be evaluated only
16991697
* once per distinct row, not once per original row, and if the qual is
17001698
* volatile then extra evaluations could change the results. (This issue
17011699
* does not apply to other forms of aggregation such as GROUP BY, because
17021700
* when those are present we push into HAVING not WHERE, so that the quals
17031701
* are still applied after aggregation.)
17041702
*
1703+
* 4. If the subquery contains window functions, we cannot push volatile quals
1704+
* into it. The issue here is a bit different from DISTINCT: a volatile qual
1705+
* might succeed for some rows of a window partition and fail for others,
1706+
* thereby changing the partition contents and thus the window functions'
1707+
* results for rows that remain.
1708+
*
17051709
* In addition, we make several checks on the subquery's output columns to see
17061710
* if it is safe to reference them in pushed-down quals. If output column k
17071711
* is found to be unsafe to reference, we set safetyInfo->unsafeColumns[k]
@@ -1723,6 +1727,18 @@ standard_join_search(PlannerInfo *root, int levels_needed, List *initial_rels)
17231727
* more than we save by eliminating rows before the DISTINCT step. But it
17241728
* would be very hard to estimate that at this stage, and in practice pushdown
17251729
* seldom seems to make things worse, so we ignore that problem too.
1730+
*
1731+
* Note: likewise, pushing quals into a subquery with window functions is a
1732+
* bit dubious: the quals might remove some rows of a window partition while
1733+
* leaving others, causing changes in the window functions' results for the
1734+
* surviving rows. We insist that such a qual reference only partitioning
1735+
* columns, but again that only protects us if the qual does not distinguish
1736+
* values that the partitioning equality operator sees as equal. The risks
1737+
* here are perhaps larger than for DISTINCT, since no de-duplication of rows
1738+
* occurs and thus there is no theoretical problem with such a qual. But
1739+
* we'll do this anyway because the potential performance benefits are very
1740+
* large, and we've seen no field complaints about the longstanding comparable
1741+
* behavior with DISTINCT.
17261742
*/
17271743
staticbool
17281744
subquery_is_pushdown_safe(Query*subquery,Query*topquery,
@@ -1734,12 +1750,8 @@ subquery_is_pushdown_safe(Query *subquery, Query *topquery,
17341750
if (subquery->limitOffset!=NULL||subquery->limitCount!=NULL)
17351751
return false;
17361752

1737-
/* Check point 2 */
1738-
if (subquery->hasWindowFuncs)
1739-
return false;
1740-
1741-
/* Check point 4 */
1742-
if (subquery->distinctClause)
1753+
/* Check points 3 and 4 */
1754+
if (subquery->distinctClause||subquery->hasWindowFuncs)
17431755
safetyInfo->unsafeVolatile= true;
17441756

17451757
/*
@@ -1795,7 +1807,7 @@ recurse_pushdown_safe(Node *setOp, Query *topquery,
17951807
{
17961808
SetOperationStmt*op= (SetOperationStmt*)setOp;
17971809

1798-
/* EXCEPT is no good (point3 for subquery_is_pushdown_safe) */
1810+
/* EXCEPT is no good (point2 for subquery_is_pushdown_safe) */
17991811
if (op->op==SETOP_EXCEPT)
18001812
return false;
18011813
/* Else recurse */
@@ -1835,6 +1847,15 @@ recurse_pushdown_safe(Node *setOp, Query *topquery,
18351847
* there are no non-DISTINCT output columns, so we needn't check. Note that
18361848
* subquery_is_pushdown_safe already reported that we can't use volatile
18371849
* quals if there's DISTINCT or DISTINCT ON.)
1850+
*
1851+
* 4. If the subquery has any window functions, we must not push down quals
1852+
* that reference any output columns that are not listed in all the subquery's
1853+
* window PARTITION BY clauses. We can push down quals that use only
1854+
* partitioning columns because they should succeed or fail identically for
1855+
* every row of any one window partition, and totally excluding some
1856+
* partitions will not change a window function's results for remaining
1857+
* partitions. (Again, this also requires nonvolatile quals, but
1858+
* subquery_is_pushdown_safe handles that.)
18381859
*/
18391860
staticvoid
18401861
check_output_expressions(Query*subquery,pushdown_safety_info*safetyInfo)
@@ -1874,6 +1895,15 @@ check_output_expressions(Query *subquery, pushdown_safety_info *safetyInfo)
18741895
safetyInfo->unsafeColumns[tle->resno]= true;
18751896
continue;
18761897
}
1898+
1899+
/* If subquery uses window functions, check point 4 */
1900+
if (subquery->hasWindowFuncs&&
1901+
!targetIsInAllPartitionLists(tle,subquery))
1902+
{
1903+
/* not present in all PARTITION BY clauses, so mark it unsafe */
1904+
safetyInfo->unsafeColumns[tle->resno]= true;
1905+
continue;
1906+
}
18771907
}
18781908
}
18791909

@@ -1917,6 +1947,31 @@ compare_tlist_datatypes(List *tlist, List *colTypes,
19171947
elog(ERROR,"wrong number of tlist entries");
19181948
}
19191949

1950+
/*
1951+
* targetIsInAllPartitionLists
1952+
*True if the TargetEntry is listed in the PARTITION BY clause
1953+
*of every window defined in the query.
1954+
*
1955+
* It would be safe to ignore windows not actually used by any window
1956+
* function, but it's not easy to get that info at this stage; and it's
1957+
* unlikely to be useful to spend any extra cycles getting it, since
1958+
* unreferenced window definitions are probably infrequent in practice.
1959+
*/
1960+
staticbool
1961+
targetIsInAllPartitionLists(TargetEntry*tle,Query*query)
1962+
{
1963+
ListCell*lc;
1964+
1965+
foreach(lc,query->windowClause)
1966+
{
1967+
WindowClause*wc= (WindowClause*)lfirst(lc);
1968+
1969+
if (!targetIsInSortList(tle,InvalidOid,wc->partitionClause))
1970+
return false;
1971+
}
1972+
return true;
1973+
}
1974+
19201975
/*
19211976
* qual_is_pushdown_safe - is a particular qual safe to push down?
19221977
*

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

Lines changed: 41 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1034,6 +1034,47 @@ FROM empsalary GROUP BY depname;
10341034
25100 | 1 | 22600 | develop
10351035
(3 rows)
10361036

1037+
-- Test pushdown of quals into a subquery containing window functions
1038+
-- pushdown is safe because all PARTITION BY clauses include depname:
1039+
EXPLAIN (COSTS OFF)
1040+
SELECT * FROM
1041+
(SELECT depname,
1042+
sum(salary) OVER (PARTITION BY depname) depsalary,
1043+
min(salary) OVER (PARTITION BY depname || 'A', depname) depminsalary
1044+
FROM empsalary) emp
1045+
WHERE depname = 'sales';
1046+
QUERY PLAN
1047+
---------------------------------------------------------------------
1048+
Subquery Scan on emp
1049+
-> WindowAgg
1050+
-> Sort
1051+
Sort Key: (((empsalary.depname)::text || 'A'::text))
1052+
-> WindowAgg
1053+
-> Seq Scan on empsalary
1054+
Filter: ((depname)::text = 'sales'::text)
1055+
(7 rows)
1056+
1057+
-- pushdown is unsafe because there's a PARTITION BY clause without depname:
1058+
EXPLAIN (COSTS OFF)
1059+
SELECT * FROM
1060+
(SELECT depname,
1061+
sum(salary) OVER (PARTITION BY enroll_date) enroll_salary,
1062+
min(salary) OVER (PARTITION BY depname) depminsalary
1063+
FROM empsalary) emp
1064+
WHERE depname = 'sales';
1065+
QUERY PLAN
1066+
-----------------------------------------------------------
1067+
Subquery Scan on emp
1068+
Filter: ((emp.depname)::text = 'sales'::text)
1069+
-> WindowAgg
1070+
-> Sort
1071+
Sort Key: empsalary.depname
1072+
-> WindowAgg
1073+
-> Sort
1074+
Sort Key: empsalary.enroll_date
1075+
-> Seq Scan on empsalary
1076+
(9 rows)
1077+
10371078
-- cleanup
10381079
DROP TABLE empsalary;
10391080
-- test user-defined window function with named args and default args

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

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -272,6 +272,26 @@ SELECT sum(salary), row_number() OVER (ORDER BY depname), sum(
272272
depname
273273
FROM empsalaryGROUP BY depname;
274274

275+
-- Test pushdown of quals into a subquery containing window functions
276+
277+
-- pushdown is safe because all PARTITION BY clauses include depname:
278+
EXPLAIN (COSTS OFF)
279+
SELECT*FROM
280+
(SELECT depname,
281+
sum(salary) OVER (PARTITION BY depname) depsalary,
282+
min(salary) OVER (PARTITION BY depname||'A', depname) depminsalary
283+
FROM empsalary) emp
284+
WHERE depname='sales';
285+
286+
-- pushdown is unsafe because there's a PARTITION BY clause without depname:
287+
EXPLAIN (COSTS OFF)
288+
SELECT*FROM
289+
(SELECT depname,
290+
sum(salary) OVER (PARTITION BY enroll_date) enroll_salary,
291+
min(salary) OVER (PARTITION BY depname) depminsalary
292+
FROM empsalary) emp
293+
WHERE depname='sales';
294+
275295
-- cleanup
276296
DROPTABLE empsalary;
277297

0 commit comments

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