This is an automated email from the ASF dual-hosted git repository.
maxyang pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
The following commit(s) were added to refs/heads/main by this push:
new f4ad0f77e7 ORCA: Support two phase window functions
f4ad0f77e7 is described below
commit f4ad0f77e7994d93123a87c533532c252f7c8d05
Author: jiaqizho <[email protected]>
AuthorDate: Wed May 28 11:59:36 2025 +0800
ORCA: Support two phase window functions
For TPC-DS 67 queries, due to data skew, most of the data will be motioned
to limited segments for processing.
If data is filtered before gathering, it can significantly reduce the
amount of data that upstream nodes need to
process and improve query performance. So we introduce two-phase WindowAgg
for the case:
explain SELECT * FROM (SELECT rank() OVER (PARTITION BY four ORDER BY ten)
AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 3;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..0.00 rows=5 width=16)
-> Result (cost=0.00..0.00 rows=2 width=16)
Filter: ((rank() OVER (?)) < 3)
-> WindowAgg (cost=0.00..0.00 rows=2 width=16)
Partition By: four
Order By: ten
-> Sort (cost=0.00..6.00 rows=2 width=8)
Sort Key: four, ten
-> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.00..6.00 rows=2 width=8)
Hash Key: four
-> Result (cost=0.00..6.00 rows=2 width=8)
Filter: ((rank() OVER (?)) < 3)
-> WindowAgg (cost=0.00..6.00 rows=4
width=16)
Partition By: four
Order By: ten
-> Sort (cost=0.00..6.00 rows=4
width=8)
Sort Key: four, ten
-> Index Scan using
tenk1_unique2 on tenk1 (cost=0.00..6.00 rows=4 width=8)
Index Cond: (unique2 <
10)
Optimizer: GPORCA
(20 rows)
---
src/backend/gpopt/config/CConfigParamMapping.cpp | 5 +
src/backend/gpopt/utils/COptTasks.cpp | 2 +-
.../gporca/libgpdbcost/src/CCostModelGPDB.cpp | 11 +-
.../gporca/libgpopt/include/gpopt/base/CUtils.h | 10 +-
.../libgpopt/include/gpopt/base/CWindowOids.h | 27 +-
.../include/gpopt/operators/CExpressionHandle.h | 7 +
.../gpopt/operators/CLogicalSequenceProject.h | 20 +-
.../libgpopt/include/gpopt/operators/COperator.h | 13 +
.../gpopt/operators/CPhysicalSequenceProject.h | 14 +-
.../gporca/libgpopt/include/gpopt/xforms/CXform.h | 7 +-
.../include/gpopt/xforms/CXformSplitWindowFunc.h | 115 +++++
.../gporca/libgpopt/include/gpopt/xforms/xforms.h | 1 +
src/backend/gporca/libgpopt/src/base/CUtils.cpp | 12 +-
.../gporca/libgpopt/src/base/CWindowOids.cpp | 36 +-
.../libgpopt/src/operators/CLogicalSelect.cpp | 3 +-
.../src/operators/CLogicalSequenceProject.cpp | 47 +-
.../src/operators/CPhysicalSequenceProject.cpp | 13 +-
.../libgpopt/src/optimizer/COptimizerConfig.cpp | 2 +
.../src/translate/CTranslatorDXLToExpr.cpp | 15 +-
.../gporca/libgpopt/src/xforms/CXformFactory.cpp | 1 +
.../src/xforms/CXformImplementSequenceProject.cpp | 5 +-
.../libgpopt/src/xforms/CXformSplitGbAgg.cpp | 4 +-
.../libgpopt/src/xforms/CXformSplitWindowFunc.cpp | 393 +++++++++++++++++
.../libgpopt/src/xforms/CXformSubqueryUnnest.cpp | 5 +-
.../gporca/libgpopt/src/xforms/CXformUtils.cpp | 3 +-
src/backend/gporca/libgpopt/src/xforms/Makefile | 1 +
.../include/naucrates/dxl/xml/dxltokens.h | 1 +
.../include/naucrates/traceflags/traceflags.h | 3 +
.../src/parser/CParseHandlerWindowOids.cpp | 5 +-
.../gporca/libnaucrates/src/xml/dxltokens.cpp | 1 +
.../gporca/server/src/unittest/CTestUtils.cpp | 2 +-
src/backend/utils/misc/guc_gp.c | 12 +
src/include/utils/guc.h | 1 +
src/include/utils/unsync_guc_name.h | 1 +
src/test/regress/expected/window.out | 440 +++++++++++++++++++
src/test/regress/expected/window_optimizer.out | 484 ++++++++++++++++++++-
src/test/regress/sql/window.sql | 42 ++
37 files changed, 1718 insertions(+), 46 deletions(-)
diff --git a/src/backend/gpopt/config/CConfigParamMapping.cpp
b/src/backend/gpopt/config/CConfigParamMapping.cpp
index c19558aeb1..b69d01e407 100644
--- a/src/backend/gpopt/config/CConfigParamMapping.cpp
+++ b/src/backend/gpopt/config/CConfigParamMapping.cpp
@@ -265,6 +265,11 @@ CConfigParamMapping::SConfigMappingElem
CConfigParamMapping::m_elements[] = {
false, // m_negate_param
GPOS_WSZ_LIT(
"Always pick a plan for aggregate distinct that minimizes
skew.")},
+
+ {EopttraceForceSplitWindowFunc, &optimizer_force_split_window_function,
+ false, // m_negate_param
+ GPOS_WSZ_LIT(
+ "Always split the window function.")},
{EopttraceEnableEagerAgg, &optimizer_enable_eageragg,
false, // m_negate_param
diff --git a/src/backend/gpopt/utils/COptTasks.cpp
b/src/backend/gpopt/utils/COptTasks.cpp
index 47576d77e8..0f5aadbd82 100644
--- a/src/backend/gpopt/utils/COptTasks.cpp
+++ b/src/backend/gpopt/utils/COptTasks.cpp
@@ -403,7 +403,7 @@ COptTasks::CreateOptimizerConfig(CMemoryPool *mp,
ICostModel *cost_model,
push_group_by_below_setop_threshold,
xform_bind_threshold,
skew_factor),
plan_hints,
- GPOS_NEW(mp) CWindowOids(OID(F_ROW_NUMBER), OID(F_RANK_)));
+ GPOS_NEW(mp) CWindowOids(mp, OID(F_ROW_NUMBER), OID(F_RANK_),
OID(F_DENSE_RANK_)));
}
//---------------------------------------------------------------------------
diff --git a/src/backend/gporca/libgpdbcost/src/CCostModelGPDB.cpp
b/src/backend/gporca/libgpdbcost/src/CCostModelGPDB.cpp
index 2bc469084b..a91fc4283f 100644
--- a/src/backend/gporca/libgpdbcost/src/CCostModelGPDB.cpp
+++ b/src/backend/gporca/libgpdbcost/src/CCostModelGPDB.cpp
@@ -1594,12 +1594,19 @@ CCostModelGPDB::CostSequenceProject(CMemoryPool *mp,
CExpressionHandle &exprhdl,
GPOS_ASSERT(COperator::EopPhysicalSequenceProject ==
exprhdl.Pop()->Eopid());
+ CPhysicalSequenceProject *psp =
CPhysicalSequenceProject::PopConvert(exprhdl.Pop());
+
+ if (GPOS_FTRACE(EopttraceForceSplitWindowFunc) &&
+ psp->Pspt() == COperator::EsptypeGlobalTwoStep) {
+ return CCost(0);
+ }
+
const DOUBLE num_rows_outer = pci->PdRows()[0];
const DOUBLE dWidthOuter = pci->GetWidth()[0];
ULONG ulSortCols = 0;
COrderSpecArray *pdrgpos =
- CPhysicalSequenceProject::PopConvert(exprhdl.Pop())->Pdrgpos();
+ CPhysicalSequenceProject::PopConvert(psp)->Pdrgpos();
const ULONG ulOrderSpecs = pdrgpos->Size();
for (ULONG ul = 0; ul < ulOrderSpecs; ul++)
{
@@ -1619,7 +1626,7 @@ CCostModelGPDB::CostSequenceProject(CMemoryPool *mp,
CExpressionHandle &exprhdl,
dTupDefaultProcCostUnit));
CCost costChild =
CostChildren(mp, exprhdl, pci, pcmgpdb->GetCostModelParams());
-
+
return costLocal + costChild;
}
diff --git a/src/backend/gporca/libgpopt/include/gpopt/base/CUtils.h
b/src/backend/gporca/libgpopt/include/gpopt/base/CUtils.h
index 2a756c09e7..b1b55e29ea 100644
--- a/src/backend/gporca/libgpopt/include/gpopt/base/CUtils.h
+++ b/src/backend/gporca/libgpopt/include/gpopt/base/CUtils.h
@@ -320,12 +320,10 @@ public:
BOOL fNewComputedCol);
// generate a sequence project expression
- static CExpression *PexprLogicalSequenceProject(CMemoryPool *mp,
-
CDistributionSpec *pds,
-
COrderSpecArray *pdrgpos,
-
CWindowFrameArray *pdrgpwf,
-
CExpression *pexpr,
-
CExpression *pexprPrjList);
+ static CExpression *PexprLogicalSequenceProject(
+ CMemoryPool *mp, COperator::ESPType sptype, CDistributionSpec
*pds,
+ COrderSpecArray *pdrgpos, CWindowFrameArray *pdrgpwf,
+ CExpression *pexpr, CExpression *pexprPrjList);
// generate a projection of NULL constants
// to the map 'colref_mapping', and add the mappings to the
colref_mapping map if not NULL
diff --git a/src/backend/gporca/libgpopt/include/gpopt/base/CWindowOids.h
b/src/backend/gporca/libgpopt/include/gpopt/base/CWindowOids.h
index 56148b85d8..ba120eac01 100644
--- a/src/backend/gporca/libgpopt/include/gpopt/base/CWindowOids.h
+++ b/src/backend/gporca/libgpopt/include/gpopt/base/CWindowOids.h
@@ -17,10 +17,16 @@
#define DUMMY_ROW_NUMBER_OID OID(7000)
#define DUMMY_WIN_RANK OID(7001)
+#define DUMMY_WIN_DENSE_RANK OID(7002)
+namespace gpmd
+{
+class IMDId;
+}
namespace gpopt
{
+using namespace gpmd;
//---------------------------------------------------------------------------
// @class:
// CWindowOids
@@ -35,17 +41,36 @@ private:
// oid of window operation "row_number" function
OID m_oidRowNumber;
+ // metadata id of window operation "row_number" function
+ IMDId *m_MDIdRowNumber;
+
// oid of window operation "rank" function
OID m_oidRank;
+ // metadata id of window operation "rank" function
+ IMDId *m_MDIdRank;
+
+ // oid of window operation "dense_rank" function
+ OID m_oidDenseRank;
+
+ // metadata id of window operation "dense_rank" function
+ IMDId *m_MDDenseRank;
+
public:
- CWindowOids(OID row_number_oid, OID rank_oid);
+ CWindowOids(CMemoryPool *mp, OID row_number_oid, OID rank_oid,
+ OID dense_rank_oid);
// accessor of oid value of "row_number" function
OID OidRowNumber() const;
+ IMDId *MDIdRowNumber() const;
// accessor of oid value of "rank" function
OID OidRank() const;
+ IMDId *MDIdRank() const;
+
+ // accessor of oid value of "dense_rank" function
+ OID OidDenseRank() const;
+ IMDId *MDIdDenseRank() const;
// generate default window oids
static CWindowOids *GetWindowOids(CMemoryPool *mp);
diff --git
a/src/backend/gporca/libgpopt/include/gpopt/operators/CExpressionHandle.h
b/src/backend/gporca/libgpopt/include/gpopt/operators/CExpressionHandle.h
index fbef560d17..a74661cd94 100644
--- a/src/backend/gporca/libgpopt/include/gpopt/operators/CExpressionHandle.h
+++ b/src/backend/gporca/libgpopt/include/gpopt/operators/CExpressionHandle.h
@@ -173,6 +173,13 @@ public:
// stats of attached expr/gexpr
IStatistics *Pstats();
+ // pop the memory pool
+ CMemoryPool *
+ Pmp() const
+ {
+ return m_mp;
+ }
+
// required properties of attached expr/gexpr
CReqdProp *
Prp() const
diff --git
a/src/backend/gporca/libgpopt/include/gpopt/operators/CLogicalSequenceProject.h
b/src/backend/gporca/libgpopt/include/gpopt/operators/CLogicalSequenceProject.h
index 53331756d2..a05add9d30 100644
---
a/src/backend/gporca/libgpopt/include/gpopt/operators/CLogicalSequenceProject.h
+++
b/src/backend/gporca/libgpopt/include/gpopt/operators/CLogicalSequenceProject.h
@@ -33,6 +33,9 @@ class CDistributionSpec;
class CLogicalSequenceProject : public CLogicalUnary
{
private:
+ // type of sequence project
+ COperator::ESPType m_sptype;
+
// partition by keys
CDistributionSpec *m_pds;
@@ -58,8 +61,8 @@ public:
CLogicalSequenceProject(const CLogicalSequenceProject &) = delete;
// ctor
- CLogicalSequenceProject(CMemoryPool *mp, CDistributionSpec *pds,
- COrderSpecArray
*pdrgpos,
+ CLogicalSequenceProject(CMemoryPool *mp, COperator::ESPType sptype,
+ CDistributionSpec *pds,
COrderSpecArray *pdrgpos,
CWindowFrameArray
*pdrgpwf);
// ctor for pattern
@@ -82,6 +85,13 @@ public:
return "CLogicalSequenceProject";
}
+ // window type
+ COperator::ESPType
+ Pspt() const
+ {
+ return m_sptype;
+ }
+
// distribution spec
CDistributionSpec *
Pds() const
@@ -123,7 +133,8 @@ public:
BOOL must_exist) override;
// return true if we can pull projections up past this operator from
its given child
- BOOL FCanPullProjectionsUp(ULONG //child_index
+ BOOL
+ FCanPullProjectionsUp(ULONG //child_index
) const override
{
return false;
@@ -180,6 +191,9 @@ public:
// print
IOstream &OsPrint(IOstream &os) const override;
+ static IOstream &OsPrintWindowType(IOstream &os,
+
COperator::ESPType wintype);
+
// remove outer references from Order By/ Partition By clauses, and
return a new operator
CLogicalSequenceProject *PopRemoveLocalOuterRefs(
CMemoryPool *mp, CExpressionHandle &exprhdl);
diff --git a/src/backend/gporca/libgpopt/include/gpopt/operators/COperator.h
b/src/backend/gporca/libgpopt/include/gpopt/operators/COperator.h
index f9ae1074ea..272bba9092 100644
--- a/src/backend/gporca/libgpopt/include/gpopt/operators/COperator.h
+++ b/src/backend/gporca/libgpopt/include/gpopt/operators/COperator.h
@@ -266,12 +266,25 @@ public:
EopLogicalIndexOnlyGet,
EopLogicalDynamicIndexOnlyGet,
+
+ EopLogicalWindowFunc,
EopSentinel
};
+ // sequence project type
+ enum ESPType
+ {
+ EsptypeGlobalTwoStep, // global group by sequence project
+ EsptypeGlobalOneStep, // global group by sequence project
+ EsptypeLocal, // local group by sequence project
+
+ EsptypeSentinel
+ };
+
// aggregate type
enum EGbAggType
{
+ // todo(jiaqizho): change to onestep, twostep(global),
twostep(local)
EgbaggtypeGlobal, // global group by aggregate
EgbaggtypeLocal, // local group by aggregate
EgbaggtypeIntermediate, // intermediate group by aggregate
diff --git
a/src/backend/gporca/libgpopt/include/gpopt/operators/CPhysicalSequenceProject.h
b/src/backend/gporca/libgpopt/include/gpopt/operators/CPhysicalSequenceProject.h
index 1821770300..dc6afd1ecf 100644
---
a/src/backend/gporca/libgpopt/include/gpopt/operators/CPhysicalSequenceProject.h
+++
b/src/backend/gporca/libgpopt/include/gpopt/operators/CPhysicalSequenceProject.h
@@ -32,6 +32,9 @@ class CDistributionSpec;
class CPhysicalSequenceProject : public CPhysical
{
private:
+ // window type
+ ESPType m_sptype;
+
// partition by keys
CDistributionSpec *m_pds;
@@ -57,8 +60,8 @@ public:
CPhysicalSequenceProject(const CPhysicalSequenceProject &) = delete;
// ctor
- CPhysicalSequenceProject(CMemoryPool *mp, CDistributionSpec *pds,
- COrderSpecArray
*pdrgpos,
+ CPhysicalSequenceProject(CMemoryPool *mp, ESPType m_sptype,
+ CDistributionSpec
*pds, COrderSpecArray *pdrgpos,
CWindowFrameArray
*pdrgpwf);
// dtor
@@ -78,6 +81,13 @@ public:
return "CPhysicalSequenceProject";
}
+ // window type
+ COperator::ESPType
+ Pspt() const
+ {
+ return m_sptype;
+ }
+
// partition by keys
CDistributionSpec *
Pds() const
diff --git a/src/backend/gporca/libgpopt/include/gpopt/xforms/CXform.h
b/src/backend/gporca/libgpopt/include/gpopt/xforms/CXform.h
index d3a4524295..fc7a1c8c7e 100644
--- a/src/backend/gporca/libgpopt/include/gpopt/xforms/CXform.h
+++ b/src/backend/gporca/libgpopt/include/gpopt/xforms/CXform.h
@@ -25,7 +25,8 @@
#include "naucrates/traceflags/traceflags.h"
// Macro for enabling and disabling xforms
-#define GPOPT_DISABLE_XFORM_TF(x) EopttraceDisableXformBase +
static_cast<int>(x)
+#define GPOPT_DISABLE_XFORM_TF(x) \
+ EopttraceDisableXformBase + static_cast<int>(x)
#define GPOPT_ENABLE_XFORM(x) GPOS_UNSET_TRACE(GPOPT_DISABLE_XFORM_TF(x))
#define GPOPT_DISABLE_XFORM(x) GPOS_SET_TRACE(GPOPT_DISABLE_XFORM_TF(x))
#define GPOPT_FENABLED_XFORM(x) !GPOS_FTRACE(GPOPT_DISABLE_XFORM_TF(x))
@@ -236,6 +237,7 @@ public:
ExfLimit2IndexOnlyGet,
ExfFullOuterJoin2HashJoin,
ExfFullJoinCommutativity,
+ ExfSplitWindowFunc,
ExfInvalid,
ExfSentinel = ExfInvalid
};
@@ -297,7 +299,8 @@ public:
}
// check compatibility with another xform
- virtual BOOL FCompatible(CXform::EXformId)
+ virtual BOOL
+ FCompatible(CXform::EXformId)
{
return true;
}
diff --git
a/src/backend/gporca/libgpopt/include/gpopt/xforms/CXformSplitWindowFunc.h
b/src/backend/gporca/libgpopt/include/gpopt/xforms/CXformSplitWindowFunc.h
new file mode 100644
index 0000000000..71508fc024
--- /dev/null
+++ b/src/backend/gporca/libgpopt/include/gpopt/xforms/CXformSplitWindowFunc.h
@@ -0,0 +1,115 @@
+/*-------------------------------------------------------------------------
+ *
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ *
+ * @filename:
+ * CXformSplitWindowFunc.h
+ *
+ * @doc:
+ * Split a window function into pair of local and global window
function
+ *
+ *-------------------------------------------------------------------------
+ */
+#ifndef GPOPT_CXformSplitWindowFunc_H
+#define GPOPT_CXformSplitWindowFunc_H
+
+#include "gpos/base.h"
+
+#include "gpopt/xforms/CXformExploration.h"
+
+namespace gpopt
+{
+using namespace gpos;
+
+//---------------------------------------------------------------------------
+// @class:
+// CXformSplitWindowFunc
+//
+// @doc:
+// Split a window function into pair of local and global window
function
+//
+//---------------------------------------------------------------------------
+class CXformSplitWindowFunc : public CXformExploration
+{
+private:
+ // generate a project lists for the local and global window function
+ // from the original window function
+ static CExpression *PexprWindowFunc(
+ CMemoryPool *mp, // memory pool
+ CExpression *
+ pexprProjListOrig, // project list of the original
global window function
+ CExpression *
+ ppexprProjListLocal, // project list of the new local
window function
+ CExpression *
+ ppexprProjListGlobal // project list of the new global
window function
+ );
+
+ static void PopulateLocalGlobalProjectList(
+ CMemoryPool *mp, CExpression *pexprProjList,
+ CExpression **ppexprProjListLocal, CExpression
**ppexprProjListGlobal);
+
+ static bool CheckFilterAndProjectList(CExpression *pexprSelect,
+
CExpression *pexprProjList);
+
+ static void PopulateSelect(CMemoryPool *mp, CExpression *pexprSelect,
+ COperator
**ppSelectCopy);
+
+public:
+ CXformSplitWindowFunc(const CXformSplitWindowFunc &) = delete;
+
+ // ctor
+ explicit CXformSplitWindowFunc(CMemoryPool *mp);
+
+ // dtor
+ ~CXformSplitWindowFunc() override = default;
+
+ // ident accessors
+ EXformId
+ Exfid() const override
+ {
+ return ExfSplitWindowFunc;
+ }
+
+ // return a string for xform name
+ const CHAR *
+ SzId() const override
+ {
+ return "CXformSplitWindowFunc";
+ }
+
+ // Compatibility function for splitting limit
+ BOOL
+ FCompatible(CXform::EXformId exfid) override
+ {
+ return (CXform::ExfSplitWindowFunc != exfid);
+ }
+
+ // compute xform promise for a given expression handle
+ EXformPromise Exfp(CExpressionHandle &exprhdl) const override;
+
+ // actual transform
+ void Transform(CXformContext *pxfctxt, CXformResult *pxfres,
+ CExpression *pexpr) const override;
+
+}; // class CXformSplitWindowFunc
+
+} // namespace gpopt
+
+#endif // !GPOPT_CXformSplitWindowFunc_H
+
+// EOF
diff --git a/src/backend/gporca/libgpopt/include/gpopt/xforms/xforms.h
b/src/backend/gporca/libgpopt/include/gpopt/xforms/xforms.h
index 3c06b3b804..25e724c212 100644
--- a/src/backend/gporca/libgpopt/include/gpopt/xforms/xforms.h
+++ b/src/backend/gporca/libgpopt/include/gpopt/xforms/xforms.h
@@ -160,6 +160,7 @@
#include "gpopt/xforms/CXformSplitGbAgg.h"
#include "gpopt/xforms/CXformSplitGbAggDedup.h"
#include "gpopt/xforms/CXformSplitLimit.h"
+#include "gpopt/xforms/CXformSplitWindowFunc.h"
#include "gpopt/xforms/CXformSubqJoin2Apply.h"
#include "gpopt/xforms/CXformSubqNAryJoin2Apply.h"
#include "gpopt/xforms/CXformUnion2UnionAll.h"
diff --git a/src/backend/gporca/libgpopt/src/base/CUtils.cpp
b/src/backend/gporca/libgpopt/src/base/CUtils.cpp
index b3a9a5bcc0..e41a04c578 100644
--- a/src/backend/gporca/libgpopt/src/base/CUtils.cpp
+++ b/src/backend/gporca/libgpopt/src/base/CUtils.cpp
@@ -2269,7 +2269,8 @@ CUtils::PexprLogicalProject(CMemoryPool *mp, CExpression
*pexpr,
// generate a sequence project expression
CExpression *
-CUtils::PexprLogicalSequenceProject(CMemoryPool *mp, CDistributionSpec *pds,
+CUtils::PexprLogicalSequenceProject(CMemoryPool *mp, COperator::ESPType sptype,
+
CDistributionSpec *pds,
COrderSpecArray *pdrgpos,
CWindowFrameArray *pdrgpwf,
CExpression *pexpr,
@@ -2285,7 +2286,8 @@ CUtils::PexprLogicalSequenceProject(CMemoryPool *mp,
CDistributionSpec *pds,
pexprPrjList->Pop()->Eopid());
return GPOS_NEW(mp) CExpression(
- mp, GPOS_NEW(mp) CLogicalSequenceProject(mp, pds, pdrgpos,
pdrgpwf),
+ mp,
+ GPOS_NEW(mp) CLogicalSequenceProject(mp, sptype, pds, pdrgpos,
pdrgpwf),
pexpr, pexprPrjList);
}
@@ -3857,9 +3859,9 @@ CUtils::PexprFuncElemExpr(CMemoryPool *mp, CMDAccessor
*md_accessor,
CWStringConst(mp,
(cast_func->Mdname().GetMDName())->GetBuffer());
mdid_func->AddRef();
cast_func->GetResultTypeMdid()->AddRef();
- CScalarFunc *popCastScalarFunc =
- GPOS_NEW(mp) CScalarFunc(mp, mdid_func,
cast_func->GetResultTypeMdid(),
- typmod,
pstrFunc, 1 /* Explicit Cast */, false /* funcvariadic */);
+ CScalarFunc *popCastScalarFunc = GPOS_NEW(mp)
+ CScalarFunc(mp, mdid_func, cast_func->GetResultTypeMdid(),
typmod,
+ pstrFunc, 1 /* Explicit Cast */, false
/* funcvariadic */);
mdid_elem_type->AddRef();
CExpression *pexprCaseTest = GPOS_NEW(mp)
CExpression(mp, GPOS_NEW(mp) CScalarCaseTest(mp,
mdid_elem_type));
diff --git a/src/backend/gporca/libgpopt/src/base/CWindowOids.cpp
b/src/backend/gporca/libgpopt/src/base/CWindowOids.cpp
index 5afa5095e2..da744a018d 100644
--- a/src/backend/gporca/libgpopt/src/base/CWindowOids.cpp
+++ b/src/backend/gporca/libgpopt/src/base/CWindowOids.cpp
@@ -3,12 +3,19 @@
#include "gpopt/base/CWindowOids.h"
+#include "naucrates/md/CMDIdGPDB.h"
using namespace gpopt;
-CWindowOids::CWindowOids(OID row_number_oid, OID rank_oid)
+CWindowOids::CWindowOids(CMemoryPool *mp, OID row_number_oid, OID rank_oid,
+ OID dense_rank_oid)
{
m_oidRowNumber = row_number_oid;
+ m_MDIdRowNumber =
+ GPOS_NEW(mp) CMDIdGPDB(IMDId::EmdidGeneral, m_oidRowNumber);
m_oidRank = rank_oid;
+ m_MDIdRank = GPOS_NEW(mp) CMDIdGPDB(IMDId::EmdidGeneral, m_oidRank);
+ m_oidDenseRank = dense_rank_oid;
+ m_MDDenseRank = GPOS_NEW(mp) CMDIdGPDB(IMDId::EmdidGeneral,
m_oidDenseRank);
}
OID
@@ -17,14 +24,39 @@ CWindowOids::OidRowNumber() const
return m_oidRowNumber;
}
+IMDId *
+CWindowOids::MDIdRowNumber() const
+{
+ return m_MDIdRowNumber;
+}
+
OID
CWindowOids::OidRank() const
{
return m_oidRank;
}
+IMDId *
+CWindowOids::MDIdRank() const
+{
+ return m_MDIdRank;
+}
+
+OID
+CWindowOids::OidDenseRank() const
+{
+ return m_oidDenseRank;
+}
+
+IMDId *
+CWindowOids::MDIdDenseRank() const
+{
+ return m_MDDenseRank;
+}
+
CWindowOids *
CWindowOids::GetWindowOids(CMemoryPool *mp)
{
- return GPOS_NEW(mp) CWindowOids(DUMMY_ROW_NUMBER_OID, DUMMY_WIN_RANK);
+ return GPOS_NEW(mp) CWindowOids(mp, DUMMY_ROW_NUMBER_OID,
DUMMY_WIN_RANK,
+
DUMMY_WIN_DENSE_RANK);
}
diff --git a/src/backend/gporca/libgpopt/src/operators/CLogicalSelect.cpp
b/src/backend/gporca/libgpopt/src/operators/CLogicalSelect.cpp
index 38ff45ff62..2b1e7ca9d0 100644
--- a/src/backend/gporca/libgpopt/src/operators/CLogicalSelect.cpp
+++ b/src/backend/gporca/libgpopt/src/operators/CLogicalSelect.cpp
@@ -102,7 +102,8 @@ CLogicalSelect::PxfsCandidates(CMemoryPool *mp) const
(void) xform_set->ExchangeSet(CXform::ExfSelect2DynamicBitmapBoolOp);
(void) xform_set->ExchangeSet(CXform::ExfSimplifySelectWithSubquery);
(void) xform_set->ExchangeSet(CXform::ExfSelect2Filter);
-
+ if (GPOS_FTRACE(EopttraceForceSplitWindowFunc))
+ (void) xform_set->ExchangeSet(CXform::ExfSplitWindowFunc);
return xform_set;
}
diff --git
a/src/backend/gporca/libgpopt/src/operators/CLogicalSequenceProject.cpp
b/src/backend/gporca/libgpopt/src/operators/CLogicalSequenceProject.cpp
index 47666a2fbf..f0018ea02a 100644
--- a/src/backend/gporca/libgpopt/src/operators/CLogicalSequenceProject.cpp
+++ b/src/backend/gporca/libgpopt/src/operators/CLogicalSequenceProject.cpp
@@ -32,10 +32,12 @@ using namespace gpopt;
//
//---------------------------------------------------------------------------
CLogicalSequenceProject::CLogicalSequenceProject(CMemoryPool *mp,
+
COperator::ESPType sptype,
CDistributionSpec *pds,
COrderSpecArray *pdrgpos,
CWindowFrameArray *pdrgpwf)
: CLogicalUnary(mp),
+ m_sptype(sptype),
m_pds(pds),
m_pdrgpos(pdrgpos),
m_pdrgpwf(pdrgpwf),
@@ -86,6 +88,7 @@ CLogicalSequenceProject::CLogicalSequenceProject(CMemoryPool
*mp,
//---------------------------------------------------------------------------
CLogicalSequenceProject::CLogicalSequenceProject(CMemoryPool *mp)
: CLogicalUnary(mp),
+ m_sptype(COperator::ESPType::EsptypeSentinel),
m_pds(nullptr),
m_pdrgpos(nullptr),
m_pdrgpwf(nullptr),
@@ -146,7 +149,8 @@ CLogicalSequenceProject::PopCopyWithRemappedColumns(
pdrgpwf->Append(pwf);
}
- return GPOS_NEW(mp) CLogicalSequenceProject(mp, pds, pdrgpos, pdrgpwf);
+ return GPOS_NEW(mp)
+ CLogicalSequenceProject(mp, m_sptype, pds, pdrgpos, pdrgpwf);
}
@@ -317,7 +321,8 @@ CLogicalSequenceProject::Matches(COperator *pop) const
{
CLogicalSequenceProject *popLogicalSequenceProject =
CLogicalSequenceProject::PopConvert(pop);
- return m_pds->Matches(popLogicalSequenceProject->Pds()) &&
+ return m_sptype == popLogicalSequenceProject->Pspt() &&
+ m_pds->Matches(popLogicalSequenceProject->Pds()) &&
CWindowFrame::Equals(m_pdrgpwf,
popLogicalSequenceProject->Pdrgpwf()) &&
COrderSpec::Equals(m_pdrgpos,
@@ -386,6 +391,39 @@ CLogicalSequenceProject::PstatsDerive(CMemoryPool *mp,
return PstatsDeriveProject(mp, exprhdl);
}
+//---------------------------------------------------------------------------
+// @function:
+// CLogicalSequenceProject::OsPrintWindoType
+//
+// @doc:
+// Helper function to print window type
+//
+//---------------------------------------------------------------------------
+IOstream &
+CLogicalSequenceProject::OsPrintWindowType(IOstream &os,
+
COperator::ESPType wintype)
+{
+ switch (wintype)
+ {
+ case COperator::EsptypeGlobalTwoStep:
+ os << "Global(two-step)";
+ break;
+
+ case COperator::EsptypeGlobalOneStep:
+ os << "Global(one-step)";
+ break;
+
+ case COperator::EsptypeLocal:
+ os << "Local";
+ break;
+
+ default:
+ GPOS_ASSERT(!"Unsupported window type");
+ }
+ return os;
+}
+
+
//---------------------------------------------------------------------------
// @function:
// CLogicalSequenceProject::OsPrint
@@ -398,6 +436,8 @@ IOstream &
CLogicalSequenceProject::OsPrint(IOstream &os) const
{
os << SzId() << " (";
+ OsPrintWindowType(os, m_sptype);
+ os << ") (";
os << "Partition By Keys:";
(void) m_pds->OsPrint(os);
os << ", ";
@@ -452,7 +492,8 @@
CLogicalSequenceProject::PopRemoveLocalOuterRefs(CMemoryPool *mp,
// we re-use the frame edges without changing here
m_pdrgpwf->AddRef();
- return GPOS_NEW(mp) CLogicalSequenceProject(mp, pds, pdrgpos,
m_pdrgpwf);
+ return GPOS_NEW(mp)
+ CLogicalSequenceProject(mp, m_sptype, pds, pdrgpos, m_pdrgpwf);
}
// EOF
diff --git
a/src/backend/gporca/libgpopt/src/operators/CPhysicalSequenceProject.cpp
b/src/backend/gporca/libgpopt/src/operators/CPhysicalSequenceProject.cpp
index b0116cbbe7..4b663137cf 100644
--- a/src/backend/gporca/libgpopt/src/operators/CPhysicalSequenceProject.cpp
+++ b/src/backend/gporca/libgpopt/src/operators/CPhysicalSequenceProject.cpp
@@ -22,6 +22,7 @@
#include "gpopt/base/CWindowFrame.h"
#include "gpopt/cost/ICostModel.h"
#include "gpopt/operators/CExpressionHandle.h"
+#include "gpopt/operators/CLogicalSequenceProject.h"
#include "gpopt/operators/CScalarIdent.h"
using namespace gpopt;
@@ -35,10 +36,12 @@ using namespace gpopt;
//
//---------------------------------------------------------------------------
CPhysicalSequenceProject::CPhysicalSequenceProject(CMemoryPool *mp,
+
ESPType sptype,
CDistributionSpec *pds,
COrderSpecArray *pdrgpos,
CWindowFrameArray *pdrgpwf)
: CPhysical(mp),
+ m_sptype(sptype),
m_pds(pds),
m_pdrgpos(pdrgpos),
m_pdrgpwf(pdrgpwf),
@@ -209,7 +212,8 @@ CPhysicalSequenceProject::Matches(COperator *pop) const
{
CPhysicalSequenceProject *popPhysicalSequenceProject =
CPhysicalSequenceProject::PopConvert(pop);
- return m_pds->Matches(popPhysicalSequenceProject->Pds()) &&
+ return m_sptype == popPhysicalSequenceProject->Pspt() &&
+ m_pds->Matches(popPhysicalSequenceProject->Pds()) &&
CWindowFrame::Equals(m_pdrgpwf,
popPhysicalSequenceProject->Pdrgpwf()) &&
COrderSpec::Equals(m_pdrgpos,
@@ -328,6 +332,11 @@ CPhysicalSequenceProject::PdsRequired(CMemoryPool *mp,
return PdsRequireSingleton(mp, exprhdl, pdsRequired,
child_index);
}
+ if (m_sptype == COperator::EsptypeLocal)
+ {
+ return GPOS_NEW(mp) CDistributionSpecAny(this->Eopid());
+ }
+
// if there are outer references, then we need a broadcast (or a gather)
if (exprhdl.HasOuterRefs())
{
@@ -553,6 +562,8 @@ IOstream &
CPhysicalSequenceProject::OsPrint(IOstream &os) const
{
os << SzId() << " (";
+ CLogicalSequenceProject::OsPrintWindowType(os, m_sptype);
+ os << ") (";
(void) m_pds->OsPrint(os);
os << ", ";
(void) COrderSpec::OsPrint(os, m_pdrgpos);
diff --git a/src/backend/gporca/libgpopt/src/optimizer/COptimizerConfig.cpp
b/src/backend/gporca/libgpopt/src/optimizer/COptimizerConfig.cpp
index 542600a4df..e55a04862a 100644
--- a/src/backend/gporca/libgpopt/src/optimizer/COptimizerConfig.cpp
+++ b/src/backend/gporca/libgpopt/src/optimizer/COptimizerConfig.cpp
@@ -179,6 +179,8 @@ COptimizerConfig::Serialize(CMemoryPool *mp, CXMLSerializer
*xml_serializer,
m_window_oids->OidRowNumber());
xml_serializer->AddAttribute(CDXLTokens::GetDXLTokenStr(EdxltokenOidRank),
m_window_oids->OidRank());
+
xml_serializer->AddAttribute(CDXLTokens::GetDXLTokenStr(EdxltokenOidDenseRank),
+
m_window_oids->OidDenseRank());
xml_serializer->CloseElement(
CDXLTokens::GetDXLTokenStr(EdxltokenNamespacePrefix),
CDXLTokens::GetDXLTokenStr(EdxltokenWindowOids));
diff --git a/src/backend/gporca/libgpopt/src/translate/CTranslatorDXLToExpr.cpp
b/src/backend/gporca/libgpopt/src/translate/CTranslatorDXLToExpr.cpp
index 61ce62282f..bad4b1e812 100644
--- a/src/backend/gporca/libgpopt/src/translate/CTranslatorDXLToExpr.cpp
+++ b/src/backend/gporca/libgpopt/src/translate/CTranslatorDXLToExpr.cpp
@@ -1871,8 +1871,10 @@ CTranslatorDXLToExpr::PexprLogicalSeqPr(const CDXLNode
*dxlnode)
pdrgpos->Append(GPOS_NEW(m_mp) COrderSpec(m_mp));
}
+ // todo(jiaqizho): double check
CLogicalSequenceProject *popLgSequence =
- GPOS_NEW(m_mp) CLogicalSequenceProject(m_mp, pds,
pdrgpos, pdrgpwf);
+ GPOS_NEW(m_mp) CLogicalSequenceProject(
+ m_mp, COperator::ESPType::EsptypeGlobalOneStep,
pds, pdrgpos, pdrgpwf);
pexprLgSequence = GPOS_NEW(m_mp)
CExpression(m_mp, popLgSequence, pexprWindowChild,
pexprProjList);
pexprWindowChild = pexprLgSequence;
@@ -2164,9 +2166,8 @@ CTranslatorDXLToExpr::Ptabdesc(CDXLTableDescr
*table_descr)
mdid->AddRef();
CTableDescriptor *ptabdesc = GPOS_NEW(m_mp) CTableDescriptor(
m_mp, mdid, CName(m_mp, &strName),
pmdrel->ConvertHashToRandom(),
- rel_distr_policy, rel_storage_type,
- table_descr->GetExecuteAsUserId(), table_descr->LockMode(),
- table_descr->GetAclMode(),
+ rel_distr_policy, rel_storage_type,
table_descr->GetExecuteAsUserId(),
+ table_descr->LockMode(), table_descr->GetAclMode(),
table_descr->GetAssignedQueryIdForTargetRel());
const ULONG ulColumns = table_descr->Arity();
@@ -2362,7 +2363,7 @@ CTranslatorDXLToExpr::PtabdescFromCTAS(CDXLLogicalCTAS
*pdxlopCTAS)
mdid->AddRef();
CTableDescriptor *ptabdesc = GPOS_NEW(m_mp) CTableDescriptor(
m_mp, mdid, CName(m_mp, &strName),
pmdrel->ConvertHashToRandom(),
- rel_distr_policy, rel_storage_type,
+ rel_distr_policy, rel_storage_type,
0, // ulExecuteAsUser, use permissions of current user
3, // CTAS always uses a RowExclusiveLock on the table.
See createas.c
2, // CTAS always requires SELECT and SELECT only privilege
@@ -3020,8 +3021,8 @@ CTranslatorDXLToExpr::PexprScalarFunc(const CDXLNode
*pdxlnFunc)
pop = GPOS_NEW(m_mp) CScalarFunc(
m_mp, mdid_func, mdid_return_type,
pdxlopFuncExpr->TypeModifier(),
GPOS_NEW(m_mp) CWStringConst(
- m_mp,
(pmdfunc->Mdname().GetMDName())->GetBuffer()),
- pdxlopFuncExpr->FuncFormat(),
pdxlopFuncExpr->IsFuncVariadic());
+ m_mp,
(pmdfunc->Mdname().GetMDName())->GetBuffer()),
+ pdxlopFuncExpr->FuncFormat(),
pdxlopFuncExpr->IsFuncVariadic());
}
CExpression *pexprFunc = nullptr;
diff --git a/src/backend/gporca/libgpopt/src/xforms/CXformFactory.cpp
b/src/backend/gporca/libgpopt/src/xforms/CXformFactory.cpp
index b45b72f189..27267cc39c 100644
--- a/src/backend/gporca/libgpopt/src/xforms/CXformFactory.cpp
+++ b/src/backend/gporca/libgpopt/src/xforms/CXformFactory.cpp
@@ -302,6 +302,7 @@ CXformFactory::Instantiate()
Add(GPOS_NEW(m_mp) CXformLimit2IndexOnlyGet(m_mp));
Add(GPOS_NEW(m_mp) CXformFullOuterJoin2HashJoin(m_mp));
Add(GPOS_NEW(m_mp) CXformFullJoinCommutativity(m_mp));
+ Add(GPOS_NEW(m_mp) CXformSplitWindowFunc(m_mp));
GPOS_ASSERT(nullptr != m_rgpxf[CXform::ExfSentinel - 1] &&
"Not all xforms have been instantiated");
diff --git
a/src/backend/gporca/libgpopt/src/xforms/CXformImplementSequenceProject.cpp
b/src/backend/gporca/libgpopt/src/xforms/CXformImplementSequenceProject.cpp
index 60645cd532..b6ad3f7e93 100644
--- a/src/backend/gporca/libgpopt/src/xforms/CXformImplementSequenceProject.cpp
+++ b/src/backend/gporca/libgpopt/src/xforms/CXformImplementSequenceProject.cpp
@@ -72,6 +72,7 @@ CXformImplementSequenceProject::Transform(CXformContext
*pxfctxt,
// extract members of logical sequence project operator
CLogicalSequenceProject *popLogicalSequenceProject =
CLogicalSequenceProject::PopConvert(pexpr->Pop());
+ COperator::ESPType sptype = popLogicalSequenceProject->Pspt();
CDistributionSpec *pds = popLogicalSequenceProject->Pds();
COrderSpecArray *pdrgpos = popLogicalSequenceProject->Pdrgpos();
CWindowFrameArray *pdrgpwf = popLogicalSequenceProject->Pdrgpwf();
@@ -81,7 +82,9 @@ CXformImplementSequenceProject::Transform(CXformContext
*pxfctxt,
// assemble physical operator
CExpression *pexprSequenceProject = GPOS_NEW(mp) CExpression(
- mp, GPOS_NEW(mp) CPhysicalSequenceProject(mp, pds, pdrgpos,
pdrgpwf),
+ mp,
+ GPOS_NEW(mp)
+ CPhysicalSequenceProject(mp, sptype, pds, pdrgpos,
pdrgpwf),
pexprRelational, pexprScalar);
// add alternative to results
diff --git a/src/backend/gporca/libgpopt/src/xforms/CXformSplitGbAgg.cpp
b/src/backend/gporca/libgpopt/src/xforms/CXformSplitGbAgg.cpp
index e6c7d27bc1..2a55afdc75 100644
--- a/src/backend/gporca/libgpopt/src/xforms/CXformSplitGbAgg.cpp
+++ b/src/backend/gporca/libgpopt/src/xforms/CXformSplitGbAgg.cpp
@@ -136,11 +136,11 @@ CXformSplitGbAgg::Transform(CXformContext *pxfctxt,
CXformResult *pxfres,
CColRefArray *colref_array = popAgg->Pdrgpcr();
- colref_array->AddRef();
CColRefArray *pdrgpcrLocal = colref_array;
-
colref_array->AddRef();
+
CColRefArray *pdrgpcrGlobal = colref_array;
+ colref_array->AddRef();
CColRefArray *pdrgpcrMinimal = popAgg->PdrgpcrMinimal();
if (nullptr != pdrgpcrMinimal)
diff --git a/src/backend/gporca/libgpopt/src/xforms/CXformSplitWindowFunc.cpp
b/src/backend/gporca/libgpopt/src/xforms/CXformSplitWindowFunc.cpp
new file mode 100644
index 0000000000..92a310101b
--- /dev/null
+++ b/src/backend/gporca/libgpopt/src/xforms/CXformSplitWindowFunc.cpp
@@ -0,0 +1,393 @@
+/*-------------------------------------------------------------------------
+ *
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ *
+ * @filename:
+ * CXformSplitWindowFunc.cpp
+ *
+ * @doc:
+ * Implementation of the splitting of window function
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "gpopt/xforms/CXformSplitWindowFunc.h"
+
+#include "gpos/base.h"
+
+#include "gpopt/base/CUtils.h"
+#include "gpopt/operators/CExpressionHandle.h"
+#include "gpopt/operators/CLogicalSelect.h"
+#include "gpopt/operators/CLogicalSequenceProject.h"
+#include "gpopt/operators/CPatternLeaf.h"
+#include "gpopt/operators/CScalarCmp.h"
+#include "gpopt/operators/CScalarProjectList.h"
+#include "gpopt/operators/CScalarWindowFunc.h"
+#include "gpopt/xforms/CXformUtils.h"
+
+using namespace gpmd;
+using namespace gpopt;
+
+
+//---------------------------------------------------------------------------
+// @function:
+// CXformSplitWindowFunc::CXformSplitWindowFunc
+//
+// @doc:
+// Ctor
+//
+//---------------------------------------------------------------------------
+CXformSplitWindowFunc::CXformSplitWindowFunc(CMemoryPool *mp)
+ : CXformExploration(
+ // pattern
+ GPOS_NEW(mp) CExpression(
+ mp, GPOS_NEW(mp) CLogicalSelect(mp),
+ GPOS_NEW(mp) CExpression(
+ mp, GPOS_NEW(mp) CLogicalSequenceProject(mp),
+ GPOS_NEW(mp) CExpression(
+ mp, GPOS_NEW(mp) CPatternLeaf(mp)),
// relational child
+ GPOS_NEW(mp) CExpression(
+ mp,
+ GPOS_NEW(mp) CPatternTree(mp))),
// scalar project list
+ GPOS_NEW(mp) CExpression(
+ mp, GPOS_NEW(mp) CPatternTree(mp)))) //
scalar project list
+{
+}
+
+//---------------------------------------------------------------------------
+// @function:
+// CXformSplitWindowFunc::Exfp
+//
+// @doc:
+// Compute xform promise for a given expression handle;
+//
+//---------------------------------------------------------------------------
+CXform::EXformPromise
+CXformSplitWindowFunc::Exfp(CExpressionHandle & /*exprhdl*/) const
+{
+ // Current promise can't be measured in `Exfp`
+ // Cause current root operator is the `CLogicalSelect`
+ // But we can measure it in the `Transform`
+ return CXform::ExfpHigh;
+}
+
+//---------------------------------------------------------------------------
+// @function:
+// CXformSplitWindowFunc::PopulateLocalGlobalProjectList
+//
+// @doc:
+// Populate the local or global project list from the input
project list
+//
+//---------------------------------------------------------------------------
+void
+CXformSplitWindowFunc::PopulateLocalGlobalProjectList(
+ CMemoryPool *mp, CExpression *pexprProjList,
+ CExpression **ppexprProjListLocal, CExpression **ppexprProjListGlobal)
+{
+ // list of project elements for the new local and global windows agg
+ CExpressionArray *pdrgpexprProjElemLocal =
+ GPOS_NEW(mp) CExpressionArray(mp);
+ CExpressionArray *pdrgpexprProjElemGlobal =
+ GPOS_NEW(mp) CExpressionArray(mp);
+ const ULONG arity = pexprProjList->Arity();
+ for (ULONG ul = 0; ul < arity; ul++)
+ {
+ CExpression *pexprProgElem = (*pexprProjList)[ul];
+ CScalarProjectElement *popScPrEl =
+ CScalarProjectElement::PopConvert(pexprProgElem->Pop());
+ GPOS_ASSERT(popScPrEl);
+
+ // get the scalar window func
+ CExpression *pexprWinFunc = (*pexprProgElem)[0];
+ CScalarWindowFunc *popScWinFunc =
+ CScalarWindowFunc::PopConvert(pexprWinFunc->Pop());
+ GPOS_ASSERT(popScWinFunc);
+
+ // add ref for the pop local window function
+ popScWinFunc->FuncMdId()->AddRef();
+ popScWinFunc->MdidType()->AddRef();
+
+ CScalarWindowFunc *popScWinFuncLocal = GPOS_NEW(mp)
CScalarWindowFunc(
+ mp, popScWinFunc->FuncMdId(), popScWinFunc->MdidType(),
+ GPOS_NEW(mp)
+ CWStringConst(mp,
popScWinFunc->PstrFunc()->GetBuffer()),
+ popScWinFunc->Ews(), popScWinFunc->IsDistinct(),
+ popScWinFunc->IsStarArg(), popScWinFunc->IsSimpleAgg());
+
+ // add ref for the pop global window function
+ popScWinFunc->FuncMdId()->AddRef();
+ popScWinFunc->MdidType()->AddRef();
+ CScalarWindowFunc *popScWinFuncGlobal = GPOS_NEW(mp)
CScalarWindowFunc(
+ mp, popScWinFunc->FuncMdId(), popScWinFunc->MdidType(),
+ GPOS_NEW(mp)
+ CWStringConst(mp,
popScWinFunc->PstrFunc()->GetBuffer()),
+ popScWinFunc->Ews(), popScWinFunc->IsDistinct(),
+ popScWinFunc->IsStarArg(), popScWinFunc->IsSimpleAgg());
+
+
+ CExpressionArray *pdrgpexprWin = pexprWinFunc->PdrgPexpr();
+
+ // create a new local window function
+ // create array of arguments for the window function
+ pdrgpexprWin->AddRef();
+ CExpression *pexprWinFuncLocal =
+ GPOS_NEW(mp) CExpression(mp, popScWinFuncLocal,
pdrgpexprWin);
+
+ // create a new global window function
+ pdrgpexprWin->AddRef();
+ CExpression *pexprWinFuncGlobal =
+ GPOS_NEW(mp) CExpression(mp, popScWinFuncGlobal,
pdrgpexprWin);
+
+ // create new project elements for the window functions
+ CExpression *pexprProjElemLocal =
CUtils::PexprScalarProjectElement(
+ mp, popScPrEl->Pcr(), pexprWinFuncLocal);
+
+ CExpression *pexprProjElemGlobal =
CUtils::PexprScalarProjectElement(
+ mp, popScPrEl->Pcr(), pexprWinFuncGlobal);
+
+ pdrgpexprProjElemLocal->Append(pexprProjElemLocal);
+ pdrgpexprProjElemGlobal->Append(pexprProjElemGlobal);
+ }
+
+ // create new project lists
+ *ppexprProjListLocal = GPOS_NEW(mp) CExpression(
+ mp, GPOS_NEW(mp) CScalarProjectList(mp),
pdrgpexprProjElemLocal);
+
+ *ppexprProjListGlobal = GPOS_NEW(mp) CExpression(
+ mp, GPOS_NEW(mp) CScalarProjectList(mp),
pdrgpexprProjElemGlobal);
+}
+
+void
+CXformSplitWindowFunc::PopulateSelect(CMemoryPool *mp, CExpression *pexpr,
+
COperator **ppSelectCopy)
+{
+ CLogicalSelect *pexprSelect = CLogicalSelect::PopConvert(pexpr->Pop());
+ CTableDescriptor *tblDesc = pexprSelect->Ptabdesc();
+ if (tblDesc)
+ tblDesc->AddRef();
+
+ *ppSelectCopy = GPOS_NEW(mp) CLogicalSelect(mp, tblDesc);
+}
+
+bool
+CXformSplitWindowFunc::CheckFilterAndProjectList(CExpression *pexprScalarCmp,
+
CExpression *pexprProjList)
+{
+ CScalarCmp *pexprCmp;
+ CScalarIdent *pexprId;
+ IMDType::ECmpType pexprCmpType;
+
+ if (pexprScalarCmp->Pop()->Eopid() != CScalarCmp::EopScalarCmp)
+ {
+ return false;
+ }
+
+ pexprCmp = CScalarCmp::PopConvert(pexprScalarCmp->Pop());
+ pexprCmpType = pexprCmp->ParseCmpType();
+
+ if (!(pexprCmpType == IMDType::EcmptL || pexprCmpType ==
IMDType::EcmptLEq))
+ {
+ return false;
+ }
+
+ if ((*pexprScalarCmp)[0]->Pop()->Eopid() !=
CScalarIdent::EopScalarIdent)
+ {
+ return false;
+ }
+
+ pexprId = CScalarIdent::PopConvert((*pexprScalarCmp)[0]->Pop());
+
+ auto windowOids =
+
COptCtxt::PoctxtFromTLS()->GetOptimizerConfig()->GetWindowOids();
+
+ const ULONG arity = pexprProjList->Arity();
+ for (ULONG ul = 0; ul < arity; ul++)
+ {
+ CExpression *pexprProgElem = (*pexprProjList)[ul];
+ if (pexprProgElem->Pop()->Eopid() !=
+ CScalarProjectElement::EopScalarProjectElement)
+ {
+ return false;
+ }
+
+ CScalarProjectElement *popScPrEl =
+ CScalarProjectElement::PopConvert(pexprProgElem->Pop());
+
+ if (popScPrEl->Pcr() != pexprId->Pcr())
+ {
+ return false;
+ }
+
+ CExpression *pexprWinFunc = (*pexprProgElem)[0];
+ if (pexprWinFunc->Pop()->Eopid() !=
+ CScalarWindowFunc::EopScalarWindowFunc)
+ {
+ return false;
+ }
+
+ CScalarWindowFunc *popScWinFunc =
+ CScalarWindowFunc::PopConvert(pexprWinFunc->Pop());
+
+ if (!(IMDId::MDIdCompare(popScWinFunc->FuncMdId(),
+
windowOids->MDIdRowNumber()) ||
+ IMDId::MDIdCompare(popScWinFunc->FuncMdId(),
+
windowOids->MDIdRank()) ||
+ IMDId::MDIdCompare(popScWinFunc->FuncMdId(),
+
windowOids->MDIdDenseRank())))
+ {
+ return false;
+ }
+ }
+
+ return true;
+}
+
+//---------------------------------------------------------------------------
+// @function:
+// CXformSplitWindowFunc::Transform
+//
+// @doc:
+// Actual transformation to expand a global window agg into a pair
of
+// local and global window agg
+//
+// Input:
+// +--CLogicalSelect (Global(one-step))
+// |--CLogicalSequenceProject
+// | |-- ANY(*)
+// | +--CScalarProjectList
+// +--CScalarCmp
+//
+// Output:
+// +--CLogicalSelect
+// |--CLogicalSequenceProject (Global(two-step))
+// | |--CLogicalSelect
+// | | |--CLogicalSequenceProject (Local)
+// | | | |-- ANY(*)
+// | | | +--CScalarProjectList
+// | | +--CScalarCmp
+// | +--CScalarProjectList
+// +--CScalarCmp
+//
+//---------------------------------------------------------------------------
+void
+CXformSplitWindowFunc::Transform(CXformContext *pxfctxt, CXformResult *pxfres,
+ CExpression
*pexpr) const
+{
+ GPOS_ASSERT(nullptr != pxfctxt);
+ GPOS_ASSERT(nullptr != pxfres);
+ GPOS_ASSERT(FPromising(pxfctxt->Pmp(), this, pexpr));
+ GPOS_ASSERT(FCheckPattern(pexpr));
+
+ CMemoryPool *mp = pxfctxt->Pmp();
+
+ CExpression *pexprSequenceProject = (*pexpr)[0];
+ CExpression *pexprScalarCmp = (*pexpr)[1]; // scalar filter
+
+ // extend logic from `Exfp()`
+ {
+ // not the SequenceProject below Select
+ if (pexprSequenceProject->Pop()->Eopid() !=
+ COperator::EopLogicalSequenceProject ||
+ pexprScalarCmp->Pop()->Eopid() !=
COperator::EopScalarCmp ||
+ pexprSequenceProject->Arity() != 2)
+ {
+ return;
+ }
+
+
+ // make sure `SequenceProject` can be Transform
+ CExpressionHandle exprhdl(mp);
+ exprhdl.Attach(pexprSequenceProject);
+
+ if
(CLogicalSequenceProject::PopConvert(pexprSequenceProject->Pop())
+ ->Pspt() ==
+ COperator::ESPType::EsptypeLocal || /* split
global only */
+ 0 < exprhdl.DeriveOuterReferences()->Size() ||
+ nullptr == exprhdl.PexprScalarExactChild(1) ||
+ CXformUtils::FHasAmbiguousType(
+ exprhdl.PexprScalarExactChild(1
/*child_index*/),
+ COptCtxt::PoctxtFromTLS()->Pmda()))
+ {
+ return;
+ }
+ }
+
+ COperator *pSelectCopy = nullptr;
+
+ CLogicalSequenceProject *popWinFunc =
+
CLogicalSequenceProject::PopConvert(pexprSequenceProject->Pop());
+
+ // extract components
+ CExpression *pexprRelational = (*pexprSequenceProject)[0];
+ CExpression *pexprProjectList = (*pexprSequenceProject)[1];
+
+ CExpression *pexprProjectListLocal = nullptr;
+ CExpression *pexprProjectListGlobal = nullptr;
+
+ if (!CheckFilterAndProjectList(pexprScalarCmp, pexprProjectList))
+ {
+ return;
+ }
+
+ // copy the LogicalSelect
+ (void) PopulateSelect(mp, pexpr, &pSelectCopy);
+ GPOS_ASSERT(pSelectCopy);
+
+ (void) PopulateLocalGlobalProjectList(
+ mp, pexprProjectList, &pexprProjectListLocal,
&pexprProjectListGlobal);
+
+ GPOS_ASSERT(nullptr != pexprProjectListLocal &&
+ nullptr != pexprProjectListLocal);
+
+ CDistributionSpec *pds = popWinFunc->Pds();
+ pds->AddRef();
+
+ COrderSpecArray *pdrgpos = popWinFunc->Pdrgpos();
+ pdrgpos->AddRef();
+
+ CWindowFrameArray *pdrgpwf = popWinFunc->Pdrgpwf();
+ pdrgpwf->AddRef();
+
+ pexprRelational->AddRef();
+
+ CExpression *pexprLocal = GPOS_NEW(mp) CExpression(
+ mp,
+ GPOS_NEW(mp) CLogicalSequenceProject(
+ mp, COperator::ESPType::EsptypeLocal, pds, pdrgpos,
pdrgpwf),
+ pexprRelational, pexprProjectListLocal);
+
+ pexprScalarCmp->AddRef();
+
+ CExpression *pexprLocalSelect =
+ GPOS_NEW(mp) CExpression(mp, pSelectCopy, pexprLocal,
pexprScalarCmp);
+
+ CExpression *pexprGlobal = GPOS_NEW(mp)
+ CExpression(mp,
+ GPOS_NEW(mp) CLogicalSequenceProject(
+ mp,
COperator::ESPType::EsptypeGlobalTwoStep, pds,
+ pdrgpos, pdrgpwf),
+ pexprLocalSelect,
pexprProjectListGlobal);
+
+ CExpression *pexprGlobalSelect =
+ GPOS_NEW(mp) CExpression(mp, pexpr->Pop(), pexprGlobal,
pexprScalarCmp);
+
+ pxfres->Add(pexprGlobalSelect);
+
+ return;
+}
+
+// EOF
diff --git a/src/backend/gporca/libgpopt/src/xforms/CXformSubqueryUnnest.cpp
b/src/backend/gporca/libgpopt/src/xforms/CXformSubqueryUnnest.cpp
index dc31c79702..1c3ccd45cb 100644
--- a/src/backend/gporca/libgpopt/src/xforms/CXformSubqueryUnnest.cpp
+++ b/src/backend/gporca/libgpopt/src/xforms/CXformSubqueryUnnest.cpp
@@ -121,8 +121,9 @@ CXformSubqueryUnnest::PexprSubqueryUnnest(CMemoryPool *mp,
CExpression *pexpr,
popSeqPrj->Pdrgpos()->AddRef();
popSeqPrj->Pdrgpwf()->AddRef();
pexprResult =
CUtils::PexprLogicalSequenceProject(
- mp, popSeqPrj->Pds(),
popSeqPrj->Pdrgpos(),
- popSeqPrj->Pdrgpwf(), pexprNewOuter,
pexprResidualScalar);
+ mp, popSeqPrj->Pspt(), popSeqPrj->Pds(),
+ popSeqPrj->Pdrgpos(),
popSeqPrj->Pdrgpwf(), pexprNewOuter,
+ pexprResidualScalar);
break;
default:
diff --git a/src/backend/gporca/libgpopt/src/xforms/CXformUtils.cpp
b/src/backend/gporca/libgpopt/src/xforms/CXformUtils.cpp
index ffe7c8d097..cce598d386 100644
--- a/src/backend/gporca/libgpopt/src/xforms/CXformUtils.cpp
+++ b/src/backend/gporca/libgpopt/src/xforms/CXformUtils.cpp
@@ -2059,7 +2059,8 @@ CXformUtils::PexprWindowWithRowNumber(CMemoryPool *mp,
CExpression(mp, GPOS_NEW(mp) CScalarProjectList(mp),
pexprProjElem);
CLogicalSequenceProject *popLgSequence =
- GPOS_NEW(mp) CLogicalSequenceProject(mp, pds, pdrgpos, pdrgpwf);
+ GPOS_NEW(mp) CLogicalSequenceProject(
+ mp, COperator::ESPType::EsptypeGlobalOneStep, pds,
pdrgpos, pdrgpwf);
pexprWindowChild->AddRef();
CExpression *pexprLgSequence = GPOS_NEW(mp)
diff --git a/src/backend/gporca/libgpopt/src/xforms/Makefile
b/src/backend/gporca/libgpopt/src/xforms/Makefile
index addfb34025..4283daedb4 100644
--- a/src/backend/gporca/libgpopt/src/xforms/Makefile
+++ b/src/backend/gporca/libgpopt/src/xforms/Makefile
@@ -132,6 +132,7 @@ OBJS = CDecorrelator.o \
CXformSplitGbAgg.o \
CXformSplitGbAggDedup.o \
CXformSplitLimit.o \
+ CXformSplitWindowFunc.o \
CXformSubqJoin2Apply.o \
CXformSubqueryUnnest.o \
CXformUnion2UnionAll.o \
diff --git
a/src/backend/gporca/libnaucrates/include/naucrates/dxl/xml/dxltokens.h
b/src/backend/gporca/libnaucrates/include/naucrates/dxl/xml/dxltokens.h
index 0449d4bf4b..9c21027c05 100644
--- a/src/backend/gporca/libnaucrates/include/naucrates/dxl/xml/dxltokens.h
+++ b/src/backend/gporca/libnaucrates/include/naucrates/dxl/xml/dxltokens.h
@@ -76,6 +76,7 @@ enum Edxltoken
EdxltokenWindowOids,
EdxltokenOidRowNumber,
EdxltokenOidRank,
+ EdxltokenOidDenseRank,
EdxltokenPlanSamples,
diff --git
a/src/backend/gporca/libnaucrates/include/naucrates/traceflags/traceflags.h
b/src/backend/gporca/libnaucrates/include/naucrates/traceflags/traceflags.h
index cf823b6024..b4aea06ac8 100644
--- a/src/backend/gporca/libnaucrates/include/naucrates/traceflags/traceflags.h
+++ b/src/backend/gporca/libnaucrates/include/naucrates/traceflags/traceflags.h
@@ -229,6 +229,9 @@ enum EOptTraceFlag
// Ordered Agg
EopttraceDisableOrderedAgg = 103047,
+ // Force split the window function
+ EopttraceForceSplitWindowFunc = 103048,
+
///////////////////////////////////////////////////////
///////////////////// statistics flags ////////////////
//////////////////////////////////////////////////////
diff --git
a/src/backend/gporca/libnaucrates/src/parser/CParseHandlerWindowOids.cpp
b/src/backend/gporca/libnaucrates/src/parser/CParseHandlerWindowOids.cpp
index d8258f1931..1d8ea944df 100644
--- a/src/backend/gporca/libnaucrates/src/parser/CParseHandlerWindowOids.cpp
+++ b/src/backend/gporca/libnaucrates/src/parser/CParseHandlerWindowOids.cpp
@@ -57,8 +57,11 @@ CParseHandlerWindowOids::StartElement(const XMLCh *const,
//element_uri,
OID rank_oid = CDXLOperatorFactory::ExtractConvertAttrValueToOid(
m_parse_handler_mgr->GetDXLMemoryManager(), attrs,
EdxltokenOidRank,
EdxltokenWindowOids);
+ OID dense_rank_oid = CDXLOperatorFactory::ExtractConvertAttrValueToOid(
+ m_parse_handler_mgr->GetDXLMemoryManager(), attrs,
EdxltokenOidDenseRank,
+ EdxltokenWindowOids);
- m_window_oids = GPOS_NEW(m_mp) CWindowOids(row_number_oid, rank_oid);
+ m_window_oids = GPOS_NEW(m_mp) CWindowOids(m_mp, row_number_oid,
rank_oid, dense_rank_oid);
}
// invoked by Xerces to process a closing tag
diff --git a/src/backend/gporca/libnaucrates/src/xml/dxltokens.cpp
b/src/backend/gporca/libnaucrates/src/xml/dxltokens.cpp
index cce1d445c8..0c977f5752 100644
--- a/src/backend/gporca/libnaucrates/src/xml/dxltokens.cpp
+++ b/src/backend/gporca/libnaucrates/src/xml/dxltokens.cpp
@@ -96,6 +96,7 @@ CDXLTokens::Init(CMemoryPool *mp)
{EdxltokenWindowOids, GPOS_WSZ_LIT("WindowOids")},
{EdxltokenOidRowNumber, GPOS_WSZ_LIT("RowNumber")},
{EdxltokenOidRank, GPOS_WSZ_LIT("Rank")},
+ {EdxltokenOidDenseRank, GPOS_WSZ_LIT("DenseRank")},
{EdxltokenPlanSamples, GPOS_WSZ_LIT("PlanSamples")},
diff --git a/src/backend/gporca/server/src/unittest/CTestUtils.cpp
b/src/backend/gporca/server/src/unittest/CTestUtils.cpp
index 5c66f11a0c..bca99766fd 100644
--- a/src/backend/gporca/server/src/unittest/CTestUtils.cpp
+++ b/src/backend/gporca/server/src/unittest/CTestUtils.cpp
@@ -2272,7 +2272,7 @@ CTestUtils::PexprLogicalSequenceProject(CMemoryPool *mp,
OID oidFunc,
CWindowFrameArray *pdrgwf = GPOS_NEW(mp) CWindowFrameArray(mp);
CLogicalSequenceProject *popSeqProj = GPOS_NEW(mp)
CLogicalSequenceProject(
- mp,
+ mp, COperator::ESPType::EsptypeLocal,
GPOS_NEW(mp)
CDistributionSpecHashed(pdrgpexpr, true
/*fNullsCollocated*/),
pdrgpos, pdrgwf);
diff --git a/src/backend/utils/misc/guc_gp.c b/src/backend/utils/misc/guc_gp.c
index 83481b1496..e342d76270 100644
--- a/src/backend/utils/misc/guc_gp.c
+++ b/src/backend/utils/misc/guc_gp.c
@@ -400,6 +400,7 @@ bool optimizer_force_multistage_agg;
bool optimizer_force_three_stage_scalar_dqa;
bool optimizer_force_expanded_distinct_aggs;
bool optimizer_force_agg_skew_avoidance;
+bool optimizer_force_split_window_function;
bool optimizer_penalize_skew;
bool optimizer_prune_computed_columns;
bool optimizer_push_requirements_from_consumer_to_producer;
@@ -2410,6 +2411,17 @@ struct config_bool ConfigureNamesBool_gp[] =
NULL, NULL, NULL
},
+ {
+ {"optimizer_force_split_window_function", PGC_USERSET,
QUERY_TUNING_METHOD,
+ gettext_noop("Always split the window function."),
+ NULL,
+ GUC_NO_SHOW_ALL | GUC_NOT_IN_SAMPLE
+ },
+ &optimizer_force_split_window_function,
+ false,
+ NULL, NULL, NULL
+ },
+
{
{"optimizer_penalize_skew", PGC_USERSET, QUERY_TUNING_METHOD,
gettext_noop("Penalize operators with skewed hash
redistribute below it."),
diff --git a/src/include/utils/guc.h b/src/include/utils/guc.h
index 1eab599277..63a69864d9 100644
--- a/src/include/utils/guc.h
+++ b/src/include/utils/guc.h
@@ -580,6 +580,7 @@ extern bool optimizer_force_multistage_agg;
extern bool optimizer_force_three_stage_scalar_dqa;
extern bool optimizer_force_expanded_distinct_aggs;
extern bool optimizer_force_agg_skew_avoidance;
+extern bool optimizer_force_split_window_function;
extern bool optimizer_penalize_skew;
extern bool optimizer_prune_computed_columns;
extern bool optimizer_push_requirements_from_consumer_to_producer;
diff --git a/src/include/utils/unsync_guc_name.h
b/src/include/utils/unsync_guc_name.h
index 5945fe3de6..6cbf4b3179 100644
--- a/src/include/utils/unsync_guc_name.h
+++ b/src/include/utils/unsync_guc_name.h
@@ -442,6 +442,7 @@
"optimizer_extract_dxl_stats",
"optimizer_extract_dxl_stats_all_nodes",
"optimizer_force_agg_skew_avoidance",
+ "optimizer_force_split_window_function",
"optimizer_force_comprehensive_join_implementation",
"optimizer_force_expanded_distinct_aggs",
"optimizer_force_multistage_agg",
diff --git a/src/test/regress/expected/window.out
b/src/test/regress/expected/window.out
index d718af699b..27b2a1aaf8 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -399,6 +399,446 @@ SELECT first_value(ten) OVER (PARTITION BY four ORDER BY
ten), ten, four FROM te
0 | 0 | 2
(10 rows)
+-- test split window func
+explain SELECT * FROM (SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS
rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 3;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=8.60..8.76 rows=3 width=16)
+ -> Subquery Scan on t (cost=8.60..8.71 rows=1 width=16)
+ Filter: (t.rank_1 < 3)
+ -> WindowAgg (cost=8.60..8.67 rows=3 width=16)
+ Partition By: tenk1.four
+ Order By: tenk1.ten
+ -> Sort (cost=8.60..8.61 rows=3 width=8)
+ Sort Key: tenk1.four, tenk1.ten
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.16..8.57 rows=3 width=8)
+ Hash Key: tenk1.four
+ -> Index Scan using tenk1_unique2 on tenk1
(cost=0.16..8.51 rows=3 width=8)
+ Index Cond: (unique2 < 10)
+ Optimizer: Postgres query optimizer
+(13 rows)
+
+explain SELECT * FROM (SELECT row_number() OVER (PARTITION BY four ORDER BY
ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 3;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=8.60..8.76 rows=3 width=16)
+ -> Subquery Scan on t (cost=8.60..8.71 rows=1 width=16)
+ Filter: (t.rank_1 < 3)
+ -> WindowAgg (cost=8.60..8.67 rows=3 width=16)
+ Partition By: tenk1.four
+ Order By: tenk1.ten
+ -> Sort (cost=8.60..8.61 rows=3 width=8)
+ Sort Key: tenk1.four, tenk1.ten
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.16..8.57 rows=3 width=8)
+ Hash Key: tenk1.four
+ -> Index Scan using tenk1_unique2 on tenk1
(cost=0.16..8.51 rows=3 width=8)
+ Index Cond: (unique2 < 10)
+ Optimizer: Postgres query optimizer
+(13 rows)
+
+explain SELECT * FROM (SELECT dense_rank() OVER (PARTITION BY four ORDER BY
ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 3;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=8.60..8.76 rows=3 width=16)
+ -> Subquery Scan on t (cost=8.60..8.71 rows=1 width=16)
+ Filter: (t.rank_1 < 3)
+ -> WindowAgg (cost=8.60..8.67 rows=3 width=16)
+ Partition By: tenk1.four
+ Order By: tenk1.ten
+ -> Sort (cost=8.60..8.61 rows=3 width=8)
+ Sort Key: tenk1.four, tenk1.ten
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.16..8.57 rows=3 width=8)
+ Hash Key: tenk1.four
+ -> Index Scan using tenk1_unique2 on tenk1
(cost=0.16..8.51 rows=3 width=8)
+ Index Cond: (unique2 < 10)
+ Optimizer: Postgres query optimizer
+(13 rows)
+
+explain SELECT * FROM (SELECT percent_rank() OVER (PARTITION BY four ORDER BY
ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 0.5;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=8.60..8.76 rows=3 width=16)
+ -> Subquery Scan on t (cost=8.60..8.71 rows=1 width=16)
+ Filter: (t.rank_1 < '0.5'::double precision)
+ -> WindowAgg (cost=8.60..8.67 rows=3 width=16)
+ Partition By: tenk1.four
+ Order By: tenk1.ten
+ -> Sort (cost=8.60..8.61 rows=3 width=8)
+ Sort Key: tenk1.four, tenk1.ten
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.16..8.57 rows=3 width=8)
+ Hash Key: tenk1.four
+ -> Index Scan using tenk1_unique2 on tenk1
(cost=0.16..8.51 rows=3 width=8)
+ Index Cond: (unique2 < 10)
+ Optimizer: Postgres query optimizer
+(13 rows)
+
+set optimizer_force_split_window_function to on;
+-- worked
+explain SELECT * FROM (SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS
rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 3;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=8.60..8.76 rows=3 width=16)
+ -> Subquery Scan on t (cost=8.60..8.71 rows=1 width=16)
+ Filter: (t.rank_1 < 3)
+ -> WindowAgg (cost=8.60..8.67 rows=3 width=16)
+ Partition By: tenk1.four
+ Order By: tenk1.ten
+ -> Sort (cost=8.60..8.61 rows=3 width=8)
+ Sort Key: tenk1.four, tenk1.ten
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.16..8.57 rows=3 width=8)
+ Hash Key: tenk1.four
+ -> Index Scan using tenk1_unique2 on tenk1
(cost=0.16..8.51 rows=3 width=8)
+ Index Cond: (unique2 < 10)
+ Optimizer: Postgres query optimizer
+(13 rows)
+
+explain SELECT * FROM (SELECT row_number() OVER (PARTITION BY four ORDER BY
ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 3;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=8.60..8.76 rows=3 width=16)
+ -> Subquery Scan on t (cost=8.60..8.71 rows=1 width=16)
+ Filter: (t.rank_1 < 3)
+ -> WindowAgg (cost=8.60..8.67 rows=3 width=16)
+ Partition By: tenk1.four
+ Order By: tenk1.ten
+ -> Sort (cost=8.60..8.61 rows=3 width=8)
+ Sort Key: tenk1.four, tenk1.ten
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.16..8.57 rows=3 width=8)
+ Hash Key: tenk1.four
+ -> Index Scan using tenk1_unique2 on tenk1
(cost=0.16..8.51 rows=3 width=8)
+ Index Cond: (unique2 < 10)
+ Optimizer: Postgres query optimizer
+(13 rows)
+
+explain SELECT * FROM (SELECT dense_rank() OVER (PARTITION BY four ORDER BY
ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 3;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=8.60..8.76 rows=3 width=16)
+ -> Subquery Scan on t (cost=8.60..8.71 rows=1 width=16)
+ Filter: (t.rank_1 < 3)
+ -> WindowAgg (cost=8.60..8.67 rows=3 width=16)
+ Partition By: tenk1.four
+ Order By: tenk1.ten
+ -> Sort (cost=8.60..8.61 rows=3 width=8)
+ Sort Key: tenk1.four, tenk1.ten
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.16..8.57 rows=3 width=8)
+ Hash Key: tenk1.four
+ -> Index Scan using tenk1_unique2 on tenk1
(cost=0.16..8.51 rows=3 width=8)
+ Index Cond: (unique2 < 10)
+ Optimizer: Postgres query optimizer
+(13 rows)
+
+explain SELECT * FROM (SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS
rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 <= 3;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=8.60..8.76 rows=3 width=16)
+ -> Subquery Scan on t (cost=8.60..8.71 rows=1 width=16)
+ Filter: (t.rank_1 <= 3)
+ -> WindowAgg (cost=8.60..8.67 rows=3 width=16)
+ Partition By: tenk1.four
+ Order By: tenk1.ten
+ -> Sort (cost=8.60..8.61 rows=3 width=8)
+ Sort Key: tenk1.four, tenk1.ten
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.16..8.57 rows=3 width=8)
+ Hash Key: tenk1.four
+ -> Index Scan using tenk1_unique2 on tenk1
(cost=0.16..8.51 rows=3 width=8)
+ Index Cond: (unique2 < 10)
+ Optimizer: Postgres query optimizer
+(13 rows)
+
+explain SELECT * FROM (SELECT row_number() OVER (PARTITION BY four ORDER BY
ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 <= 3;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=8.60..8.76 rows=3 width=16)
+ -> Subquery Scan on t (cost=8.60..8.71 rows=1 width=16)
+ Filter: (t.rank_1 <= 3)
+ -> WindowAgg (cost=8.60..8.67 rows=3 width=16)
+ Partition By: tenk1.four
+ Order By: tenk1.ten
+ -> Sort (cost=8.60..8.61 rows=3 width=8)
+ Sort Key: tenk1.four, tenk1.ten
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.16..8.57 rows=3 width=8)
+ Hash Key: tenk1.four
+ -> Index Scan using tenk1_unique2 on tenk1
(cost=0.16..8.51 rows=3 width=8)
+ Index Cond: (unique2 < 10)
+ Optimizer: Postgres query optimizer
+(13 rows)
+
+explain SELECT * FROM (SELECT dense_rank() OVER (PARTITION BY four ORDER BY
ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 <= 3;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=8.60..8.76 rows=3 width=16)
+ -> Subquery Scan on t (cost=8.60..8.71 rows=1 width=16)
+ Filter: (t.rank_1 <= 3)
+ -> WindowAgg (cost=8.60..8.67 rows=3 width=16)
+ Partition By: tenk1.four
+ Order By: tenk1.ten
+ -> Sort (cost=8.60..8.61 rows=3 width=8)
+ Sort Key: tenk1.four, tenk1.ten
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.16..8.57 rows=3 width=8)
+ Hash Key: tenk1.four
+ -> Index Scan using tenk1_unique2 on tenk1
(cost=0.16..8.51 rows=3 width=8)
+ Index Cond: (unique2 < 10)
+ Optimizer: Postgres query optimizer
+(13 rows)
+
+-- no worked
+explain SELECT * FROM (SELECT cume_dist() OVER (PARTITION BY four ORDER BY
ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 <= 1;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=8.60..8.76 rows=3 width=16)
+ -> Subquery Scan on t (cost=8.60..8.71 rows=1 width=16)
+ Filter: (t.rank_1 <= '1'::double precision)
+ -> WindowAgg (cost=8.60..8.67 rows=3 width=16)
+ Partition By: tenk1.four
+ Order By: tenk1.ten
+ -> Sort (cost=8.60..8.61 rows=3 width=8)
+ Sort Key: tenk1.four, tenk1.ten
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.16..8.57 rows=3 width=8)
+ Hash Key: tenk1.four
+ -> Index Scan using tenk1_unique2 on tenk1
(cost=0.16..8.51 rows=3 width=8)
+ Index Cond: (unique2 < 10)
+ Optimizer: Postgres query optimizer
+(13 rows)
+
+explain SELECT * FROM (SELECT percent_rank() OVER (PARTITION BY four ORDER BY
ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 0.5;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=8.60..8.76 rows=3 width=16)
+ -> Subquery Scan on t (cost=8.60..8.71 rows=1 width=16)
+ Filter: (t.rank_1 < '0.5'::double precision)
+ -> WindowAgg (cost=8.60..8.67 rows=3 width=16)
+ Partition By: tenk1.four
+ Order By: tenk1.ten
+ -> Sort (cost=8.60..8.61 rows=3 width=8)
+ Sort Key: tenk1.four, tenk1.ten
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.16..8.57 rows=3 width=8)
+ Hash Key: tenk1.four
+ -> Index Scan using tenk1_unique2 on tenk1
(cost=0.16..8.51 rows=3 width=8)
+ Index Cond: (unique2 < 10)
+ Optimizer: Postgres query optimizer
+(13 rows)
+
+explain SELECT * FROM (SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS
rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 > 1;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=8.60..8.76 rows=3 width=16)
+ -> Subquery Scan on t (cost=8.60..8.71 rows=1 width=16)
+ Filter: (t.rank_1 > 1)
+ -> WindowAgg (cost=8.60..8.67 rows=3 width=16)
+ Partition By: tenk1.four
+ Order By: tenk1.ten
+ -> Sort (cost=8.60..8.61 rows=3 width=8)
+ Sort Key: tenk1.four, tenk1.ten
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.16..8.57 rows=3 width=8)
+ Hash Key: tenk1.four
+ -> Index Scan using tenk1_unique2 on tenk1
(cost=0.16..8.51 rows=3 width=8)
+ Index Cond: (unique2 < 10)
+ Optimizer: Postgres query optimizer
+(13 rows)
+
+explain SELECT * FROM (SELECT row_number() OVER (PARTITION BY four ORDER BY
ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 > 1;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=8.60..8.76 rows=3 width=16)
+ -> Subquery Scan on t (cost=8.60..8.71 rows=1 width=16)
+ Filter: (t.rank_1 > 1)
+ -> WindowAgg (cost=8.60..8.67 rows=3 width=16)
+ Partition By: tenk1.four
+ Order By: tenk1.ten
+ -> Sort (cost=8.60..8.61 rows=3 width=8)
+ Sort Key: tenk1.four, tenk1.ten
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.16..8.57 rows=3 width=8)
+ Hash Key: tenk1.four
+ -> Index Scan using tenk1_unique2 on tenk1
(cost=0.16..8.51 rows=3 width=8)
+ Index Cond: (unique2 < 10)
+ Optimizer: Postgres query optimizer
+(13 rows)
+
+explain SELECT * FROM (SELECT dense_rank() OVER (PARTITION BY four ORDER BY
ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 > 1;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=8.60..8.76 rows=3 width=16)
+ -> Subquery Scan on t (cost=8.60..8.71 rows=1 width=16)
+ Filter: (t.rank_1 > 1)
+ -> WindowAgg (cost=8.60..8.67 rows=3 width=16)
+ Partition By: tenk1.four
+ Order By: tenk1.ten
+ -> Sort (cost=8.60..8.61 rows=3 width=8)
+ Sort Key: tenk1.four, tenk1.ten
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.16..8.57 rows=3 width=8)
+ Hash Key: tenk1.four
+ -> Index Scan using tenk1_unique2 on tenk1
(cost=0.16..8.51 rows=3 width=8)
+ Index Cond: (unique2 < 10)
+ Optimizer: Postgres query optimizer
+(13 rows)
+
+-- verify the split window function result
+set optimizer_force_split_window_function to off;
+SELECT * FROM (SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS rank_1,
ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 3;
+ rank_1 | ten | four
+--------+-----+------
+ 1 | 0 | 0
+ 1 | 0 | 0
+ 1 | 1 | 1
+ 1 | 1 | 1
+ 1 | 0 | 2
+ 1 | 1 | 3
+ 2 | 3 | 3
+(7 rows)
+
+SELECT * FROM (SELECT row_number() OVER (PARTITION BY four ORDER BY ten) AS
rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 3;
+ rank_1 | ten | four
+--------+-----+------
+ 1 | 0 | 2
+ 1 | 1 | 3
+ 2 | 3 | 3
+ 1 | 0 | 0
+ 2 | 0 | 0
+ 1 | 1 | 1
+ 2 | 1 | 1
+(7 rows)
+
+SELECT * FROM (SELECT dense_rank() OVER (PARTITION BY four ORDER BY ten) AS
rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 3;
+ rank_1 | ten | four
+--------+-----+------
+ 1 | 0 | 2
+ 1 | 1 | 3
+ 2 | 3 | 3
+ 1 | 0 | 0
+ 1 | 0 | 0
+ 2 | 4 | 0
+ 1 | 1 | 1
+ 1 | 1 | 1
+ 2 | 7 | 1
+(9 rows)
+
+SELECT * FROM (SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS rank_1,
ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 <= 3;
+ rank_1 | ten | four
+--------+-----+------
+ 1 | 0 | 2
+ 1 | 1 | 3
+ 2 | 3 | 3
+ 1 | 0 | 0
+ 1 | 0 | 0
+ 3 | 4 | 0
+ 1 | 1 | 1
+ 1 | 1 | 1
+ 3 | 7 | 1
+(9 rows)
+
+SELECT * FROM (SELECT row_number() OVER (PARTITION BY four ORDER BY ten) AS
rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 <= 3;
+ rank_1 | ten | four
+--------+-----+------
+ 1 | 0 | 2
+ 1 | 1 | 3
+ 2 | 3 | 3
+ 1 | 0 | 0
+ 2 | 0 | 0
+ 3 | 4 | 0
+ 1 | 1 | 1
+ 2 | 1 | 1
+ 3 | 7 | 1
+(9 rows)
+
+SELECT * FROM (SELECT dense_rank() OVER (PARTITION BY four ORDER BY ten) AS
rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 <= 3;
+ rank_1 | ten | four
+--------+-----+------
+ 1 | 0 | 2
+ 1 | 1 | 3
+ 2 | 3 | 3
+ 1 | 0 | 0
+ 1 | 0 | 0
+ 2 | 4 | 0
+ 1 | 1 | 1
+ 1 | 1 | 1
+ 2 | 7 | 1
+ 3 | 9 | 1
+(10 rows)
+
+set optimizer_force_split_window_function to on;
+SELECT * FROM (SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS rank_1,
ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 3;
+ rank_1 | ten | four
+--------+-----+------
+ 1 | 0 | 2
+ 1 | 1 | 3
+ 2 | 3 | 3
+ 1 | 0 | 0
+ 1 | 0 | 0
+ 1 | 1 | 1
+ 1 | 1 | 1
+(7 rows)
+
+SELECT * FROM (SELECT row_number() OVER (PARTITION BY four ORDER BY ten) AS
rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 3;
+ rank_1 | ten | four
+--------+-----+------
+ 1 | 0 | 2
+ 1 | 1 | 3
+ 2 | 3 | 3
+ 1 | 0 | 0
+ 2 | 0 | 0
+ 1 | 1 | 1
+ 2 | 1 | 1
+(7 rows)
+
+SELECT * FROM (SELECT dense_rank() OVER (PARTITION BY four ORDER BY ten) AS
rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 3;
+ rank_1 | ten | four
+--------+-----+------
+ 1 | 0 | 0
+ 1 | 0 | 0
+ 2 | 4 | 0
+ 1 | 1 | 1
+ 1 | 1 | 1
+ 2 | 7 | 1
+ 1 | 0 | 2
+ 1 | 1 | 3
+ 2 | 3 | 3
+(9 rows)
+
+SELECT * FROM (SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS rank_1,
ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 <= 3;
+ rank_1 | ten | four
+--------+-----+------
+ 1 | 0 | 2
+ 1 | 1 | 3
+ 2 | 3 | 3
+ 1 | 0 | 0
+ 1 | 0 | 0
+ 3 | 4 | 0
+ 1 | 1 | 1
+ 1 | 1 | 1
+ 3 | 7 | 1
+(9 rows)
+
+SELECT * FROM (SELECT row_number() OVER (PARTITION BY four ORDER BY ten) AS
rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 <= 3;
+ rank_1 | ten | four
+--------+-----+------
+ 1 | 0 | 2
+ 1 | 1 | 3
+ 2 | 3 | 3
+ 1 | 0 | 0
+ 2 | 0 | 0
+ 3 | 4 | 0
+ 1 | 1 | 1
+ 2 | 1 | 1
+ 3 | 7 | 1
+(9 rows)
+
+SELECT * FROM (SELECT dense_rank() OVER (PARTITION BY four ORDER BY ten) AS
rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 <= 3;
+ rank_1 | ten | four
+--------+-----+------
+ 1 | 0 | 2
+ 1 | 1 | 3
+ 2 | 3 | 3
+ 1 | 0 | 0
+ 1 | 0 | 0
+ 2 | 4 | 0
+ 1 | 1 | 1
+ 1 | 1 | 1
+ 2 | 7 | 1
+ 3 | 9 | 1
+(10 rows)
+
+reset optimizer_force_split_window_function;
-- last_value returns the last row of the frame, which is CURRENT ROW in ORDER
BY window.
-- the column `ten` is ordered, so we should call last_value on this
-- column. Using other cols the result is flaky because there are
diff --git a/src/test/regress/expected/window_optimizer.out
b/src/test/regress/expected/window_optimizer.out
index cb80eb0fa7..f6bdcc3b6c 100644
--- a/src/test/regress/expected/window_optimizer.out
+++ b/src/test/regress/expected/window_optimizer.out
@@ -399,6 +399,488 @@ SELECT first_value(ten) OVER (PARTITION BY four ORDER BY
ten), ten, four FROM te
0 | 0 | 2
(10 rows)
+-- test split window func
+explain SELECT * FROM (SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS
rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 3;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..6.00 rows=5 width=16)
+ -> Result (cost=0.00..6.00 rows=2 width=16)
+ Filter: ((rank() OVER (?)) < 3)
+ -> WindowAgg (cost=0.00..6.00 rows=4 width=16)
+ Partition By: four
+ Order By: ten
+ -> Sort (cost=0.00..6.00 rows=4 width=8)
+ Sort Key: four, ten
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.00..6.00 rows=4 width=8)
+ Hash Key: four
+ -> Index Scan using tenk1_unique2 on tenk1
(cost=0.00..6.00 rows=4 width=8)
+ Index Cond: (unique2 < 10)
+ Optimizer: GPORCA
+(13 rows)
+
+explain SELECT * FROM (SELECT row_number() OVER (PARTITION BY four ORDER BY
ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 3;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..6.00 rows=5 width=16)
+ -> Result (cost=0.00..6.00 rows=2 width=16)
+ Filter: ((row_number() OVER (?)) < 3)
+ -> WindowAgg (cost=0.00..6.00 rows=4 width=16)
+ Partition By: four
+ Order By: ten
+ -> Sort (cost=0.00..6.00 rows=4 width=8)
+ Sort Key: four, ten
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.00..6.00 rows=4 width=8)
+ Hash Key: four
+ -> Index Scan using tenk1_unique2 on tenk1
(cost=0.00..6.00 rows=4 width=8)
+ Index Cond: (unique2 < 10)
+ Optimizer: GPORCA
+(13 rows)
+
+explain SELECT * FROM (SELECT dense_rank() OVER (PARTITION BY four ORDER BY
ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 3;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..6.00 rows=5 width=16)
+ -> Result (cost=0.00..6.00 rows=2 width=16)
+ Filter: ((dense_rank() OVER (?)) < 3)
+ -> WindowAgg (cost=0.00..6.00 rows=4 width=16)
+ Partition By: four
+ Order By: ten
+ -> Sort (cost=0.00..6.00 rows=4 width=8)
+ Sort Key: four, ten
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.00..6.00 rows=4 width=8)
+ Hash Key: four
+ -> Index Scan using tenk1_unique2 on tenk1
(cost=0.00..6.00 rows=4 width=8)
+ Index Cond: (unique2 < 10)
+ Optimizer: GPORCA
+(13 rows)
+
+explain SELECT * FROM (SELECT percent_rank() OVER (PARTITION BY four ORDER BY
ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 0.5;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..6.00 rows=5 width=16)
+ -> Result (cost=0.00..6.00 rows=2 width=16)
+ Filter: ((percent_rank() OVER (?)) < '0.5'::double precision)
+ -> WindowAgg (cost=0.00..6.00 rows=4 width=16)
+ Partition By: four
+ Order By: ten
+ -> Sort (cost=0.00..6.00 rows=4 width=8)
+ Sort Key: four, ten
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.00..6.00 rows=4 width=8)
+ Hash Key: four
+ -> Index Scan using tenk1_unique2 on tenk1
(cost=0.00..6.00 rows=4 width=8)
+ Index Cond: (unique2 < 10)
+ Optimizer: GPORCA
+(13 rows)
+
+set optimizer_force_split_window_function to on;
+-- worked
+explain SELECT * FROM (SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS
rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 3;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..0.00 rows=5 width=16)
+ -> Result (cost=0.00..0.00 rows=2 width=16)
+ Filter: ((rank() OVER (?)) < 3)
+ -> WindowAgg (cost=0.00..0.00 rows=2 width=16)
+ Partition By: four
+ Order By: ten
+ -> Sort (cost=0.00..6.00 rows=2 width=8)
+ Sort Key: four, ten
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.00..6.00 rows=2 width=8)
+ Hash Key: four
+ -> Result (cost=0.00..6.00 rows=2 width=8)
+ Filter: ((rank() OVER (?)) < 3)
+ -> WindowAgg (cost=0.00..6.00 rows=4
width=16)
+ Partition By: four
+ Order By: ten
+ -> Sort (cost=0.00..6.00 rows=4
width=8)
+ Sort Key: four, ten
+ -> Index Scan using
tenk1_unique2 on tenk1 (cost=0.00..6.00 rows=4 width=8)
+ Index Cond: (unique2 < 10)
+ Optimizer: GPORCA
+(20 rows)
+
+explain SELECT * FROM (SELECT row_number() OVER (PARTITION BY four ORDER BY
ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 3;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..0.00 rows=5 width=16)
+ -> Result (cost=0.00..0.00 rows=2 width=16)
+ Filter: ((row_number() OVER (?)) < 3)
+ -> WindowAgg (cost=0.00..0.00 rows=2 width=16)
+ Partition By: four
+ Order By: ten
+ -> Sort (cost=0.00..6.00 rows=2 width=8)
+ Sort Key: four, ten
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.00..6.00 rows=2 width=8)
+ Hash Key: four
+ -> Result (cost=0.00..6.00 rows=2 width=8)
+ Filter: ((row_number() OVER (?)) < 3)
+ -> WindowAgg (cost=0.00..6.00 rows=4
width=16)
+ Partition By: four
+ Order By: ten
+ -> Sort (cost=0.00..6.00 rows=4
width=8)
+ Sort Key: four, ten
+ -> Index Scan using
tenk1_unique2 on tenk1 (cost=0.00..6.00 rows=4 width=8)
+ Index Cond: (unique2 < 10)
+ Optimizer: GPORCA
+(20 rows)
+
+explain SELECT * FROM (SELECT dense_rank() OVER (PARTITION BY four ORDER BY
ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 3;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..0.00 rows=5 width=16)
+ -> Result (cost=0.00..0.00 rows=2 width=16)
+ Filter: ((dense_rank() OVER (?)) < 3)
+ -> WindowAgg (cost=0.00..0.00 rows=2 width=16)
+ Partition By: four
+ Order By: ten
+ -> Sort (cost=0.00..6.00 rows=2 width=8)
+ Sort Key: four, ten
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.00..6.00 rows=2 width=8)
+ Hash Key: four
+ -> Result (cost=0.00..6.00 rows=2 width=8)
+ Filter: ((dense_rank() OVER (?)) < 3)
+ -> WindowAgg (cost=0.00..6.00 rows=4
width=16)
+ Partition By: four
+ Order By: ten
+ -> Sort (cost=0.00..6.00 rows=4
width=8)
+ Sort Key: four, ten
+ -> Index Scan using
tenk1_unique2 on tenk1 (cost=0.00..6.00 rows=4 width=8)
+ Index Cond: (unique2 < 10)
+ Optimizer: GPORCA
+(20 rows)
+
+explain SELECT * FROM (SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS
rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 <= 3;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..0.00 rows=5 width=16)
+ -> Result (cost=0.00..0.00 rows=2 width=16)
+ Filter: ((rank() OVER (?)) <= 3)
+ -> WindowAgg (cost=0.00..0.00 rows=2 width=16)
+ Partition By: four
+ Order By: ten
+ -> Sort (cost=0.00..6.00 rows=2 width=8)
+ Sort Key: four, ten
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.00..6.00 rows=2 width=8)
+ Hash Key: four
+ -> Result (cost=0.00..6.00 rows=2 width=8)
+ Filter: ((rank() OVER (?)) <= 3)
+ -> WindowAgg (cost=0.00..6.00 rows=4
width=16)
+ Partition By: four
+ Order By: ten
+ -> Sort (cost=0.00..6.00 rows=4
width=8)
+ Sort Key: four, ten
+ -> Index Scan using
tenk1_unique2 on tenk1 (cost=0.00..6.00 rows=4 width=8)
+ Index Cond: (unique2 < 10)
+ Optimizer: GPORCA
+(20 rows)
+
+explain SELECT * FROM (SELECT row_number() OVER (PARTITION BY four ORDER BY
ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 <= 3;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..0.00 rows=5 width=16)
+ -> Result (cost=0.00..0.00 rows=2 width=16)
+ Filter: ((row_number() OVER (?)) <= 3)
+ -> WindowAgg (cost=0.00..0.00 rows=2 width=16)
+ Partition By: four
+ Order By: ten
+ -> Sort (cost=0.00..6.00 rows=2 width=8)
+ Sort Key: four, ten
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.00..6.00 rows=2 width=8)
+ Hash Key: four
+ -> Result (cost=0.00..6.00 rows=2 width=8)
+ Filter: ((row_number() OVER (?)) <= 3)
+ -> WindowAgg (cost=0.00..6.00 rows=4
width=16)
+ Partition By: four
+ Order By: ten
+ -> Sort (cost=0.00..6.00 rows=4
width=8)
+ Sort Key: four, ten
+ -> Index Scan using
tenk1_unique2 on tenk1 (cost=0.00..6.00 rows=4 width=8)
+ Index Cond: (unique2 < 10)
+ Optimizer: GPORCA
+(20 rows)
+
+explain SELECT * FROM (SELECT dense_rank() OVER (PARTITION BY four ORDER BY
ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 <= 3;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..0.00 rows=5 width=16)
+ -> Result (cost=0.00..0.00 rows=2 width=16)
+ Filter: ((dense_rank() OVER (?)) <= 3)
+ -> WindowAgg (cost=0.00..0.00 rows=2 width=16)
+ Partition By: four
+ Order By: ten
+ -> Sort (cost=0.00..6.00 rows=2 width=8)
+ Sort Key: four, ten
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.00..6.00 rows=2 width=8)
+ Hash Key: four
+ -> Result (cost=0.00..6.00 rows=2 width=8)
+ Filter: ((dense_rank() OVER (?)) <= 3)
+ -> WindowAgg (cost=0.00..6.00 rows=4
width=16)
+ Partition By: four
+ Order By: ten
+ -> Sort (cost=0.00..6.00 rows=4
width=8)
+ Sort Key: four, ten
+ -> Index Scan using
tenk1_unique2 on tenk1 (cost=0.00..6.00 rows=4 width=8)
+ Index Cond: (unique2 < 10)
+ Optimizer: GPORCA
+(20 rows)
+
+-- no worked
+explain SELECT * FROM (SELECT cume_dist() OVER (PARTITION BY four ORDER BY
ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 <= 1;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..6.00 rows=5 width=16)
+ -> Result (cost=0.00..6.00 rows=2 width=16)
+ Filter: ((cume_dist() OVER (?)) <= '1'::double precision)
+ -> WindowAgg (cost=0.00..6.00 rows=4 width=16)
+ Partition By: four
+ Order By: ten
+ -> Sort (cost=0.00..6.00 rows=4 width=8)
+ Sort Key: four, ten
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.00..6.00 rows=4 width=8)
+ Hash Key: four
+ -> Index Scan using tenk1_unique2 on tenk1
(cost=0.00..6.00 rows=4 width=8)
+ Index Cond: (unique2 < 10)
+ Optimizer: GPORCA
+(13 rows)
+
+explain SELECT * FROM (SELECT percent_rank() OVER (PARTITION BY four ORDER BY
ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 0.5;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..6.00 rows=5 width=16)
+ -> Result (cost=0.00..6.00 rows=2 width=16)
+ Filter: ((percent_rank() OVER (?)) < '0.5'::double precision)
+ -> WindowAgg (cost=0.00..6.00 rows=4 width=16)
+ Partition By: four
+ Order By: ten
+ -> Sort (cost=0.00..6.00 rows=4 width=8)
+ Sort Key: four, ten
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.00..6.00 rows=4 width=8)
+ Hash Key: four
+ -> Index Scan using tenk1_unique2 on tenk1
(cost=0.00..6.00 rows=4 width=8)
+ Index Cond: (unique2 < 10)
+ Optimizer: GPORCA
+(13 rows)
+
+explain SELECT * FROM (SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS
rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 > 1;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..6.00 rows=5 width=16)
+ -> Result (cost=0.00..6.00 rows=2 width=16)
+ Filter: ((rank() OVER (?)) > 1)
+ -> WindowAgg (cost=0.00..6.00 rows=4 width=16)
+ Partition By: four
+ Order By: ten
+ -> Sort (cost=0.00..6.00 rows=4 width=8)
+ Sort Key: four, ten
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.00..6.00 rows=4 width=8)
+ Hash Key: four
+ -> Index Scan using tenk1_unique2 on tenk1
(cost=0.00..6.00 rows=4 width=8)
+ Index Cond: (unique2 < 10)
+ Optimizer: GPORCA
+(13 rows)
+
+explain SELECT * FROM (SELECT row_number() OVER (PARTITION BY four ORDER BY
ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 > 1;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..6.00 rows=5 width=16)
+ -> Result (cost=0.00..6.00 rows=2 width=16)
+ Filter: ((row_number() OVER (?)) > 1)
+ -> WindowAgg (cost=0.00..6.00 rows=4 width=16)
+ Partition By: four
+ Order By: ten
+ -> Sort (cost=0.00..6.00 rows=4 width=8)
+ Sort Key: four, ten
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.00..6.00 rows=4 width=8)
+ Hash Key: four
+ -> Index Scan using tenk1_unique2 on tenk1
(cost=0.00..6.00 rows=4 width=8)
+ Index Cond: (unique2 < 10)
+ Optimizer: GPORCA
+(13 rows)
+
+explain SELECT * FROM (SELECT dense_rank() OVER (PARTITION BY four ORDER BY
ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 > 1;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..6.00 rows=5 width=16)
+ -> Result (cost=0.00..6.00 rows=2 width=16)
+ Filter: ((dense_rank() OVER (?)) > 1)
+ -> WindowAgg (cost=0.00..6.00 rows=4 width=16)
+ Partition By: four
+ Order By: ten
+ -> Sort (cost=0.00..6.00 rows=4 width=8)
+ Sort Key: four, ten
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.00..6.00 rows=4 width=8)
+ Hash Key: four
+ -> Index Scan using tenk1_unique2 on tenk1
(cost=0.00..6.00 rows=4 width=8)
+ Index Cond: (unique2 < 10)
+ Optimizer: GPORCA
+(13 rows)
+
+-- verify the split window function result
+set optimizer_force_split_window_function to off;
+SELECT * FROM (SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS rank_1,
ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 3;
+ rank_1 | ten | four
+--------+-----+------
+ 1 | 0 | 2
+ 1 | 1 | 3
+ 2 | 3 | 3
+ 1 | 0 | 0
+ 1 | 0 | 0
+ 1 | 1 | 1
+ 1 | 1 | 1
+(7 rows)
+
+SELECT * FROM (SELECT row_number() OVER (PARTITION BY four ORDER BY ten) AS
rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 3;
+ rank_1 | ten | four
+--------+-----+------
+ 1 | 0 | 2
+ 1 | 1 | 3
+ 2 | 3 | 3
+ 1 | 0 | 0
+ 2 | 0 | 0
+ 1 | 1 | 1
+ 2 | 1 | 1
+(7 rows)
+
+SELECT * FROM (SELECT dense_rank() OVER (PARTITION BY four ORDER BY ten) AS
rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 3;
+ rank_1 | ten | four
+--------+-----+------
+ 1 | 0 | 2
+ 1 | 1 | 3
+ 2 | 3 | 3
+ 1 | 0 | 0
+ 1 | 0 | 0
+ 2 | 4 | 0
+ 1 | 1 | 1
+ 1 | 1 | 1
+ 2 | 7 | 1
+(9 rows)
+
+SELECT * FROM (SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS rank_1,
ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 <= 3;
+ rank_1 | ten | four
+--------+-----+------
+ 1 | 0 | 0
+ 1 | 0 | 0
+ 3 | 4 | 0
+ 1 | 1 | 1
+ 1 | 1 | 1
+ 3 | 7 | 1
+ 1 | 0 | 2
+ 1 | 1 | 3
+ 2 | 3 | 3
+(9 rows)
+
+SELECT * FROM (SELECT row_number() OVER (PARTITION BY four ORDER BY ten) AS
rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 <= 3;
+ rank_1 | ten | four
+--------+-----+------
+ 1 | 0 | 2
+ 1 | 1 | 3
+ 2 | 3 | 3
+ 1 | 0 | 0
+ 2 | 0 | 0
+ 3 | 4 | 0
+ 1 | 1 | 1
+ 2 | 1 | 1
+ 3 | 7 | 1
+(9 rows)
+
+SELECT * FROM (SELECT dense_rank() OVER (PARTITION BY four ORDER BY ten) AS
rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 <= 3;
+ rank_1 | ten | four
+--------+-----+------
+ 1 | 0 | 2
+ 1 | 1 | 3
+ 2 | 3 | 3
+ 1 | 0 | 0
+ 1 | 0 | 0
+ 2 | 4 | 0
+ 1 | 1 | 1
+ 1 | 1 | 1
+ 2 | 7 | 1
+ 3 | 9 | 1
+(10 rows)
+
+set optimizer_force_split_window_function to on;
+SELECT * FROM (SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS rank_1,
ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 3;
+ rank_1 | ten | four
+--------+-----+------
+ 1 | 0 | 2
+ 1 | 1 | 3
+ 2 | 3 | 3
+ 1 | 0 | 0
+ 1 | 0 | 0
+ 1 | 1 | 1
+ 1 | 1 | 1
+(7 rows)
+
+SELECT * FROM (SELECT row_number() OVER (PARTITION BY four ORDER BY ten) AS
rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 3;
+ rank_1 | ten | four
+--------+-----+------
+ 1 | 0 | 2
+ 1 | 1 | 3
+ 2 | 3 | 3
+ 1 | 0 | 0
+ 2 | 0 | 0
+ 1 | 1 | 1
+ 2 | 1 | 1
+(7 rows)
+
+SELECT * FROM (SELECT dense_rank() OVER (PARTITION BY four ORDER BY ten) AS
rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 3;
+ rank_1 | ten | four
+--------+-----+------
+ 1 | 0 | 2
+ 1 | 1 | 3
+ 2 | 3 | 3
+ 1 | 0 | 0
+ 1 | 0 | 0
+ 2 | 4 | 0
+ 1 | 1 | 1
+ 1 | 1 | 1
+ 2 | 7 | 1
+(9 rows)
+
+SELECT * FROM (SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS rank_1,
ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 <= 3;
+ rank_1 | ten | four
+--------+-----+------
+ 1 | 0 | 0
+ 1 | 0 | 0
+ 3 | 4 | 0
+ 1 | 1 | 1
+ 1 | 1 | 1
+ 3 | 7 | 1
+ 1 | 0 | 2
+ 1 | 1 | 3
+ 2 | 3 | 3
+(9 rows)
+
+SELECT * FROM (SELECT row_number() OVER (PARTITION BY four ORDER BY ten) AS
rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 <= 3;
+ rank_1 | ten | four
+--------+-----+------
+ 1 | 0 | 2
+ 1 | 1 | 3
+ 2 | 3 | 3
+ 1 | 0 | 0
+ 2 | 0 | 0
+ 3 | 4 | 0
+ 1 | 1 | 1
+ 2 | 1 | 1
+ 3 | 7 | 1
+(9 rows)
+
+SELECT * FROM (SELECT dense_rank() OVER (PARTITION BY four ORDER BY ten) AS
rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 <= 3;
+ rank_1 | ten | four
+--------+-----+------
+ 1 | 0 | 2
+ 1 | 1 | 3
+ 2 | 3 | 3
+ 1 | 0 | 0
+ 1 | 0 | 0
+ 2 | 4 | 0
+ 1 | 1 | 1
+ 1 | 1 | 1
+ 2 | 7 | 1
+ 3 | 9 | 1
+(10 rows)
+
+reset optimizer_force_split_window_function;
-- last_value returns the last row of the frame, which is CURRENT ROW in ORDER
BY window.
-- the column `ten` is ordered, so we should call last_value on this
-- column. Using other cols the result is flaky because there are
@@ -1370,8 +1852,6 @@ CREATE TEMP VIEW v_window AS
SELECT i, min(i) over (order by i range between '1 day' preceding and
'10 days' following) as min_i
FROM generate_series(now(), now()+'100 days'::interval, '1 hour') i;
SELECT pg_get_viewdef('v_window');
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: Falling back to Postgres-based planner because GPORCA does not
support the following feature: Non-default collation
pg_get_viewdef
---------------------------------------------------------------------------------------------------------------------------
SELECT i.i,
+
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index b47f8a8483..70dd94fafa 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -84,6 +84,48 @@ SELECT lead(ten * 2, 1, -1.4) OVER (PARTITION BY four ORDER
BY ten), ten, four F
SELECT first_value(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM
tenk1 WHERE unique2 < 10;
+-- test split window func
+
+explain SELECT * FROM (SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS
rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 3;
+explain SELECT * FROM (SELECT row_number() OVER (PARTITION BY four ORDER BY
ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 3;
+explain SELECT * FROM (SELECT dense_rank() OVER (PARTITION BY four ORDER BY
ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 3;
+explain SELECT * FROM (SELECT percent_rank() OVER (PARTITION BY four ORDER BY
ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 0.5;
+set optimizer_force_split_window_function to on;
+-- worked
+explain SELECT * FROM (SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS
rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 3;
+explain SELECT * FROM (SELECT row_number() OVER (PARTITION BY four ORDER BY
ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 3;
+explain SELECT * FROM (SELECT dense_rank() OVER (PARTITION BY four ORDER BY
ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 3;
+
+explain SELECT * FROM (SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS
rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 <= 3;
+explain SELECT * FROM (SELECT row_number() OVER (PARTITION BY four ORDER BY
ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 <= 3;
+explain SELECT * FROM (SELECT dense_rank() OVER (PARTITION BY four ORDER BY
ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 <= 3;
+
+-- no worked
+explain SELECT * FROM (SELECT cume_dist() OVER (PARTITION BY four ORDER BY
ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 <= 1;
+explain SELECT * FROM (SELECT percent_rank() OVER (PARTITION BY four ORDER BY
ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 0.5;
+explain SELECT * FROM (SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS
rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 > 1;
+explain SELECT * FROM (SELECT row_number() OVER (PARTITION BY four ORDER BY
ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 > 1;
+explain SELECT * FROM (SELECT dense_rank() OVER (PARTITION BY four ORDER BY
ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 > 1;
+
+-- verify the split window function result
+set optimizer_force_split_window_function to off;
+SELECT * FROM (SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS rank_1,
ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 3;
+SELECT * FROM (SELECT row_number() OVER (PARTITION BY four ORDER BY ten) AS
rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 3;
+SELECT * FROM (SELECT dense_rank() OVER (PARTITION BY four ORDER BY ten) AS
rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 3;
+
+SELECT * FROM (SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS rank_1,
ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 <= 3;
+SELECT * FROM (SELECT row_number() OVER (PARTITION BY four ORDER BY ten) AS
rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 <= 3;
+SELECT * FROM (SELECT dense_rank() OVER (PARTITION BY four ORDER BY ten) AS
rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 <= 3;
+set optimizer_force_split_window_function to on;
+SELECT * FROM (SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS rank_1,
ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 3;
+SELECT * FROM (SELECT row_number() OVER (PARTITION BY four ORDER BY ten) AS
rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 3;
+SELECT * FROM (SELECT dense_rank() OVER (PARTITION BY four ORDER BY ten) AS
rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 3;
+
+SELECT * FROM (SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS rank_1,
ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 <= 3;
+SELECT * FROM (SELECT row_number() OVER (PARTITION BY four ORDER BY ten) AS
rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 <= 3;
+SELECT * FROM (SELECT dense_rank() OVER (PARTITION BY four ORDER BY ten) AS
rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 <= 3;
+reset optimizer_force_split_window_function;
+
-- last_value returns the last row of the frame, which is CURRENT ROW in ORDER
BY window.
-- the column `ten` is ordered, so we should call last_value on this
-- column. Using other cols the result is flaky because there are
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
