You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cloudberry.apache.org by ma...@apache.org on 2025/01/24 11:08:46 UTC
(cloudberry) 22/30: [ORCA] Fix option to enable multi-distinct agg (#15445)
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
commit 3fb13c7cc6a6fc72366472011e78190d9183ee2c
Author: David Kimura <dk...@vmware.com>
AuthorDate: Mon May 15 09:19:32 2023 -0700
[ORCA] Fix option to enable multi-distinct agg (#15445)
GUC optimizer_enable_multiple_distinct_aggs is supposed to enable
multi-DQA in ORCA. However, after commit 0ccfee593d3 that stopped
working.
Issue is that the offending commit updated child arguments of
CScalarAggFunc to store additional aggregate info (e.g. order/distinct
args). ORCA detects whether a query has multi-DQA by parsing a project
list. When the list format changed, ORCA failed to detect multi-DQA and
failed to trigger xform CXformGbAggWithMDQA2Join.
Fix parses the new format. Also, since we now allow multiple distinct
arguments we cannot simply pass the first distinct argument. Hence the
need to pass the list of distinct arguments.
---
.../data/dxl/minidump/MDQA-SameDQAColumn.mdp | 24 +-
.../data/dxl/minidump/MDQAs-Grouping-OrderBy.mdp | 405 +++++++++--
.../gporca/data/dxl/minidump/MDQAs-Grouping.mdp | 377 ++++++++--
.../gporca/data/dxl/minidump/MDQAs-Union.mdp | 808 ++++++++++++++++++---
src/backend/gporca/data/dxl/minidump/MDQAs1.mdp | 379 ++++++++--
.../gporca/libgpopt/src/xforms/CXformUtils.cpp | 5 +
src/test/regress/expected/gp_dqa.out | 484 ++++++++++++
src/test/regress/expected/gp_dqa_optimizer.out | 556 ++++++++++++++
src/test/regress/sql/gp_dqa.sql | 104 +++
9 files changed, 2860 insertions(+), 282 deletions(-)
diff --git a/src/backend/gporca/data/dxl/minidump/MDQA-SameDQAColumn.mdp b/src/backend/gporca/data/dxl/minidump/MDQA-SameDQAColumn.mdp
index a259e819c0..39b76e7708 100644
--- a/src/backend/gporca/data/dxl/minidump/MDQA-SameDQAColumn.mdp
+++ b/src/backend/gporca/data/dxl/minidump/MDQA-SameDQAColumn.mdp
@@ -90,6 +90,14 @@
</dxl:GPDBAgg>
<dxl:ColumnStatistics Mdid="1.359750.1.1.7" Name="cmax" Width="4.000000"/>
<dxl:ColumnStatistics Mdid="1.359750.1.1.6" Name="xmax" Width="4.000000"/>
+ <dxl:GPDBScalarOp Mdid="0.96.1.0" Name="=" ComparisonType="Eq">
+ <dxl:LeftType Mdid="0.23.1.0"/>
+ <dxl:RightType Mdid="0.23.1.0"/>
+ <dxl:ResultType Mdid="0.16.1.0"/>
+ <dxl:OpFunc Mdid="0.65.1.0"/>
+ <dxl:Commutator Mdid="0.96.1.0"/>
+ <dxl:InverseOp Mdid="0.518.1.0"/>
+ </dxl:GPDBScalarOp>
<dxl:GPDBScalarOp Mdid="0.97.1.0" Name="<" ComparisonType="LT">
<dxl:LeftType Mdid="0.23.1.0"/>
<dxl:RightType Mdid="0.23.1.0"/>
@@ -381,7 +389,7 @@
<dxl:ProjElem ColId="10" Alias="count">
<dxl:AggFunc AggMdid="0.2147.1.0" AggDistinct="false" AggStage="Normal" AggKind="n" AggArgTypes="">
<dxl:ValuesList ParamType="aggargs">
- <dxl:Ident ColId="13" ColName="ColRef_0013" TypeMdid="0.23.1.0"/>
+ <dxl:Ident ColId="13" ColName="ColRef_0013" TypeMdid="0.23.1.0"/>
</dxl:ValuesList>
<dxl:ValuesList ParamType="aggdirectargs"/>
<dxl:ValuesList ParamType="aggorder"/>
@@ -391,7 +399,7 @@
<dxl:ProjElem ColId="11" Alias="sum">
<dxl:AggFunc AggMdid="0.2108.1.0" AggDistinct="false" AggStage="Normal" AggKind="n" AggArgTypes="">
<dxl:ValuesList ParamType="aggargs">
- <dxl:Ident ColId="13" ColName="ColRef_0013" TypeMdid="0.23.1.0"/>
+ <dxl:Ident ColId="13" ColName="ColRef_0013" TypeMdid="0.23.1.0"/>
</dxl:ValuesList>
<dxl:ValuesList ParamType="aggdirectargs"/>
<dxl:ValuesList ParamType="aggorder"/>
@@ -401,7 +409,7 @@
<dxl:ProjElem ColId="12" Alias="avg">
<dxl:AggFunc AggMdid="0.2101.1.0" AggDistinct="false" AggStage="Final" AggKind="n" AggArgTypes="">
<dxl:ValuesList ParamType="aggargs">
- <dxl:Ident ColId="15" ColName="ColRef_0015" TypeMdid="0.17.1.0"/>
+ <dxl:Ident ColId="15" ColName="ColRef_0015" TypeMdid="0.17.1.0"/>
</dxl:ValuesList>
<dxl:ValuesList ParamType="aggdirectargs"/>
<dxl:ValuesList ParamType="aggorder"/>
@@ -422,7 +430,7 @@
<dxl:ProjElem ColId="15" Alias="ColRef_0015">
<dxl:AggFunc AggMdid="0.2101.1.0" AggDistinct="false" AggStage="Intermediate" AggKind="n" AggArgTypes="">
<dxl:ValuesList ParamType="aggargs">
- <dxl:Ident ColId="14" ColName="ColRef_0014" TypeMdid="0.17.1.0"/>
+ <dxl:Ident ColId="14" ColName="ColRef_0014" TypeMdid="0.17.1.0"/>
</dxl:ValuesList>
<dxl:ValuesList ParamType="aggdirectargs"/>
<dxl:ValuesList ParamType="aggorder"/>
@@ -510,10 +518,10 @@
<dxl:ProjElem ColId="14" Alias="ColRef_0014">
<dxl:AggFunc AggMdid="0.2101.1.0" AggDistinct="false" AggStage="Partial" AggKind="n" AggArgTypes="">
<dxl:ValuesList ParamType="aggargs">
- <dxl:OpExpr OperatorName="+" OperatorMdid="0.551.1.0" OperatorType="0.23.1.0">
- <dxl:Ident ColId="0" ColName="a" TypeMdid="0.23.1.0"/>
- <dxl:Ident ColId="1" ColName="b" TypeMdid="0.23.1.0"/>
- </dxl:OpExpr>
+ <dxl:OpExpr OperatorName="+" OperatorMdid="0.551.1.0" OperatorType="0.23.1.0">
+ <dxl:Ident ColId="0" ColName="a" TypeMdid="0.23.1.0"/>
+ <dxl:Ident ColId="1" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:OpExpr>
</dxl:ValuesList>
<dxl:ValuesList ParamType="aggdirectargs"/>
<dxl:ValuesList ParamType="aggorder"/>
diff --git a/src/backend/gporca/data/dxl/minidump/MDQAs-Grouping-OrderBy.mdp b/src/backend/gporca/data/dxl/minidump/MDQAs-Grouping-OrderBy.mdp
index 970f6d72c8..1c00e0c418 100644
--- a/src/backend/gporca/data/dxl/minidump/MDQAs-Grouping-OrderBy.mdp
+++ b/src/backend/gporca/data/dxl/minidump/MDQAs-Grouping-OrderBy.mdp
@@ -463,10 +463,10 @@ SQL:
</dxl:LogicalGroupBy>
</dxl:LogicalLimit>
</dxl:Query>
- <dxl:Plan Id="0" SpaceSize="4">
+ <dxl:Plan Id="0" SpaceSize="10400">
<dxl:Limit>
<dxl:Properties>
- <dxl:Cost StartupCost="0" TotalCost="431.018838" Rows="10.000000" Width="20"/>
+ <dxl:Cost StartupCost="0" TotalCost="1293.104458" Rows="10.000000" Width="20"/>
</dxl:Properties>
<dxl:ProjList>
<dxl:ProjElem ColId="0" Alias="a">
@@ -481,7 +481,7 @@ SQL:
</dxl:ProjList>
<dxl:GatherMotion InputSegments="0,1" OutputSegments="-1">
<dxl:Properties>
- <dxl:Cost StartupCost="0" TotalCost="431.018638" Rows="10.000000" Width="20"/>
+ <dxl:Cost StartupCost="0" TotalCost="1293.104258" Rows="10.000000" Width="20"/>
</dxl:Properties>
<dxl:ProjList>
<dxl:ProjElem ColId="0" Alias="a">
@@ -500,7 +500,7 @@ SQL:
</dxl:SortingColumnList>
<dxl:Limit>
<dxl:Properties>
- <dxl:Cost StartupCost="0" TotalCost="431.017740" Rows="10.000000" Width="20"/>
+ <dxl:Cost StartupCost="0" TotalCost="1293.103360" Rows="10.000000" Width="20"/>
</dxl:Properties>
<dxl:ProjList>
<dxl:ProjElem ColId="0" Alias="a">
@@ -513,86 +513,381 @@ SQL:
<dxl:Ident ColId="10" ColName="count" TypeMdid="0.20.1.0"/>
</dxl:ProjElem>
</dxl:ProjList>
- <dxl:Aggregate AggregationStrategy="Sorted" StreamSafe="false">
+ <dxl:Sort SortDiscardDuplicates="false">
<dxl:Properties>
- <dxl:Cost StartupCost="0" TotalCost="431.017640" Rows="111.000000" Width="20"/>
+ <dxl:Cost StartupCost="0" TotalCost="1293.103260" Rows="111.000000" Width="20"/>
</dxl:Properties>
- <dxl:GroupingColumns>
- <dxl:GroupingColumn ColId="0"/>
- </dxl:GroupingColumns>
<dxl:ProjList>
<dxl:ProjElem ColId="0" Alias="a">
<dxl:Ident ColId="0" ColName="a" TypeMdid="0.23.1.0"/>
</dxl:ProjElem>
<dxl:ProjElem ColId="9" Alias="count">
- <dxl:AggFunc AggMdid="0.2147.1.0" AggDistinct="true" AggStage="Normal" AggKind="n" AggArgTypes="">
- <dxl:ValuesList ParamType="aggargs">
- <dxl:Ident ColId="0" ColName="a" TypeMdid="0.23.1.0"/>
- </dxl:ValuesList>
- <dxl:ValuesList ParamType="aggdirectargs"/>
- <dxl:ValuesList ParamType="aggorder"/>
- <dxl:ValuesList ParamType="aggdistinct"/>
- </dxl:AggFunc>
+ <dxl:Ident ColId="9" ColName="count" TypeMdid="0.20.1.0"/>
</dxl:ProjElem>
<dxl:ProjElem ColId="10" Alias="count">
- <dxl:AggFunc AggMdid="0.2147.1.0" AggDistinct="true" AggStage="Normal" AggKind="n" AggArgTypes="">
- <dxl:ValuesList ParamType="aggargs">
- <dxl:Ident ColId="1" ColName="b" TypeMdid="0.23.1.0"/>
- </dxl:ValuesList>
- <dxl:ValuesList ParamType="aggdirectargs"/>
- <dxl:ValuesList ParamType="aggorder"/>
- <dxl:ValuesList ParamType="aggdistinct"/>
- </dxl:AggFunc>
+ <dxl:Ident ColId="10" ColName="count" TypeMdid="0.20.1.0"/>
</dxl:ProjElem>
</dxl:ProjList>
<dxl:Filter/>
- <dxl:Sort SortDiscardDuplicates="false">
+ <dxl:SortingColumnList>
+ <dxl:SortingColumn ColId="0" SortOperatorMdid="0.97.1.0" SortOperatorName="<" SortNullsFirst="false"/>
+ </dxl:SortingColumnList>
+ <dxl:LimitCount/>
+ <dxl:LimitOffset/>
+ <dxl:Sequence>
<dxl:Properties>
- <dxl:Cost StartupCost="0" TotalCost="431.016573" Rows="111.000000" Width="8"/>
+ <dxl:Cost StartupCost="0" TotalCost="1293.066792" Rows="111.000000" Width="20"/>
</dxl:Properties>
<dxl:ProjList>
<dxl:ProjElem ColId="0" Alias="a">
<dxl:Ident ColId="0" ColName="a" TypeMdid="0.23.1.0"/>
</dxl:ProjElem>
- <dxl:ProjElem ColId="1" Alias="b">
- <dxl:Ident ColId="1" ColName="b" TypeMdid="0.23.1.0"/>
+ <dxl:ProjElem ColId="9" Alias="count">
+ <dxl:Ident ColId="9" ColName="count" TypeMdid="0.20.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="10" Alias="count">
+ <dxl:Ident ColId="10" ColName="count" TypeMdid="0.20.1.0"/>
</dxl:ProjElem>
</dxl:ProjList>
- <dxl:Filter/>
- <dxl:SortingColumnList>
- <dxl:SortingColumn ColId="0" SortOperatorMdid="0.97.1.0" SortOperatorName="<" SortNullsFirst="false"/>
- </dxl:SortingColumnList>
- <dxl:LimitCount/>
- <dxl:LimitOffset/>
- <dxl:TableScan>
+ <dxl:CTEProducer CTEId="0" Columns="11,12,13,14,15,16,17,18,19">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.005138" Rows="111.000000" Width="1"/>
+ </dxl:Properties>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="11" Alias="a">
+ <dxl:Ident ColId="11" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="12" Alias="b">
+ <dxl:Ident ColId="12" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="13" Alias="ctid">
+ <dxl:Ident ColId="13" ColName="ctid" TypeMdid="0.27.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="14" Alias="xmin">
+ <dxl:Ident ColId="14" ColName="xmin" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="15" Alias="cmin">
+ <dxl:Ident ColId="15" ColName="cmin" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="16" Alias="xmax">
+ <dxl:Ident ColId="16" ColName="xmax" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="17" Alias="cmax">
+ <dxl:Ident ColId="17" ColName="cmax" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="18" Alias="tableoid">
+ <dxl:Ident ColId="18" ColName="tableoid" TypeMdid="0.26.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="19" Alias="gp_segment_id">
+ <dxl:Ident ColId="19" ColName="gp_segment_id" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:TableScan>
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.001160" Rows="111.000000" Width="38"/>
+ </dxl:Properties>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="11" Alias="a">
+ <dxl:Ident ColId="11" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="12" Alias="b">
+ <dxl:Ident ColId="12" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="13" Alias="ctid">
+ <dxl:Ident ColId="13" ColName="ctid" TypeMdid="0.27.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="14" Alias="xmin">
+ <dxl:Ident ColId="14" ColName="xmin" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="15" Alias="cmin">
+ <dxl:Ident ColId="15" ColName="cmin" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="16" Alias="xmax">
+ <dxl:Ident ColId="16" ColName="xmax" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="17" Alias="cmax">
+ <dxl:Ident ColId="17" ColName="cmax" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="18" Alias="tableoid">
+ <dxl:Ident ColId="18" ColName="tableoid" TypeMdid="0.26.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="19" Alias="gp_segment_id">
+ <dxl:Ident ColId="19" ColName="gp_segment_id" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:TableDescriptor Mdid="6.1639448.1.1" TableName="t1">
+ <dxl:Columns>
+ <dxl:Column ColId="11" Attno="1" ColName="a" TypeMdid="0.23.1.0"/>
+ <dxl:Column ColId="12" Attno="2" ColName="b" TypeMdid="0.23.1.0"/>
+ <dxl:Column ColId="13" Attno="-1" ColName="ctid" TypeMdid="0.27.1.0"/>
+ <dxl:Column ColId="14" Attno="-3" ColName="xmin" TypeMdid="0.28.1.0"/>
+ <dxl:Column ColId="15" Attno="-4" ColName="cmin" TypeMdid="0.29.1.0"/>
+ <dxl:Column ColId="16" Attno="-5" ColName="xmax" TypeMdid="0.28.1.0"/>
+ <dxl:Column ColId="17" Attno="-6" ColName="cmax" TypeMdid="0.29.1.0"/>
+ <dxl:Column ColId="18" Attno="-7" ColName="tableoid" TypeMdid="0.26.1.0"/>
+ <dxl:Column ColId="19" Attno="-8" ColName="gp_segment_id" TypeMdid="0.23.1.0"/>
+ </dxl:Columns>
+ </dxl:TableDescriptor>
+ </dxl:TableScan>
+ </dxl:CTEProducer>
+ <dxl:HashJoin JoinType="Inner">
<dxl:Properties>
- <dxl:Cost StartupCost="0" TotalCost="431.001160" Rows="111.000000" Width="8"/>
+ <dxl:Cost StartupCost="0" TotalCost="862.060544" Rows="111.000000" Width="20"/>
</dxl:Properties>
<dxl:ProjList>
<dxl:ProjElem ColId="0" Alias="a">
<dxl:Ident ColId="0" ColName="a" TypeMdid="0.23.1.0"/>
</dxl:ProjElem>
- <dxl:ProjElem ColId="1" Alias="b">
- <dxl:Ident ColId="1" ColName="b" TypeMdid="0.23.1.0"/>
+ <dxl:ProjElem ColId="9" Alias="count">
+ <dxl:Ident ColId="9" ColName="count" TypeMdid="0.20.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="10" Alias="count">
+ <dxl:Ident ColId="10" ColName="count" TypeMdid="0.20.1.0"/>
</dxl:ProjElem>
</dxl:ProjList>
<dxl:Filter/>
- <dxl:TableDescriptor Mdid="6.1639448.1.1" TableName="t1">
- <dxl:Columns>
- <dxl:Column ColId="0" Attno="1" ColName="a" TypeMdid="0.23.1.0"/>
- <dxl:Column ColId="1" Attno="2" ColName="b" TypeMdid="0.23.1.0"/>
- <dxl:Column ColId="2" Attno="-1" ColName="ctid" TypeMdid="0.27.1.0"/>
- <dxl:Column ColId="3" Attno="-3" ColName="xmin" TypeMdid="0.28.1.0"/>
- <dxl:Column ColId="4" Attno="-4" ColName="cmin" TypeMdid="0.29.1.0"/>
- <dxl:Column ColId="5" Attno="-5" ColName="xmax" TypeMdid="0.28.1.0"/>
- <dxl:Column ColId="6" Attno="-6" ColName="cmax" TypeMdid="0.29.1.0"/>
- <dxl:Column ColId="7" Attno="-7" ColName="tableoid" TypeMdid="0.26.1.0"/>
- <dxl:Column ColId="8" Attno="-8" ColName="gp_segment_id" TypeMdid="0.23.1.0"/>
- </dxl:Columns>
- </dxl:TableDescriptor>
- </dxl:TableScan>
- </dxl:Sort>
- </dxl:Aggregate>
+ <dxl:JoinFilter/>
+ <dxl:HashCondList>
+ <dxl:Not>
+ <dxl:IsDistinctFrom OperatorMdid="0.96.1.0">
+ <dxl:Ident ColId="0" ColName="a" TypeMdid="0.23.1.0"/>
+ <dxl:Ident ColId="20" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:IsDistinctFrom>
+ </dxl:Not>
+ </dxl:HashCondList>
+ <dxl:Aggregate AggregationStrategy="Sorted" StreamSafe="false">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.008771" Rows="111.000000" Width="12"/>
+ </dxl:Properties>
+ <dxl:GroupingColumns>
+ <dxl:GroupingColumn ColId="0"/>
+ </dxl:GroupingColumns>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="9" Alias="count">
+ <dxl:AggFunc AggMdid="0.2147.1.0" AggDistinct="false" AggStage="Normal" AggKind="n" AggArgTypes="">
+ <dxl:ValuesList ParamType="aggargs">
+ <dxl:Ident ColId="0" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ValuesList>
+ <dxl:ValuesList ParamType="aggdirectargs"/>
+ <dxl:ValuesList ParamType="aggorder"/>
+ <dxl:ValuesList ParamType="aggdistinct"/>
+ </dxl:AggFunc>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="0" Alias="a">
+ <dxl:Ident ColId="0" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:Aggregate AggregationStrategy="Sorted" StreamSafe="false">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.008175" Rows="111.000000" Width="4"/>
+ </dxl:Properties>
+ <dxl:GroupingColumns>
+ <dxl:GroupingColumn ColId="0"/>
+ </dxl:GroupingColumns>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="0" Alias="a">
+ <dxl:Ident ColId="0" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:Sort SortDiscardDuplicates="false">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.007829" Rows="111.000000" Width="4"/>
+ </dxl:Properties>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="0" Alias="a">
+ <dxl:Ident ColId="0" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="1" Alias="b">
+ <dxl:Ident ColId="1" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="2" Alias="ctid">
+ <dxl:Ident ColId="2" ColName="ctid" TypeMdid="0.27.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="3" Alias="xmin">
+ <dxl:Ident ColId="3" ColName="xmin" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="4" Alias="cmin">
+ <dxl:Ident ColId="4" ColName="cmin" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="5" Alias="xmax">
+ <dxl:Ident ColId="5" ColName="xmax" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="6" Alias="cmax">
+ <dxl:Ident ColId="6" ColName="cmax" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="7" Alias="tableoid">
+ <dxl:Ident ColId="7" ColName="tableoid" TypeMdid="0.26.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="8" Alias="gp_segment_id">
+ <dxl:Ident ColId="8" ColName="gp_segment_id" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:SortingColumnList>
+ <dxl:SortingColumn ColId="0" SortOperatorMdid="0.97.1.0" SortOperatorName="<" SortNullsFirst="false"/>
+ </dxl:SortingColumnList>
+ <dxl:LimitCount/>
+ <dxl:LimitOffset/>
+ <dxl:CTEConsumer CTEId="0" Columns="0,1,2,3,4,5,6,7,8">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.000535" Rows="111.000000" Width="4"/>
+ </dxl:Properties>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="0" Alias="a">
+ <dxl:Ident ColId="0" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="1" Alias="b">
+ <dxl:Ident ColId="1" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="2" Alias="ctid">
+ <dxl:Ident ColId="2" ColName="ctid" TypeMdid="0.27.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="3" Alias="xmin">
+ <dxl:Ident ColId="3" ColName="xmin" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="4" Alias="cmin">
+ <dxl:Ident ColId="4" ColName="cmin" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="5" Alias="xmax">
+ <dxl:Ident ColId="5" ColName="xmax" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="6" Alias="cmax">
+ <dxl:Ident ColId="6" ColName="cmax" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="7" Alias="tableoid">
+ <dxl:Ident ColId="7" ColName="tableoid" TypeMdid="0.26.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="8" Alias="gp_segment_id">
+ <dxl:Ident ColId="8" ColName="gp_segment_id" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ </dxl:CTEConsumer>
+ </dxl:Sort>
+ </dxl:Aggregate>
+ </dxl:Aggregate>
+ <dxl:Aggregate AggregationStrategy="Sorted" StreamSafe="false">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.017168" Rows="111.000000" Width="12"/>
+ </dxl:Properties>
+ <dxl:GroupingColumns>
+ <dxl:GroupingColumn ColId="20"/>
+ </dxl:GroupingColumns>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="10" Alias="count">
+ <dxl:AggFunc AggMdid="0.2147.1.0" AggDistinct="false" AggStage="Normal" AggKind="n" AggArgTypes="">
+ <dxl:ValuesList ParamType="aggargs">
+ <dxl:Ident ColId="21" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ValuesList>
+ <dxl:ValuesList ParamType="aggdirectargs"/>
+ <dxl:ValuesList ParamType="aggorder"/>
+ <dxl:ValuesList ParamType="aggdistinct"/>
+ </dxl:AggFunc>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="20" Alias="a">
+ <dxl:Ident ColId="20" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:Aggregate AggregationStrategy="Sorted" StreamSafe="false">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.016350" Rows="111.000000" Width="8"/>
+ </dxl:Properties>
+ <dxl:GroupingColumns>
+ <dxl:GroupingColumn ColId="20"/>
+ <dxl:GroupingColumn ColId="21"/>
+ </dxl:GroupingColumns>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="20" Alias="a">
+ <dxl:Ident ColId="20" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="21" Alias="b">
+ <dxl:Ident ColId="21" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:Sort SortDiscardDuplicates="false">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.015657" Rows="111.000000" Width="8"/>
+ </dxl:Properties>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="20" Alias="a">
+ <dxl:Ident ColId="20" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="21" Alias="b">
+ <dxl:Ident ColId="21" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="22" Alias="ctid">
+ <dxl:Ident ColId="22" ColName="ctid" TypeMdid="0.27.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="23" Alias="xmin">
+ <dxl:Ident ColId="23" ColName="xmin" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="24" Alias="cmin">
+ <dxl:Ident ColId="24" ColName="cmin" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="25" Alias="xmax">
+ <dxl:Ident ColId="25" ColName="xmax" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="26" Alias="cmax">
+ <dxl:Ident ColId="26" ColName="cmax" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="27" Alias="tableoid">
+ <dxl:Ident ColId="27" ColName="tableoid" TypeMdid="0.26.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="28" Alias="gp_segment_id">
+ <dxl:Ident ColId="28" ColName="gp_segment_id" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:SortingColumnList>
+ <dxl:SortingColumn ColId="20" SortOperatorMdid="0.97.1.0" SortOperatorName="<" SortNullsFirst="false"/>
+ <dxl:SortingColumn ColId="21" SortOperatorMdid="0.97.1.0" SortOperatorName="<" SortNullsFirst="false"/>
+ </dxl:SortingColumnList>
+ <dxl:LimitCount/>
+ <dxl:LimitOffset/>
+ <dxl:CTEConsumer CTEId="0" Columns="20,21,22,23,24,25,26,27,28">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.001070" Rows="111.000000" Width="8"/>
+ </dxl:Properties>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="20" Alias="a">
+ <dxl:Ident ColId="20" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="21" Alias="b">
+ <dxl:Ident ColId="21" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="22" Alias="ctid">
+ <dxl:Ident ColId="22" ColName="ctid" TypeMdid="0.27.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="23" Alias="xmin">
+ <dxl:Ident ColId="23" ColName="xmin" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="24" Alias="cmin">
+ <dxl:Ident ColId="24" ColName="cmin" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="25" Alias="xmax">
+ <dxl:Ident ColId="25" ColName="xmax" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="26" Alias="cmax">
+ <dxl:Ident ColId="26" ColName="cmax" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="27" Alias="tableoid">
+ <dxl:Ident ColId="27" ColName="tableoid" TypeMdid="0.26.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="28" Alias="gp_segment_id">
+ <dxl:Ident ColId="28" ColName="gp_segment_id" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ </dxl:CTEConsumer>
+ </dxl:Sort>
+ </dxl:Aggregate>
+ </dxl:Aggregate>
+ </dxl:HashJoin>
+ </dxl:Sequence>
+ </dxl:Sort>
<dxl:LimitCount>
<dxl:ConstValue TypeMdid="0.20.1.0" Value="10"/>
</dxl:LimitCount>
diff --git a/src/backend/gporca/data/dxl/minidump/MDQAs-Grouping.mdp b/src/backend/gporca/data/dxl/minidump/MDQAs-Grouping.mdp
index 79621fcf5e..fcbc3e19a9 100644
--- a/src/backend/gporca/data/dxl/minidump/MDQAs-Grouping.mdp
+++ b/src/backend/gporca/data/dxl/minidump/MDQAs-Grouping.mdp
@@ -454,10 +454,10 @@ SQL:
</dxl:LogicalGet>
</dxl:LogicalGroupBy>
</dxl:Query>
- <dxl:Plan Id="0" SpaceSize="1">
+ <dxl:Plan Id="0" SpaceSize="9200">
<dxl:GatherMotion InputSegments="0,1" OutputSegments="-1">
<dxl:Properties>
- <dxl:Cost StartupCost="0" TotalCost="431.027608" Rows="111.000000" Width="20"/>
+ <dxl:Cost StartupCost="0" TotalCost="1293.076760" Rows="111.000000" Width="20"/>
</dxl:Properties>
<dxl:ProjList>
<dxl:ProjElem ColId="0" Alias="a">
@@ -472,86 +472,359 @@ SQL:
</dxl:ProjList>
<dxl:Filter/>
<dxl:SortingColumnList/>
- <dxl:Aggregate AggregationStrategy="Sorted" StreamSafe="false">
+ <dxl:Sequence>
<dxl:Properties>
- <dxl:Cost StartupCost="0" TotalCost="431.017640" Rows="111.000000" Width="20"/>
+ <dxl:Cost StartupCost="0" TotalCost="1293.066792" Rows="111.000000" Width="20"/>
</dxl:Properties>
- <dxl:GroupingColumns>
- <dxl:GroupingColumn ColId="0"/>
- </dxl:GroupingColumns>
<dxl:ProjList>
<dxl:ProjElem ColId="0" Alias="a">
<dxl:Ident ColId="0" ColName="a" TypeMdid="0.23.1.0"/>
</dxl:ProjElem>
<dxl:ProjElem ColId="9" Alias="count">
- <dxl:AggFunc AggMdid="0.2147.1.0" AggDistinct="true" AggStage="Normal" AggKind="n" AggArgTypes="">
- <dxl:ValuesList ParamType="aggargs">
- <dxl:Ident ColId="0" ColName="a" TypeMdid="0.23.1.0"/>
- </dxl:ValuesList>
- <dxl:ValuesList ParamType="aggdirectargs"/>
- <dxl:ValuesList ParamType="aggorder"/>
- <dxl:ValuesList ParamType="aggdistinct"/>
- </dxl:AggFunc>
+ <dxl:Ident ColId="9" ColName="count" TypeMdid="0.20.1.0"/>
</dxl:ProjElem>
<dxl:ProjElem ColId="10" Alias="count">
- <dxl:AggFunc AggMdid="0.2147.1.0" AggDistinct="true" AggStage="Normal" AggKind="n" AggArgTypes="">
- <dxl:ValuesList ParamType="aggargs">
- <dxl:Ident ColId="1" ColName="b" TypeMdid="0.23.1.0"/>
- </dxl:ValuesList>
- <dxl:ValuesList ParamType="aggdirectargs"/>
- <dxl:ValuesList ParamType="aggorder"/>
- <dxl:ValuesList ParamType="aggdistinct"/>
- </dxl:AggFunc>
+ <dxl:Ident ColId="10" ColName="count" TypeMdid="0.20.1.0"/>
</dxl:ProjElem>
</dxl:ProjList>
- <dxl:Filter/>
- <dxl:Sort SortDiscardDuplicates="false">
+ <dxl:CTEProducer CTEId="0" Columns="11,12,13,14,15,16,17,18,19">
<dxl:Properties>
- <dxl:Cost StartupCost="0" TotalCost="431.016573" Rows="111.000000" Width="8"/>
+ <dxl:Cost StartupCost="0" TotalCost="431.005138" Rows="111.000000" Width="1"/>
</dxl:Properties>
<dxl:ProjList>
- <dxl:ProjElem ColId="0" Alias="a">
- <dxl:Ident ColId="0" ColName="a" TypeMdid="0.23.1.0"/>
+ <dxl:ProjElem ColId="11" Alias="a">
+ <dxl:Ident ColId="11" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="12" Alias="b">
+ <dxl:Ident ColId="12" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="13" Alias="ctid">
+ <dxl:Ident ColId="13" ColName="ctid" TypeMdid="0.27.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="14" Alias="xmin">
+ <dxl:Ident ColId="14" ColName="xmin" TypeMdid="0.28.1.0"/>
</dxl:ProjElem>
- <dxl:ProjElem ColId="1" Alias="b">
- <dxl:Ident ColId="1" ColName="b" TypeMdid="0.23.1.0"/>
+ <dxl:ProjElem ColId="15" Alias="cmin">
+ <dxl:Ident ColId="15" ColName="cmin" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="16" Alias="xmax">
+ <dxl:Ident ColId="16" ColName="xmax" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="17" Alias="cmax">
+ <dxl:Ident ColId="17" ColName="cmax" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="18" Alias="tableoid">
+ <dxl:Ident ColId="18" ColName="tableoid" TypeMdid="0.26.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="19" Alias="gp_segment_id">
+ <dxl:Ident ColId="19" ColName="gp_segment_id" TypeMdid="0.23.1.0"/>
</dxl:ProjElem>
</dxl:ProjList>
- <dxl:Filter/>
- <dxl:SortingColumnList>
- <dxl:SortingColumn ColId="0" SortOperatorMdid="0.97.1.0" SortOperatorName="<" SortNullsFirst="false"/>
- </dxl:SortingColumnList>
- <dxl:LimitCount/>
- <dxl:LimitOffset/>
<dxl:TableScan>
<dxl:Properties>
- <dxl:Cost StartupCost="0" TotalCost="431.001160" Rows="111.000000" Width="8"/>
+ <dxl:Cost StartupCost="0" TotalCost="431.001160" Rows="111.000000" Width="38"/>
</dxl:Properties>
<dxl:ProjList>
- <dxl:ProjElem ColId="0" Alias="a">
- <dxl:Ident ColId="0" ColName="a" TypeMdid="0.23.1.0"/>
+ <dxl:ProjElem ColId="11" Alias="a">
+ <dxl:Ident ColId="11" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="12" Alias="b">
+ <dxl:Ident ColId="12" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="13" Alias="ctid">
+ <dxl:Ident ColId="13" ColName="ctid" TypeMdid="0.27.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="14" Alias="xmin">
+ <dxl:Ident ColId="14" ColName="xmin" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="15" Alias="cmin">
+ <dxl:Ident ColId="15" ColName="cmin" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="16" Alias="xmax">
+ <dxl:Ident ColId="16" ColName="xmax" TypeMdid="0.28.1.0"/>
</dxl:ProjElem>
- <dxl:ProjElem ColId="1" Alias="b">
- <dxl:Ident ColId="1" ColName="b" TypeMdid="0.23.1.0"/>
+ <dxl:ProjElem ColId="17" Alias="cmax">
+ <dxl:Ident ColId="17" ColName="cmax" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="18" Alias="tableoid">
+ <dxl:Ident ColId="18" ColName="tableoid" TypeMdid="0.26.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="19" Alias="gp_segment_id">
+ <dxl:Ident ColId="19" ColName="gp_segment_id" TypeMdid="0.23.1.0"/>
</dxl:ProjElem>
</dxl:ProjList>
<dxl:Filter/>
<dxl:TableDescriptor Mdid="6.1639448.1.1" TableName="t1">
<dxl:Columns>
- <dxl:Column ColId="0" Attno="1" ColName="a" TypeMdid="0.23.1.0"/>
- <dxl:Column ColId="1" Attno="2" ColName="b" TypeMdid="0.23.1.0"/>
- <dxl:Column ColId="2" Attno="-1" ColName="ctid" TypeMdid="0.27.1.0"/>
- <dxl:Column ColId="3" Attno="-3" ColName="xmin" TypeMdid="0.28.1.0"/>
- <dxl:Column ColId="4" Attno="-4" ColName="cmin" TypeMdid="0.29.1.0"/>
- <dxl:Column ColId="5" Attno="-5" ColName="xmax" TypeMdid="0.28.1.0"/>
- <dxl:Column ColId="6" Attno="-6" ColName="cmax" TypeMdid="0.29.1.0"/>
- <dxl:Column ColId="7" Attno="-7" ColName="tableoid" TypeMdid="0.26.1.0"/>
- <dxl:Column ColId="8" Attno="-8" ColName="gp_segment_id" TypeMdid="0.23.1.0"/>
+ <dxl:Column ColId="11" Attno="1" ColName="a" TypeMdid="0.23.1.0"/>
+ <dxl:Column ColId="12" Attno="2" ColName="b" TypeMdid="0.23.1.0"/>
+ <dxl:Column ColId="13" Attno="-1" ColName="ctid" TypeMdid="0.27.1.0"/>
+ <dxl:Column ColId="14" Attno="-3" ColName="xmin" TypeMdid="0.28.1.0"/>
+ <dxl:Column ColId="15" Attno="-4" ColName="cmin" TypeMdid="0.29.1.0"/>
+ <dxl:Column ColId="16" Attno="-5" ColName="xmax" TypeMdid="0.28.1.0"/>
+ <dxl:Column ColId="17" Attno="-6" ColName="cmax" TypeMdid="0.29.1.0"/>
+ <dxl:Column ColId="18" Attno="-7" ColName="tableoid" TypeMdid="0.26.1.0"/>
+ <dxl:Column ColId="19" Attno="-8" ColName="gp_segment_id" TypeMdid="0.23.1.0"/>
</dxl:Columns>
</dxl:TableDescriptor>
</dxl:TableScan>
- </dxl:Sort>
- </dxl:Aggregate>
+ </dxl:CTEProducer>
+ <dxl:HashJoin JoinType="Inner">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="862.060544" Rows="110.999992" Width="20"/>
+ </dxl:Properties>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="0" Alias="a">
+ <dxl:Ident ColId="0" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="9" Alias="count">
+ <dxl:Ident ColId="9" ColName="count" TypeMdid="0.20.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="10" Alias="count">
+ <dxl:Ident ColId="10" ColName="count" TypeMdid="0.20.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:JoinFilter/>
+ <dxl:HashCondList>
+ <dxl:Not>
+ <dxl:IsDistinctFrom OperatorMdid="0.96.1.0">
+ <dxl:Ident ColId="0" ColName="a" TypeMdid="0.23.1.0"/>
+ <dxl:Ident ColId="20" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:IsDistinctFrom>
+ </dxl:Not>
+ </dxl:HashCondList>
+ <dxl:Aggregate AggregationStrategy="Sorted" StreamSafe="false">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.008771" Rows="111.000000" Width="12"/>
+ </dxl:Properties>
+ <dxl:GroupingColumns>
+ <dxl:GroupingColumn ColId="0"/>
+ </dxl:GroupingColumns>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="9" Alias="count">
+ <dxl:AggFunc AggMdid="0.2147.1.0" AggDistinct="false" AggStage="Normal" AggKind="n" AggArgTypes="">
+ <dxl:ValuesList ParamType="aggargs">
+ <dxl:Ident ColId="0" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ValuesList>
+ <dxl:ValuesList ParamType="aggdirectargs"/>
+ <dxl:ValuesList ParamType="aggorder"/>
+ <dxl:ValuesList ParamType="aggdistinct"/>
+ </dxl:AggFunc>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="0" Alias="a">
+ <dxl:Ident ColId="0" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:Aggregate AggregationStrategy="Sorted" StreamSafe="false">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.008175" Rows="111.000000" Width="4"/>
+ </dxl:Properties>
+ <dxl:GroupingColumns>
+ <dxl:GroupingColumn ColId="0"/>
+ </dxl:GroupingColumns>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="0" Alias="a">
+ <dxl:Ident ColId="0" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:Sort SortDiscardDuplicates="false">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.007829" Rows="111.000000" Width="4"/>
+ </dxl:Properties>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="0" Alias="a">
+ <dxl:Ident ColId="0" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="1" Alias="b">
+ <dxl:Ident ColId="1" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="2" Alias="ctid">
+ <dxl:Ident ColId="2" ColName="ctid" TypeMdid="0.27.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="3" Alias="xmin">
+ <dxl:Ident ColId="3" ColName="xmin" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="4" Alias="cmin">
+ <dxl:Ident ColId="4" ColName="cmin" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="5" Alias="xmax">
+ <dxl:Ident ColId="5" ColName="xmax" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="6" Alias="cmax">
+ <dxl:Ident ColId="6" ColName="cmax" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="7" Alias="tableoid">
+ <dxl:Ident ColId="7" ColName="tableoid" TypeMdid="0.26.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="8" Alias="gp_segment_id">
+ <dxl:Ident ColId="8" ColName="gp_segment_id" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:SortingColumnList>
+ <dxl:SortingColumn ColId="0" SortOperatorMdid="0.97.1.0" SortOperatorName="<" SortNullsFirst="false"/>
+ </dxl:SortingColumnList>
+ <dxl:LimitCount/>
+ <dxl:LimitOffset/>
+ <dxl:CTEConsumer CTEId="0" Columns="0,1,2,3,4,5,6,7,8">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.000535" Rows="111.000000" Width="4"/>
+ </dxl:Properties>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="0" Alias="a">
+ <dxl:Ident ColId="0" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="1" Alias="b">
+ <dxl:Ident ColId="1" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="2" Alias="ctid">
+ <dxl:Ident ColId="2" ColName="ctid" TypeMdid="0.27.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="3" Alias="xmin">
+ <dxl:Ident ColId="3" ColName="xmin" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="4" Alias="cmin">
+ <dxl:Ident ColId="4" ColName="cmin" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="5" Alias="xmax">
+ <dxl:Ident ColId="5" ColName="xmax" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="6" Alias="cmax">
+ <dxl:Ident ColId="6" ColName="cmax" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="7" Alias="tableoid">
+ <dxl:Ident ColId="7" ColName="tableoid" TypeMdid="0.26.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="8" Alias="gp_segment_id">
+ <dxl:Ident ColId="8" ColName="gp_segment_id" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ </dxl:CTEConsumer>
+ </dxl:Sort>
+ </dxl:Aggregate>
+ </dxl:Aggregate>
+ <dxl:Aggregate AggregationStrategy="Sorted" StreamSafe="false">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.017168" Rows="111.000000" Width="12"/>
+ </dxl:Properties>
+ <dxl:GroupingColumns>
+ <dxl:GroupingColumn ColId="20"/>
+ </dxl:GroupingColumns>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="10" Alias="count">
+ <dxl:AggFunc AggMdid="0.2147.1.0" AggDistinct="false" AggStage="Normal" AggKind="n" AggArgTypes="">
+ <dxl:ValuesList ParamType="aggargs">
+ <dxl:Ident ColId="21" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ValuesList>
+ <dxl:ValuesList ParamType="aggdirectargs"/>
+ <dxl:ValuesList ParamType="aggorder"/>
+ <dxl:ValuesList ParamType="aggdistinct"/>
+ </dxl:AggFunc>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="20" Alias="a">
+ <dxl:Ident ColId="20" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:Aggregate AggregationStrategy="Sorted" StreamSafe="false">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.016350" Rows="111.000000" Width="8"/>
+ </dxl:Properties>
+ <dxl:GroupingColumns>
+ <dxl:GroupingColumn ColId="20"/>
+ <dxl:GroupingColumn ColId="21"/>
+ </dxl:GroupingColumns>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="20" Alias="a">
+ <dxl:Ident ColId="20" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="21" Alias="b">
+ <dxl:Ident ColId="21" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:Sort SortDiscardDuplicates="false">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.015657" Rows="111.000000" Width="8"/>
+ </dxl:Properties>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="20" Alias="a">
+ <dxl:Ident ColId="20" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="21" Alias="b">
+ <dxl:Ident ColId="21" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="22" Alias="ctid">
+ <dxl:Ident ColId="22" ColName="ctid" TypeMdid="0.27.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="23" Alias="xmin">
+ <dxl:Ident ColId="23" ColName="xmin" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="24" Alias="cmin">
+ <dxl:Ident ColId="24" ColName="cmin" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="25" Alias="xmax">
+ <dxl:Ident ColId="25" ColName="xmax" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="26" Alias="cmax">
+ <dxl:Ident ColId="26" ColName="cmax" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="27" Alias="tableoid">
+ <dxl:Ident ColId="27" ColName="tableoid" TypeMdid="0.26.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="28" Alias="gp_segment_id">
+ <dxl:Ident ColId="28" ColName="gp_segment_id" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:SortingColumnList>
+ <dxl:SortingColumn ColId="20" SortOperatorMdid="0.97.1.0" SortOperatorName="<" SortNullsFirst="false"/>
+ <dxl:SortingColumn ColId="21" SortOperatorMdid="0.97.1.0" SortOperatorName="<" SortNullsFirst="false"/>
+ </dxl:SortingColumnList>
+ <dxl:LimitCount/>
+ <dxl:LimitOffset/>
+ <dxl:CTEConsumer CTEId="0" Columns="20,21,22,23,24,25,26,27,28">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.001070" Rows="111.000000" Width="8"/>
+ </dxl:Properties>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="20" Alias="a">
+ <dxl:Ident ColId="20" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="21" Alias="b">
+ <dxl:Ident ColId="21" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="22" Alias="ctid">
+ <dxl:Ident ColId="22" ColName="ctid" TypeMdid="0.27.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="23" Alias="xmin">
+ <dxl:Ident ColId="23" ColName="xmin" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="24" Alias="cmin">
+ <dxl:Ident ColId="24" ColName="cmin" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="25" Alias="xmax">
+ <dxl:Ident ColId="25" ColName="xmax" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="26" Alias="cmax">
+ <dxl:Ident ColId="26" ColName="cmax" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="27" Alias="tableoid">
+ <dxl:Ident ColId="27" ColName="tableoid" TypeMdid="0.26.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="28" Alias="gp_segment_id">
+ <dxl:Ident ColId="28" ColName="gp_segment_id" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ </dxl:CTEConsumer>
+ </dxl:Sort>
+ </dxl:Aggregate>
+ </dxl:Aggregate>
+ </dxl:HashJoin>
+ </dxl:Sequence>
</dxl:GatherMotion>
</dxl:Plan>
</dxl:Thread>
diff --git a/src/backend/gporca/data/dxl/minidump/MDQAs-Union.mdp b/src/backend/gporca/data/dxl/minidump/MDQAs-Union.mdp
index 4e94cd1935..80a69572f7 100644
--- a/src/backend/gporca/data/dxl/minidump/MDQAs-Union.mdp
+++ b/src/backend/gporca/data/dxl/minidump/MDQAs-Union.mdp
@@ -507,10 +507,10 @@ select a,count(distinct a), count(distinct b) from t1 group by a
</dxl:LogicalGroupBy>
</dxl:Union>
</dxl:Query>
- <dxl:Plan Id="0" SpaceSize="100">
+ <dxl:Plan Id="0" SpaceSize="426240000">
<dxl:GatherMotion InputSegments="0,1" OutputSegments="-1">
<dxl:Properties>
- <dxl:Cost StartupCost="0" TotalCost="862.074871" Rows="134.000000" Width="20"/>
+ <dxl:Cost StartupCost="0" TotalCost="2586.144662" Rows="134.000000" Width="20"/>
</dxl:Properties>
<dxl:ProjList>
<dxl:ProjElem ColId="0" Alias="a">
@@ -527,7 +527,7 @@ select a,count(distinct a), count(distinct b) from t1 group by a
<dxl:SortingColumnList/>
<dxl:Aggregate AggregationStrategy="Hashed" StreamSafe="false">
<dxl:Properties>
- <dxl:Cost StartupCost="0" TotalCost="862.062838" Rows="134.000000" Width="20"/>
+ <dxl:Cost StartupCost="0" TotalCost="2586.132629" Rows="134.000000" Width="20"/>
</dxl:Properties>
<dxl:GroupingColumns>
<dxl:GroupingColumn ColId="0"/>
@@ -548,7 +548,7 @@ select a,count(distinct a), count(distinct b) from t1 group by a
<dxl:Filter/>
<dxl:Append IsTarget="false" IsZapped="false">
<dxl:Properties>
- <dxl:Cost StartupCost="0" TotalCost="862.037516" Rows="134.000000" Width="20"/>
+ <dxl:Cost StartupCost="0" TotalCost="2586.107307" Rows="134.000000" Width="20"/>
</dxl:Properties>
<dxl:ProjList>
<dxl:ProjElem ColId="0" Alias="a">
@@ -562,186 +562,760 @@ select a,count(distinct a), count(distinct b) from t1 group by a
</dxl:ProjElem>
</dxl:ProjList>
<dxl:Filter/>
- <dxl:Aggregate AggregationStrategy="Sorted" StreamSafe="false">
+ <dxl:Sequence>
<dxl:Properties>
- <dxl:Cost StartupCost="0" TotalCost="431.017640" Rows="111.000000" Width="20"/>
+ <dxl:Cost StartupCost="0" TotalCost="1293.066792" Rows="111.000000" Width="20"/>
</dxl:Properties>
- <dxl:GroupingColumns>
- <dxl:GroupingColumn ColId="0"/>
- </dxl:GroupingColumns>
<dxl:ProjList>
<dxl:ProjElem ColId="0" Alias="a">
<dxl:Ident ColId="0" ColName="a" TypeMdid="0.23.1.0"/>
</dxl:ProjElem>
<dxl:ProjElem ColId="9" Alias="count">
- <dxl:AggFunc AggMdid="0.2147.1.0" AggDistinct="true" AggStage="Normal" AggKind="n" AggArgTypes="">
- <dxl:ValuesList ParamType="aggargs">
- <dxl:Ident ColId="0" ColName="a" TypeMdid="0.23.1.0"/>
- </dxl:ValuesList>
- <dxl:ValuesList ParamType="aggdirectargs"/>
- <dxl:ValuesList ParamType="aggorder"/>
- <dxl:ValuesList ParamType="aggdistinct"/>
- </dxl:AggFunc>
+ <dxl:Ident ColId="9" ColName="count" TypeMdid="0.20.1.0"/>
</dxl:ProjElem>
<dxl:ProjElem ColId="10" Alias="count">
- <dxl:AggFunc AggMdid="0.2147.1.0" AggDistinct="true" AggStage="Normal" AggKind="n" AggArgTypes="">
- <dxl:ValuesList ParamType="aggargs">
- <dxl:Ident ColId="1" ColName="b" TypeMdid="0.23.1.0"/>
- </dxl:ValuesList>
- <dxl:ValuesList ParamType="aggdirectargs"/>
- <dxl:ValuesList ParamType="aggorder"/>
- <dxl:ValuesList ParamType="aggdistinct"/>
- </dxl:AggFunc>
+ <dxl:Ident ColId="10" ColName="count" TypeMdid="0.20.1.0"/>
</dxl:ProjElem>
</dxl:ProjList>
- <dxl:Filter/>
- <dxl:Sort SortDiscardDuplicates="false">
+ <dxl:CTEProducer CTEId="1" Columns="42,43,44,45,46,47,48,49,50">
<dxl:Properties>
- <dxl:Cost StartupCost="0" TotalCost="431.016573" Rows="111.000000" Width="8"/>
+ <dxl:Cost StartupCost="0" TotalCost="431.005138" Rows="111.000000" Width="1"/>
</dxl:Properties>
<dxl:ProjList>
- <dxl:ProjElem ColId="0" Alias="a">
- <dxl:Ident ColId="0" ColName="a" TypeMdid="0.23.1.0"/>
+ <dxl:ProjElem ColId="42" Alias="a">
+ <dxl:Ident ColId="42" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="43" Alias="b">
+ <dxl:Ident ColId="43" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="44" Alias="ctid">
+ <dxl:Ident ColId="44" ColName="ctid" TypeMdid="0.27.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="45" Alias="xmin">
+ <dxl:Ident ColId="45" ColName="xmin" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="46" Alias="cmin">
+ <dxl:Ident ColId="46" ColName="cmin" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="47" Alias="xmax">
+ <dxl:Ident ColId="47" ColName="xmax" TypeMdid="0.28.1.0"/>
</dxl:ProjElem>
- <dxl:ProjElem ColId="1" Alias="b">
- <dxl:Ident ColId="1" ColName="b" TypeMdid="0.23.1.0"/>
+ <dxl:ProjElem ColId="48" Alias="cmax">
+ <dxl:Ident ColId="48" ColName="cmax" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="49" Alias="tableoid">
+ <dxl:Ident ColId="49" ColName="tableoid" TypeMdid="0.26.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="50" Alias="gp_segment_id">
+ <dxl:Ident ColId="50" ColName="gp_segment_id" TypeMdid="0.23.1.0"/>
</dxl:ProjElem>
</dxl:ProjList>
- <dxl:Filter/>
- <dxl:SortingColumnList>
- <dxl:SortingColumn ColId="0" SortOperatorMdid="0.97.1.0" SortOperatorName="<" SortNullsFirst="false"/>
- </dxl:SortingColumnList>
- <dxl:LimitCount/>
- <dxl:LimitOffset/>
<dxl:TableScan>
<dxl:Properties>
- <dxl:Cost StartupCost="0" TotalCost="431.001160" Rows="111.000000" Width="8"/>
+ <dxl:Cost StartupCost="0" TotalCost="431.001160" Rows="111.000000" Width="38"/>
</dxl:Properties>
<dxl:ProjList>
- <dxl:ProjElem ColId="0" Alias="a">
- <dxl:Ident ColId="0" ColName="a" TypeMdid="0.23.1.0"/>
+ <dxl:ProjElem ColId="42" Alias="a">
+ <dxl:Ident ColId="42" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="43" Alias="b">
+ <dxl:Ident ColId="43" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="44" Alias="ctid">
+ <dxl:Ident ColId="44" ColName="ctid" TypeMdid="0.27.1.0"/>
</dxl:ProjElem>
- <dxl:ProjElem ColId="1" Alias="b">
- <dxl:Ident ColId="1" ColName="b" TypeMdid="0.23.1.0"/>
+ <dxl:ProjElem ColId="45" Alias="xmin">
+ <dxl:Ident ColId="45" ColName="xmin" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="46" Alias="cmin">
+ <dxl:Ident ColId="46" ColName="cmin" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="47" Alias="xmax">
+ <dxl:Ident ColId="47" ColName="xmax" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="48" Alias="cmax">
+ <dxl:Ident ColId="48" ColName="cmax" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="49" Alias="tableoid">
+ <dxl:Ident ColId="49" ColName="tableoid" TypeMdid="0.26.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="50" Alias="gp_segment_id">
+ <dxl:Ident ColId="50" ColName="gp_segment_id" TypeMdid="0.23.1.0"/>
</dxl:ProjElem>
</dxl:ProjList>
<dxl:Filter/>
<dxl:TableDescriptor Mdid="6.1639448.1.1" TableName="t1">
<dxl:Columns>
- <dxl:Column ColId="0" Attno="1" ColName="a" TypeMdid="0.23.1.0"/>
- <dxl:Column ColId="1" Attno="2" ColName="b" TypeMdid="0.23.1.0"/>
- <dxl:Column ColId="2" Attno="-1" ColName="ctid" TypeMdid="0.27.1.0"/>
- <dxl:Column ColId="3" Attno="-3" ColName="xmin" TypeMdid="0.28.1.0"/>
- <dxl:Column ColId="4" Attno="-4" ColName="cmin" TypeMdid="0.29.1.0"/>
- <dxl:Column ColId="5" Attno="-5" ColName="xmax" TypeMdid="0.28.1.0"/>
- <dxl:Column ColId="6" Attno="-6" ColName="cmax" TypeMdid="0.29.1.0"/>
- <dxl:Column ColId="7" Attno="-7" ColName="tableoid" TypeMdid="0.26.1.0"/>
- <dxl:Column ColId="8" Attno="-8" ColName="gp_segment_id" TypeMdid="0.23.1.0"/>
+ <dxl:Column ColId="42" Attno="1" ColName="a" TypeMdid="0.23.1.0"/>
+ <dxl:Column ColId="43" Attno="2" ColName="b" TypeMdid="0.23.1.0"/>
+ <dxl:Column ColId="44" Attno="-1" ColName="ctid" TypeMdid="0.27.1.0"/>
+ <dxl:Column ColId="45" Attno="-3" ColName="xmin" TypeMdid="0.28.1.0"/>
+ <dxl:Column ColId="46" Attno="-4" ColName="cmin" TypeMdid="0.29.1.0"/>
+ <dxl:Column ColId="47" Attno="-5" ColName="xmax" TypeMdid="0.28.1.0"/>
+ <dxl:Column ColId="48" Attno="-6" ColName="cmax" TypeMdid="0.29.1.0"/>
+ <dxl:Column ColId="49" Attno="-7" ColName="tableoid" TypeMdid="0.26.1.0"/>
+ <dxl:Column ColId="50" Attno="-8" ColName="gp_segment_id" TypeMdid="0.23.1.0"/>
</dxl:Columns>
</dxl:TableDescriptor>
</dxl:TableScan>
- </dxl:Sort>
- </dxl:Aggregate>
- <dxl:Aggregate AggregationStrategy="Sorted" StreamSafe="false">
+ </dxl:CTEProducer>
+ <dxl:HashJoin JoinType="Inner">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="862.060544" Rows="111.000000" Width="20"/>
+ </dxl:Properties>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="0" Alias="a">
+ <dxl:Ident ColId="0" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="9" Alias="count">
+ <dxl:Ident ColId="9" ColName="count" TypeMdid="0.20.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="10" Alias="count">
+ <dxl:Ident ColId="10" ColName="count" TypeMdid="0.20.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:JoinFilter/>
+ <dxl:HashCondList>
+ <dxl:Not>
+ <dxl:IsDistinctFrom OperatorMdid="0.96.1.0">
+ <dxl:Ident ColId="0" ColName="a" TypeMdid="0.23.1.0"/>
+ <dxl:Ident ColId="51" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:IsDistinctFrom>
+ </dxl:Not>
+ </dxl:HashCondList>
+ <dxl:Aggregate AggregationStrategy="Sorted" StreamSafe="false">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.008771" Rows="111.000000" Width="12"/>
+ </dxl:Properties>
+ <dxl:GroupingColumns>
+ <dxl:GroupingColumn ColId="0"/>
+ </dxl:GroupingColumns>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="9" Alias="count">
+ <dxl:AggFunc AggMdid="0.2147.1.0" AggDistinct="false" AggStage="Normal" AggKind="n" AggArgTypes="">
+ <dxl:ValuesList ParamType="aggargs">
+ <dxl:Ident ColId="0" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ValuesList>
+ <dxl:ValuesList ParamType="aggdirectargs"/>
+ <dxl:ValuesList ParamType="aggorder"/>
+ <dxl:ValuesList ParamType="aggdistinct"/>
+ </dxl:AggFunc>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="0" Alias="a">
+ <dxl:Ident ColId="0" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:Aggregate AggregationStrategy="Sorted" StreamSafe="false">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.008175" Rows="111.000000" Width="4"/>
+ </dxl:Properties>
+ <dxl:GroupingColumns>
+ <dxl:GroupingColumn ColId="0"/>
+ </dxl:GroupingColumns>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="0" Alias="a">
+ <dxl:Ident ColId="0" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:Sort SortDiscardDuplicates="false">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.007829" Rows="111.000000" Width="4"/>
+ </dxl:Properties>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="0" Alias="a">
+ <dxl:Ident ColId="0" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="1" Alias="b">
+ <dxl:Ident ColId="1" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="2" Alias="ctid">
+ <dxl:Ident ColId="2" ColName="ctid" TypeMdid="0.27.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="3" Alias="xmin">
+ <dxl:Ident ColId="3" ColName="xmin" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="4" Alias="cmin">
+ <dxl:Ident ColId="4" ColName="cmin" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="5" Alias="xmax">
+ <dxl:Ident ColId="5" ColName="xmax" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="6" Alias="cmax">
+ <dxl:Ident ColId="6" ColName="cmax" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="7" Alias="tableoid">
+ <dxl:Ident ColId="7" ColName="tableoid" TypeMdid="0.26.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="8" Alias="gp_segment_id">
+ <dxl:Ident ColId="8" ColName="gp_segment_id" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:SortingColumnList>
+ <dxl:SortingColumn ColId="0" SortOperatorMdid="0.97.1.0" SortOperatorName="<" SortNullsFirst="false"/>
+ </dxl:SortingColumnList>
+ <dxl:LimitCount/>
+ <dxl:LimitOffset/>
+ <dxl:CTEConsumer CTEId="1" Columns="0,1,2,3,4,5,6,7,8">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.000535" Rows="111.000000" Width="4"/>
+ </dxl:Properties>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="0" Alias="a">
+ <dxl:Ident ColId="0" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="1" Alias="b">
+ <dxl:Ident ColId="1" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="2" Alias="ctid">
+ <dxl:Ident ColId="2" ColName="ctid" TypeMdid="0.27.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="3" Alias="xmin">
+ <dxl:Ident ColId="3" ColName="xmin" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="4" Alias="cmin">
+ <dxl:Ident ColId="4" ColName="cmin" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="5" Alias="xmax">
+ <dxl:Ident ColId="5" ColName="xmax" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="6" Alias="cmax">
+ <dxl:Ident ColId="6" ColName="cmax" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="7" Alias="tableoid">
+ <dxl:Ident ColId="7" ColName="tableoid" TypeMdid="0.26.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="8" Alias="gp_segment_id">
+ <dxl:Ident ColId="8" ColName="gp_segment_id" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ </dxl:CTEConsumer>
+ </dxl:Sort>
+ </dxl:Aggregate>
+ </dxl:Aggregate>
+ <dxl:Aggregate AggregationStrategy="Sorted" StreamSafe="false">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.017168" Rows="111.000000" Width="12"/>
+ </dxl:Properties>
+ <dxl:GroupingColumns>
+ <dxl:GroupingColumn ColId="51"/>
+ </dxl:GroupingColumns>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="10" Alias="count">
+ <dxl:AggFunc AggMdid="0.2147.1.0" AggDistinct="false" AggStage="Normal" AggKind="n" AggArgTypes="">
+ <dxl:ValuesList ParamType="aggargs">
+ <dxl:Ident ColId="52" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ValuesList>
+ <dxl:ValuesList ParamType="aggdirectargs"/>
+ <dxl:ValuesList ParamType="aggorder"/>
+ <dxl:ValuesList ParamType="aggdistinct"/>
+ </dxl:AggFunc>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="51" Alias="a">
+ <dxl:Ident ColId="51" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:Aggregate AggregationStrategy="Sorted" StreamSafe="false">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.016350" Rows="111.000000" Width="8"/>
+ </dxl:Properties>
+ <dxl:GroupingColumns>
+ <dxl:GroupingColumn ColId="51"/>
+ <dxl:GroupingColumn ColId="52"/>
+ </dxl:GroupingColumns>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="51" Alias="a">
+ <dxl:Ident ColId="51" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="52" Alias="b">
+ <dxl:Ident ColId="52" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:Sort SortDiscardDuplicates="false">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.015657" Rows="111.000000" Width="8"/>
+ </dxl:Properties>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="51" Alias="a">
+ <dxl:Ident ColId="51" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="52" Alias="b">
+ <dxl:Ident ColId="52" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="53" Alias="ctid">
+ <dxl:Ident ColId="53" ColName="ctid" TypeMdid="0.27.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="54" Alias="xmin">
+ <dxl:Ident ColId="54" ColName="xmin" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="55" Alias="cmin">
+ <dxl:Ident ColId="55" ColName="cmin" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="56" Alias="xmax">
+ <dxl:Ident ColId="56" ColName="xmax" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="57" Alias="cmax">
+ <dxl:Ident ColId="57" ColName="cmax" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="58" Alias="tableoid">
+ <dxl:Ident ColId="58" ColName="tableoid" TypeMdid="0.26.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="59" Alias="gp_segment_id">
+ <dxl:Ident ColId="59" ColName="gp_segment_id" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:SortingColumnList>
+ <dxl:SortingColumn ColId="51" SortOperatorMdid="0.97.1.0" SortOperatorName="<" SortNullsFirst="false"/>
+ <dxl:SortingColumn ColId="52" SortOperatorMdid="0.97.1.0" SortOperatorName="<" SortNullsFirst="false"/>
+ </dxl:SortingColumnList>
+ <dxl:LimitCount/>
+ <dxl:LimitOffset/>
+ <dxl:CTEConsumer CTEId="1" Columns="51,52,53,54,55,56,57,58,59">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.001070" Rows="111.000000" Width="8"/>
+ </dxl:Properties>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="51" Alias="a">
+ <dxl:Ident ColId="51" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="52" Alias="b">
+ <dxl:Ident ColId="52" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="53" Alias="ctid">
+ <dxl:Ident ColId="53" ColName="ctid" TypeMdid="0.27.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="54" Alias="xmin">
+ <dxl:Ident ColId="54" ColName="xmin" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="55" Alias="cmin">
+ <dxl:Ident ColId="55" ColName="cmin" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="56" Alias="xmax">
+ <dxl:Ident ColId="56" ColName="xmax" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="57" Alias="cmax">
+ <dxl:Ident ColId="57" ColName="cmax" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="58" Alias="tableoid">
+ <dxl:Ident ColId="58" ColName="tableoid" TypeMdid="0.26.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="59" Alias="gp_segment_id">
+ <dxl:Ident ColId="59" ColName="gp_segment_id" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ </dxl:CTEConsumer>
+ </dxl:Sort>
+ </dxl:Aggregate>
+ </dxl:Aggregate>
+ </dxl:HashJoin>
+ </dxl:Sequence>
+ <dxl:Sequence>
<dxl:Properties>
- <dxl:Cost StartupCost="0" TotalCost="431.018536" Rows="23.000000" Width="20"/>
+ <dxl:Cost StartupCost="0" TotalCost="1293.039175" Rows="23.000000" Width="20"/>
</dxl:Properties>
- <dxl:GroupingColumns>
- <dxl:GroupingColumn ColId="12"/>
- </dxl:GroupingColumns>
<dxl:ProjList>
<dxl:ProjElem ColId="12" Alias="b">
<dxl:Ident ColId="12" ColName="b" TypeMdid="0.23.1.0"/>
</dxl:ProjElem>
<dxl:ProjElem ColId="20" Alias="count">
- <dxl:AggFunc AggMdid="0.2147.1.0" AggDistinct="true" AggStage="Normal" AggKind="n" AggArgTypes="">
- <dxl:ValuesList ParamType="aggargs">
- <dxl:Ident ColId="11" ColName="a" TypeMdid="0.23.1.0"/>
- </dxl:ValuesList>
- <dxl:ValuesList ParamType="aggdirectargs"/>
- <dxl:ValuesList ParamType="aggorder"/>
- <dxl:ValuesList ParamType="aggdistinct"/>
- </dxl:AggFunc>
+ <dxl:Ident ColId="20" ColName="count" TypeMdid="0.20.1.0"/>
</dxl:ProjElem>
<dxl:ProjElem ColId="21" Alias="count">
- <dxl:AggFunc AggMdid="0.2147.1.0" AggDistinct="true" AggStage="Normal" AggKind="n" AggArgTypes="">
- <dxl:ValuesList ParamType="aggargs">
- <dxl:Ident ColId="12" ColName="b" TypeMdid="0.23.1.0"/>
- </dxl:ValuesList>
- <dxl:ValuesList ParamType="aggdirectargs"/>
- <dxl:ValuesList ParamType="aggorder"/>
- <dxl:ValuesList ParamType="aggdistinct"/>
- </dxl:AggFunc>
+ <dxl:Ident ColId="21" ColName="count" TypeMdid="0.20.1.0"/>
</dxl:ProjElem>
</dxl:ProjList>
- <dxl:Filter/>
- <dxl:Sort SortDiscardDuplicates="false">
+ <dxl:CTEProducer CTEId="0" Columns="22,23,24,25,26,27,28,29,30">
<dxl:Properties>
- <dxl:Cost StartupCost="0" TotalCost="431.017963" Rows="111.000000" Width="8"/>
+ <dxl:Cost StartupCost="0" TotalCost="431.005138" Rows="111.000000" Width="1"/>
</dxl:Properties>
<dxl:ProjList>
- <dxl:ProjElem ColId="11" Alias="a">
- <dxl:Ident ColId="11" ColName="a" TypeMdid="0.23.1.0"/>
+ <dxl:ProjElem ColId="22" Alias="a">
+ <dxl:Ident ColId="22" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="23" Alias="b">
+ <dxl:Ident ColId="23" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="24" Alias="ctid">
+ <dxl:Ident ColId="24" ColName="ctid" TypeMdid="0.27.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="25" Alias="xmin">
+ <dxl:Ident ColId="25" ColName="xmin" TypeMdid="0.28.1.0"/>
</dxl:ProjElem>
+ <dxl:ProjElem ColId="26" Alias="cmin">
+ <dxl:Ident ColId="26" ColName="cmin" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="27" Alias="xmax">
+ <dxl:Ident ColId="27" ColName="xmax" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="28" Alias="cmax">
+ <dxl:Ident ColId="28" ColName="cmax" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="29" Alias="tableoid">
+ <dxl:Ident ColId="29" ColName="tableoid" TypeMdid="0.26.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="30" Alias="gp_segment_id">
+ <dxl:Ident ColId="30" ColName="gp_segment_id" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:TableScan>
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.001160" Rows="111.000000" Width="38"/>
+ </dxl:Properties>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="22" Alias="a">
+ <dxl:Ident ColId="22" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="23" Alias="b">
+ <dxl:Ident ColId="23" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="24" Alias="ctid">
+ <dxl:Ident ColId="24" ColName="ctid" TypeMdid="0.27.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="25" Alias="xmin">
+ <dxl:Ident ColId="25" ColName="xmin" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="26" Alias="cmin">
+ <dxl:Ident ColId="26" ColName="cmin" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="27" Alias="xmax">
+ <dxl:Ident ColId="27" ColName="xmax" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="28" Alias="cmax">
+ <dxl:Ident ColId="28" ColName="cmax" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="29" Alias="tableoid">
+ <dxl:Ident ColId="29" ColName="tableoid" TypeMdid="0.26.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="30" Alias="gp_segment_id">
+ <dxl:Ident ColId="30" ColName="gp_segment_id" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:TableDescriptor Mdid="6.1639448.1.1" TableName="t1">
+ <dxl:Columns>
+ <dxl:Column ColId="22" Attno="1" ColName="a" TypeMdid="0.23.1.0"/>
+ <dxl:Column ColId="23" Attno="2" ColName="b" TypeMdid="0.23.1.0"/>
+ <dxl:Column ColId="24" Attno="-1" ColName="ctid" TypeMdid="0.27.1.0"/>
+ <dxl:Column ColId="25" Attno="-3" ColName="xmin" TypeMdid="0.28.1.0"/>
+ <dxl:Column ColId="26" Attno="-4" ColName="cmin" TypeMdid="0.29.1.0"/>
+ <dxl:Column ColId="27" Attno="-5" ColName="xmax" TypeMdid="0.28.1.0"/>
+ <dxl:Column ColId="28" Attno="-6" ColName="cmax" TypeMdid="0.29.1.0"/>
+ <dxl:Column ColId="29" Attno="-7" ColName="tableoid" TypeMdid="0.26.1.0"/>
+ <dxl:Column ColId="30" Attno="-8" ColName="gp_segment_id" TypeMdid="0.23.1.0"/>
+ </dxl:Columns>
+ </dxl:TableDescriptor>
+ </dxl:TableScan>
+ </dxl:CTEProducer>
+ <dxl:HashJoin JoinType="Inner">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="862.033807" Rows="23.000000" Width="20"/>
+ </dxl:Properties>
+ <dxl:ProjList>
<dxl:ProjElem ColId="12" Alias="b">
<dxl:Ident ColId="12" ColName="b" TypeMdid="0.23.1.0"/>
</dxl:ProjElem>
+ <dxl:ProjElem ColId="20" Alias="count">
+ <dxl:Ident ColId="20" ColName="count" TypeMdid="0.20.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="21" Alias="count">
+ <dxl:Ident ColId="21" ColName="count" TypeMdid="0.20.1.0"/>
+ </dxl:ProjElem>
</dxl:ProjList>
<dxl:Filter/>
- <dxl:SortingColumnList>
- <dxl:SortingColumn ColId="12" SortOperatorMdid="0.97.1.0" SortOperatorName="<" SortNullsFirst="false"/>
- </dxl:SortingColumnList>
- <dxl:LimitCount/>
- <dxl:LimitOffset/>
- <dxl:RedistributeMotion InputSegments="0,1" OutputSegments="0,1">
+ <dxl:JoinFilter/>
+ <dxl:HashCondList>
+ <dxl:Not>
+ <dxl:IsDistinctFrom OperatorMdid="0.96.1.0">
+ <dxl:Ident ColId="12" ColName="b" TypeMdid="0.23.1.0"/>
+ <dxl:Ident ColId="32" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:IsDistinctFrom>
+ </dxl:Not>
+ </dxl:HashCondList>
+ <dxl:Aggregate AggregationStrategy="Hashed" StreamSafe="false">
<dxl:Properties>
- <dxl:Cost StartupCost="0" TotalCost="431.003376" Rows="111.000000" Width="8"/>
+ <dxl:Cost StartupCost="0" TotalCost="431.018084" Rows="23.000000" Width="12"/>
</dxl:Properties>
+ <dxl:GroupingColumns>
+ <dxl:GroupingColumn ColId="12"/>
+ </dxl:GroupingColumns>
<dxl:ProjList>
- <dxl:ProjElem ColId="11" Alias="a">
- <dxl:Ident ColId="11" ColName="a" TypeMdid="0.23.1.0"/>
+ <dxl:ProjElem ColId="20" Alias="count">
+ <dxl:AggFunc AggMdid="0.2147.1.0" AggDistinct="false" AggStage="Final" AggKind="n" AggArgTypes="">
+ <dxl:ValuesList ParamType="aggargs">
+ <dxl:Ident ColId="41" ColName="ColRef_0041" TypeMdid="0.20.1.0"/>
+ </dxl:ValuesList>
+ <dxl:ValuesList ParamType="aggdirectargs"/>
+ <dxl:ValuesList ParamType="aggorder"/>
+ <dxl:ValuesList ParamType="aggdistinct"/>
+ </dxl:AggFunc>
</dxl:ProjElem>
<dxl:ProjElem ColId="12" Alias="b">
<dxl:Ident ColId="12" ColName="b" TypeMdid="0.23.1.0"/>
</dxl:ProjElem>
</dxl:ProjList>
<dxl:Filter/>
- <dxl:SortingColumnList/>
- <dxl:HashExprList>
- <dxl:HashExpr>
- <dxl:Ident ColId="12" ColName="b" TypeMdid="0.23.1.0"/>
- </dxl:HashExpr>
- </dxl:HashExprList>
- <dxl:TableScan>
+ <dxl:RedistributeMotion InputSegments="0,1" OutputSegments="0,1">
<dxl:Properties>
- <dxl:Cost StartupCost="0" TotalCost="431.001160" Rows="111.000000" Width="8"/>
+ <dxl:Cost StartupCost="0" TotalCost="431.016611" Rows="23.000000" Width="12"/>
</dxl:Properties>
<dxl:ProjList>
- <dxl:ProjElem ColId="11" Alias="a">
- <dxl:Ident ColId="11" ColName="a" TypeMdid="0.23.1.0"/>
- </dxl:ProjElem>
<dxl:ProjElem ColId="12" Alias="b">
<dxl:Ident ColId="12" ColName="b" TypeMdid="0.23.1.0"/>
</dxl:ProjElem>
+ <dxl:ProjElem ColId="41" Alias="ColRef_0041">
+ <dxl:Ident ColId="41" ColName="ColRef_0041" TypeMdid="0.20.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:SortingColumnList/>
+ <dxl:HashExprList>
+ <dxl:HashExpr>
+ <dxl:Ident ColId="12" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:HashExpr>
+ </dxl:HashExprList>
+ <dxl:Result>
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.016179" Rows="23.000000" Width="12"/>
+ </dxl:Properties>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="12" Alias="b">
+ <dxl:Ident ColId="12" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="41" Alias="ColRef_0041">
+ <dxl:Ident ColId="41" ColName="ColRef_0041" TypeMdid="0.20.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:OneTimeFilter/>
+ <dxl:Aggregate AggregationStrategy="Sorted" StreamSafe="false">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.016179" Rows="23.000000" Width="12"/>
+ </dxl:Properties>
+ <dxl:GroupingColumns>
+ <dxl:GroupingColumn ColId="12"/>
+ </dxl:GroupingColumns>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="41" Alias="ColRef_0041">
+ <dxl:AggFunc AggMdid="0.2147.1.0" AggDistinct="true" AggStage="Partial" AggKind="n" AggArgTypes="">
+ <dxl:ValuesList ParamType="aggargs">
+ <dxl:Ident ColId="11" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ValuesList>
+ <dxl:ValuesList ParamType="aggdirectargs"/>
+ <dxl:ValuesList ParamType="aggorder"/>
+ <dxl:ValuesList ParamType="aggdistinct"/>
+ </dxl:AggFunc>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="12" Alias="b">
+ <dxl:Ident ColId="12" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:Sort SortDiscardDuplicates="false">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.015657" Rows="111.000000" Width="8"/>
+ </dxl:Properties>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="11" Alias="a">
+ <dxl:Ident ColId="11" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="12" Alias="b">
+ <dxl:Ident ColId="12" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="13" Alias="ctid">
+ <dxl:Ident ColId="13" ColName="ctid" TypeMdid="0.27.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="14" Alias="xmin">
+ <dxl:Ident ColId="14" ColName="xmin" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="15" Alias="cmin">
+ <dxl:Ident ColId="15" ColName="cmin" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="16" Alias="xmax">
+ <dxl:Ident ColId="16" ColName="xmax" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="17" Alias="cmax">
+ <dxl:Ident ColId="17" ColName="cmax" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="18" Alias="tableoid">
+ <dxl:Ident ColId="18" ColName="tableoid" TypeMdid="0.26.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="19" Alias="gp_segment_id">
+ <dxl:Ident ColId="19" ColName="gp_segment_id" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:SortingColumnList>
+ <dxl:SortingColumn ColId="12" SortOperatorMdid="0.97.1.0" SortOperatorName="<" SortNullsFirst="false"/>
+ <dxl:SortingColumn ColId="11" SortOperatorMdid="0.97.1.0" SortOperatorName="<" SortNullsFirst="false"/>
+ </dxl:SortingColumnList>
+ <dxl:LimitCount/>
+ <dxl:LimitOffset/>
+ <dxl:CTEConsumer CTEId="0" Columns="11,12,13,14,15,16,17,18,19">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.001070" Rows="111.000000" Width="8"/>
+ </dxl:Properties>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="11" Alias="a">
+ <dxl:Ident ColId="11" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="12" Alias="b">
+ <dxl:Ident ColId="12" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="13" Alias="ctid">
+ <dxl:Ident ColId="13" ColName="ctid" TypeMdid="0.27.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="14" Alias="xmin">
+ <dxl:Ident ColId="14" ColName="xmin" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="15" Alias="cmin">
+ <dxl:Ident ColId="15" ColName="cmin" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="16" Alias="xmax">
+ <dxl:Ident ColId="16" ColName="xmax" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="17" Alias="cmax">
+ <dxl:Ident ColId="17" ColName="cmax" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="18" Alias="tableoid">
+ <dxl:Ident ColId="18" ColName="tableoid" TypeMdid="0.26.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="19" Alias="gp_segment_id">
+ <dxl:Ident ColId="19" ColName="gp_segment_id" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ </dxl:CTEConsumer>
+ </dxl:Sort>
+ </dxl:Aggregate>
+ </dxl:Result>
+ </dxl:RedistributeMotion>
+ </dxl:Aggregate>
+ <dxl:Aggregate AggregationStrategy="Sorted" StreamSafe="false">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.008553" Rows="23.000000" Width="12"/>
+ </dxl:Properties>
+ <dxl:GroupingColumns>
+ <dxl:GroupingColumn ColId="32"/>
+ </dxl:GroupingColumns>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="21" Alias="count">
+ <dxl:AggFunc AggMdid="0.2147.1.0" AggDistinct="false" AggStage="Normal" AggKind="n" AggArgTypes="">
+ <dxl:ValuesList ParamType="aggargs">
+ <dxl:Ident ColId="32" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ValuesList>
+ <dxl:ValuesList ParamType="aggdirectargs"/>
+ <dxl:ValuesList ParamType="aggorder"/>
+ <dxl:ValuesList ParamType="aggdistinct"/>
+ </dxl:AggFunc>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="32" Alias="b">
+ <dxl:Ident ColId="32" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:Aggregate AggregationStrategy="Sorted" StreamSafe="false">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.008430" Rows="23.000000" Width="4"/>
+ </dxl:Properties>
+ <dxl:GroupingColumns>
+ <dxl:GroupingColumn ColId="32"/>
+ </dxl:GroupingColumns>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="32" Alias="b">
+ <dxl:Ident ColId="32" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
</dxl:ProjList>
<dxl:Filter/>
- <dxl:TableDescriptor Mdid="6.1639448.1.1" TableName="t1">
- <dxl:Columns>
- <dxl:Column ColId="11" Attno="1" ColName="a" TypeMdid="0.23.1.0"/>
- <dxl:Column ColId="12" Attno="2" ColName="b" TypeMdid="0.23.1.0"/>
- <dxl:Column ColId="13" Attno="-1" ColName="ctid" TypeMdid="0.27.1.0"/>
- <dxl:Column ColId="14" Attno="-3" ColName="xmin" TypeMdid="0.28.1.0"/>
- <dxl:Column ColId="15" Attno="-4" ColName="cmin" TypeMdid="0.29.1.0"/>
- <dxl:Column ColId="16" Attno="-5" ColName="xmax" TypeMdid="0.28.1.0"/>
- <dxl:Column ColId="17" Attno="-6" ColName="cmax" TypeMdid="0.29.1.0"/>
- <dxl:Column ColId="18" Attno="-7" ColName="tableoid" TypeMdid="0.26.1.0"/>
- <dxl:Column ColId="19" Attno="-8" ColName="gp_segment_id" TypeMdid="0.23.1.0"/>
- </dxl:Columns>
- </dxl:TableDescriptor>
- </dxl:TableScan>
- </dxl:RedistributeMotion>
- </dxl:Sort>
- </dxl:Aggregate>
+ <dxl:Sort SortDiscardDuplicates="false">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.008358" Rows="23.000000" Width="4"/>
+ </dxl:Properties>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="32" Alias="b">
+ <dxl:Ident ColId="32" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:SortingColumnList>
+ <dxl:SortingColumn ColId="32" SortOperatorMdid="0.97.1.0" SortOperatorName="<" SortNullsFirst="false"/>
+ </dxl:SortingColumnList>
+ <dxl:LimitCount/>
+ <dxl:LimitOffset/>
+ <dxl:RedistributeMotion InputSegments="0,1" OutputSegments="0,1">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.007439" Rows="23.000000" Width="4"/>
+ </dxl:Properties>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="32" Alias="b">
+ <dxl:Ident ColId="32" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:SortingColumnList/>
+ <dxl:HashExprList>
+ <dxl:HashExpr>
+ <dxl:Ident ColId="32" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:HashExpr>
+ </dxl:HashExprList>
+ <dxl:Aggregate AggregationStrategy="Hashed" StreamSafe="true">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.007295" Rows="23.000000" Width="4"/>
+ </dxl:Properties>
+ <dxl:GroupingColumns>
+ <dxl:GroupingColumn ColId="32"/>
+ </dxl:GroupingColumns>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="32" Alias="b">
+ <dxl:Ident ColId="32" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:CTEConsumer CTEId="0" Columns="31,32,33,34,35,36,37,38,39">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.000535" Rows="111.000000" Width="4"/>
+ </dxl:Properties>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="31" Alias="a">
+ <dxl:Ident ColId="31" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="32" Alias="b">
+ <dxl:Ident ColId="32" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="33" Alias="ctid">
+ <dxl:Ident ColId="33" ColName="ctid" TypeMdid="0.27.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="34" Alias="xmin">
+ <dxl:Ident ColId="34" ColName="xmin" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="35" Alias="cmin">
+ <dxl:Ident ColId="35" ColName="cmin" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="36" Alias="xmax">
+ <dxl:Ident ColId="36" ColName="xmax" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="37" Alias="cmax">
+ <dxl:Ident ColId="37" ColName="cmax" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="38" Alias="tableoid">
+ <dxl:Ident ColId="38" ColName="tableoid" TypeMdid="0.26.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="39" Alias="gp_segment_id">
+ <dxl:Ident ColId="39" ColName="gp_segment_id" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ </dxl:CTEConsumer>
+ </dxl:Aggregate>
+ </dxl:RedistributeMotion>
+ </dxl:Sort>
+ </dxl:Aggregate>
+ </dxl:Aggregate>
+ </dxl:HashJoin>
+ </dxl:Sequence>
</dxl:Append>
</dxl:Aggregate>
</dxl:GatherMotion>
diff --git a/src/backend/gporca/data/dxl/minidump/MDQAs1.mdp b/src/backend/gporca/data/dxl/minidump/MDQAs1.mdp
index cc102f3215..e0fed1698b 100644
--- a/src/backend/gporca/data/dxl/minidump/MDQAs1.mdp
+++ b/src/backend/gporca/data/dxl/minidump/MDQAs1.mdp
@@ -460,78 +460,357 @@ SQL:
</dxl:LogicalGet>
</dxl:LogicalGroupBy>
</dxl:Query>
- <dxl:Plan Id="0" SpaceSize="1">
- <dxl:Aggregate AggregationStrategy="Plain" StreamSafe="false">
+ <dxl:Plan Id="0" SpaceSize="1488">
+ <dxl:GatherMotion InputSegments="0,1" OutputSegments="-1">
<dxl:Properties>
- <dxl:Cost StartupCost="0" TotalCost="431.006371" Rows="1.000000" Width="16"/>
+ <dxl:Cost StartupCost="0" TotalCost="1724.007238" Rows="1.000000" Width="16"/>
</dxl:Properties>
- <dxl:GroupingColumns/>
<dxl:ProjList>
<dxl:ProjElem ColId="9" Alias="sum">
- <dxl:AggFunc AggMdid="0.2108.1.0" AggDistinct="true" AggStage="Normal" AggKind="n" AggArgTypes="">
- <dxl:ValuesList ParamType="aggargs">
- <dxl:Ident ColId="0" ColName="a" TypeMdid="0.23.1.0"/>
- </dxl:ValuesList>
- <dxl:ValuesList ParamType="aggdirectargs"/>
- <dxl:ValuesList ParamType="aggorder"/>
- <dxl:ValuesList ParamType="aggdistinct"/>
- </dxl:AggFunc>
+ <dxl:Ident ColId="9" ColName="sum" TypeMdid="0.20.1.0"/>
</dxl:ProjElem>
<dxl:ProjElem ColId="10" Alias="avg">
- <dxl:AggFunc AggMdid="0.2101.1.0" AggDistinct="true" AggStage="Normal" AggKind="n" AggArgTypes="">
- <dxl:ValuesList ParamType="aggargs">
- <dxl:Ident ColId="1" ColName="b" TypeMdid="0.23.1.0"/>
- </dxl:ValuesList>
- <dxl:ValuesList ParamType="aggdirectargs"/>
- <dxl:ValuesList ParamType="aggorder"/>
- <dxl:ValuesList ParamType="aggdistinct"/>
- </dxl:AggFunc>
+ <dxl:Ident ColId="10" ColName="avg" TypeMdid="0.1700.1.0"/>
</dxl:ProjElem>
</dxl:ProjList>
<dxl:Filter/>
- <dxl:GatherMotion InputSegments="0,1" OutputSegments="-1">
+ <dxl:SortingColumnList/>
+ <dxl:Sequence>
<dxl:Properties>
- <dxl:Cost StartupCost="0" TotalCost="431.005973" Rows="111.000000" Width="8"/>
+ <dxl:Cost StartupCost="0" TotalCost="1724.007166" Rows="1.000000" Width="16"/>
</dxl:Properties>
<dxl:ProjList>
- <dxl:ProjElem ColId="0" Alias="a">
- <dxl:Ident ColId="0" ColName="a" TypeMdid="0.23.1.0"/>
+ <dxl:ProjElem ColId="9" Alias="sum">
+ <dxl:Ident ColId="9" ColName="sum" TypeMdid="0.20.1.0"/>
</dxl:ProjElem>
- <dxl:ProjElem ColId="1" Alias="b">
- <dxl:Ident ColId="1" ColName="b" TypeMdid="0.23.1.0"/>
+ <dxl:ProjElem ColId="10" Alias="avg">
+ <dxl:Ident ColId="10" ColName="avg" TypeMdid="0.1700.1.0"/>
</dxl:ProjElem>
</dxl:ProjList>
- <dxl:Filter/>
- <dxl:SortingColumnList/>
- <dxl:TableScan>
+ <dxl:CTEProducer CTEId="0" Columns="11,12,13,14,15,16,17,18,19">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.005138" Rows="111.000000" Width="1"/>
+ </dxl:Properties>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="11" Alias="a">
+ <dxl:Ident ColId="11" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="12" Alias="b">
+ <dxl:Ident ColId="12" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="13" Alias="ctid">
+ <dxl:Ident ColId="13" ColName="ctid" TypeMdid="0.27.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="14" Alias="xmin">
+ <dxl:Ident ColId="14" ColName="xmin" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="15" Alias="cmin">
+ <dxl:Ident ColId="15" ColName="cmin" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="16" Alias="xmax">
+ <dxl:Ident ColId="16" ColName="xmax" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="17" Alias="cmax">
+ <dxl:Ident ColId="17" ColName="cmax" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="18" Alias="tableoid">
+ <dxl:Ident ColId="18" ColName="tableoid" TypeMdid="0.26.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="19" Alias="gp_segment_id">
+ <dxl:Ident ColId="19" ColName="gp_segment_id" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:TableScan>
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.001160" Rows="111.000000" Width="38"/>
+ </dxl:Properties>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="11" Alias="a">
+ <dxl:Ident ColId="11" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="12" Alias="b">
+ <dxl:Ident ColId="12" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="13" Alias="ctid">
+ <dxl:Ident ColId="13" ColName="ctid" TypeMdid="0.27.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="14" Alias="xmin">
+ <dxl:Ident ColId="14" ColName="xmin" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="15" Alias="cmin">
+ <dxl:Ident ColId="15" ColName="cmin" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="16" Alias="xmax">
+ <dxl:Ident ColId="16" ColName="xmax" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="17" Alias="cmax">
+ <dxl:Ident ColId="17" ColName="cmax" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="18" Alias="tableoid">
+ <dxl:Ident ColId="18" ColName="tableoid" TypeMdid="0.26.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="19" Alias="gp_segment_id">
+ <dxl:Ident ColId="19" ColName="gp_segment_id" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:TableDescriptor Mdid="6.1639448.1.1" TableName="t1">
+ <dxl:Columns>
+ <dxl:Column ColId="11" Attno="1" ColName="a" TypeMdid="0.23.1.0"/>
+ <dxl:Column ColId="12" Attno="2" ColName="b" TypeMdid="0.23.1.0"/>
+ <dxl:Column ColId="13" Attno="-1" ColName="ctid" TypeMdid="0.27.1.0"/>
+ <dxl:Column ColId="14" Attno="-3" ColName="xmin" TypeMdid="0.28.1.0"/>
+ <dxl:Column ColId="15" Attno="-4" ColName="cmin" TypeMdid="0.29.1.0"/>
+ <dxl:Column ColId="16" Attno="-5" ColName="xmax" TypeMdid="0.28.1.0"/>
+ <dxl:Column ColId="17" Attno="-6" ColName="cmax" TypeMdid="0.29.1.0"/>
+ <dxl:Column ColId="18" Attno="-7" ColName="tableoid" TypeMdid="0.26.1.0"/>
+ <dxl:Column ColId="19" Attno="-8" ColName="gp_segment_id" TypeMdid="0.23.1.0"/>
+ </dxl:Columns>
+ </dxl:TableDescriptor>
+ </dxl:TableScan>
+ </dxl:CTEProducer>
+ <dxl:RandomMotion InputSegments="-1" OutputSegments="0,1">
<dxl:Properties>
- <dxl:Cost StartupCost="0" TotalCost="431.001160" Rows="111.000000" Width="8"/>
+ <dxl:Cost StartupCost="0" TotalCost="1293.002020" Rows="1.000000" Width="16"/>
</dxl:Properties>
<dxl:ProjList>
- <dxl:ProjElem ColId="0" Alias="a">
- <dxl:Ident ColId="0" ColName="a" TypeMdid="0.23.1.0"/>
+ <dxl:ProjElem ColId="9" Alias="sum">
+ <dxl:Ident ColId="9" ColName="sum" TypeMdid="0.20.1.0"/>
</dxl:ProjElem>
- <dxl:ProjElem ColId="1" Alias="b">
- <dxl:Ident ColId="1" ColName="b" TypeMdid="0.23.1.0"/>
+ <dxl:ProjElem ColId="10" Alias="avg">
+ <dxl:Ident ColId="10" ColName="avg" TypeMdid="0.1700.1.0"/>
</dxl:ProjElem>
</dxl:ProjList>
<dxl:Filter/>
- <dxl:TableDescriptor Mdid="6.1639448.1.1" TableName="t1">
- <dxl:Columns>
- <dxl:Column ColId="0" Attno="1" ColName="a" TypeMdid="0.23.1.0"/>
- <dxl:Column ColId="1" Attno="2" ColName="b" TypeMdid="0.23.1.0"/>
- <dxl:Column ColId="2" Attno="-1" ColName="ctid" TypeMdid="0.27.1.0"/>
- <dxl:Column ColId="3" Attno="-3" ColName="xmin" TypeMdid="0.28.1.0"/>
- <dxl:Column ColId="4" Attno="-4" ColName="cmin" TypeMdid="0.29.1.0"/>
- <dxl:Column ColId="5" Attno="-5" ColName="xmax" TypeMdid="0.28.1.0"/>
- <dxl:Column ColId="6" Attno="-6" ColName="cmax" TypeMdid="0.29.1.0"/>
- <dxl:Column ColId="7" Attno="-7" ColName="tableoid" TypeMdid="0.26.1.0"/>
- <dxl:Column ColId="8" Attno="-8" ColName="gp_segment_id" TypeMdid="0.23.1.0"/>
- </dxl:Columns>
- </dxl:TableDescriptor>
- </dxl:TableScan>
- </dxl:GatherMotion>
- </dxl:Aggregate>
+ <dxl:SortingColumnList/>
+ <dxl:NestedLoopJoin JoinType="Inner" IndexNestedLoopJoin="false" OuterRefAsParam="false">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="1293.001976" Rows="1.000000" Width="16"/>
+ </dxl:Properties>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="9" Alias="sum">
+ <dxl:Ident ColId="9" ColName="sum" TypeMdid="0.20.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="10" Alias="avg">
+ <dxl:Ident ColId="10" ColName="avg" TypeMdid="0.1700.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:JoinFilter>
+ <dxl:ConstValue TypeMdid="0.16.1.0" Value="true"/>
+ </dxl:JoinFilter>
+ <dxl:Aggregate AggregationStrategy="Plain" StreamSafe="false">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.000597" Rows="1.000000" Width="8"/>
+ </dxl:Properties>
+ <dxl:GroupingColumns/>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="9" Alias="sum">
+ <dxl:AggFunc AggMdid="0.2108.1.0" AggDistinct="false" AggStage="Final" AggKind="n" AggArgTypes="">
+ <dxl:ValuesList ParamType="aggargs">
+ <dxl:Ident ColId="30" ColName="ColRef_0030" TypeMdid="0.20.1.0"/>
+ </dxl:ValuesList>
+ <dxl:ValuesList ParamType="aggdirectargs"/>
+ <dxl:ValuesList ParamType="aggorder"/>
+ <dxl:ValuesList ParamType="aggdistinct"/>
+ </dxl:AggFunc>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:GatherMotion InputSegments="0,1" OutputSegments="-1">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.000596" Rows="1.000000" Width="8"/>
+ </dxl:Properties>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="30" Alias="ColRef_0030">
+ <dxl:Ident ColId="30" ColName="ColRef_0030" TypeMdid="0.20.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:SortingColumnList/>
+ <dxl:Aggregate AggregationStrategy="Plain" StreamSafe="false">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.000560" Rows="1.000000" Width="8"/>
+ </dxl:Properties>
+ <dxl:GroupingColumns/>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="30" Alias="ColRef_0030">
+ <dxl:AggFunc AggMdid="0.2108.1.0" AggDistinct="true" AggStage="Partial" AggKind="n" AggArgTypes="">
+ <dxl:ValuesList ParamType="aggargs">
+ <dxl:Ident ColId="0" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ValuesList>
+ <dxl:ValuesList ParamType="aggdirectargs"/>
+ <dxl:ValuesList ParamType="aggorder"/>
+ <dxl:ValuesList ParamType="aggdistinct"/>
+ </dxl:AggFunc>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:CTEConsumer CTEId="0" Columns="0,1,2,3,4,5,6,7,8">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.000535" Rows="111.000000" Width="4"/>
+ </dxl:Properties>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="0" Alias="a">
+ <dxl:Ident ColId="0" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="1" Alias="b">
+ <dxl:Ident ColId="1" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="2" Alias="ctid">
+ <dxl:Ident ColId="2" ColName="ctid" TypeMdid="0.27.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="3" Alias="xmin">
+ <dxl:Ident ColId="3" ColName="xmin" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="4" Alias="cmin">
+ <dxl:Ident ColId="4" ColName="cmin" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="5" Alias="xmax">
+ <dxl:Ident ColId="5" ColName="xmax" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="6" Alias="cmax">
+ <dxl:Ident ColId="6" ColName="cmax" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="7" Alias="tableoid">
+ <dxl:Ident ColId="7" ColName="tableoid" TypeMdid="0.26.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="8" Alias="gp_segment_id">
+ <dxl:Ident ColId="8" ColName="gp_segment_id" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ </dxl:CTEConsumer>
+ </dxl:Aggregate>
+ </dxl:GatherMotion>
+ </dxl:Aggregate>
+ <dxl:Materialize Eager="true">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.001300" Rows="1.000000" Width="8"/>
+ </dxl:Properties>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="10" Alias="avg">
+ <dxl:Ident ColId="10" ColName="avg" TypeMdid="0.1700.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:Aggregate AggregationStrategy="Plain" StreamSafe="false">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.001292" Rows="1.000000" Width="8"/>
+ </dxl:Properties>
+ <dxl:GroupingColumns/>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="10" Alias="avg">
+ <dxl:AggFunc AggMdid="0.2101.1.0" AggDistinct="false" AggStage="Final" AggKind="n" AggArgTypes="">
+ <dxl:ValuesList ParamType="aggargs">
+ <dxl:Ident ColId="29" ColName="ColRef_0029" TypeMdid="0.17.1.0"/>
+ </dxl:ValuesList>
+ <dxl:ValuesList ParamType="aggdirectargs"/>
+ <dxl:ValuesList ParamType="aggorder"/>
+ <dxl:ValuesList ParamType="aggdistinct"/>
+ </dxl:AggFunc>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:GatherMotion InputSegments="0,1" OutputSegments="-1">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.001291" Rows="1.000000" Width="8"/>
+ </dxl:Properties>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="29" Alias="ColRef_0029">
+ <dxl:Ident ColId="29" ColName="ColRef_0029" TypeMdid="0.17.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:SortingColumnList/>
+ <dxl:Aggregate AggregationStrategy="Plain" StreamSafe="false">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.001255" Rows="1.000000" Width="8"/>
+ </dxl:Properties>
+ <dxl:GroupingColumns/>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="29" Alias="ColRef_0029">
+ <dxl:AggFunc AggMdid="0.2101.1.0" AggDistinct="true" AggStage="Partial" AggKind="n" AggArgTypes="">
+ <dxl:ValuesList ParamType="aggargs">
+ <dxl:Ident ColId="21" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ValuesList>
+ <dxl:ValuesList ParamType="aggdirectargs"/>
+ <dxl:ValuesList ParamType="aggorder"/>
+ <dxl:ValuesList ParamType="aggdistinct"/>
+ </dxl:AggFunc>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:RedistributeMotion InputSegments="0,1" OutputSegments="0,1">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.001230" Rows="111.000000" Width="4"/>
+ </dxl:Properties>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="21" Alias="b">
+ <dxl:Ident ColId="21" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:SortingColumnList/>
+ <dxl:HashExprList>
+ <dxl:HashExpr>
+ <dxl:Ident ColId="21" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:HashExpr>
+ </dxl:HashExprList>
+ <dxl:Result>
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.000535" Rows="111.000000" Width="4"/>
+ </dxl:Properties>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="21" Alias="b">
+ <dxl:Ident ColId="21" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:OneTimeFilter/>
+ <dxl:CTEConsumer CTEId="0" Columns="20,21,22,23,24,25,26,27,28">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="431.000535" Rows="111.000000" Width="4"/>
+ </dxl:Properties>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="20" Alias="a">
+ <dxl:Ident ColId="20" ColName="a" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="21" Alias="b">
+ <dxl:Ident ColId="21" ColName="b" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="22" Alias="ctid">
+ <dxl:Ident ColId="22" ColName="ctid" TypeMdid="0.27.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="23" Alias="xmin">
+ <dxl:Ident ColId="23" ColName="xmin" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="24" Alias="cmin">
+ <dxl:Ident ColId="24" ColName="cmin" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="25" Alias="xmax">
+ <dxl:Ident ColId="25" ColName="xmax" TypeMdid="0.28.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="26" Alias="cmax">
+ <dxl:Ident ColId="26" ColName="cmax" TypeMdid="0.29.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="27" Alias="tableoid">
+ <dxl:Ident ColId="27" ColName="tableoid" TypeMdid="0.26.1.0"/>
+ </dxl:ProjElem>
+ <dxl:ProjElem ColId="28" Alias="gp_segment_id">
+ <dxl:Ident ColId="28" ColName="gp_segment_id" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ </dxl:CTEConsumer>
+ </dxl:Result>
+ </dxl:RedistributeMotion>
+ </dxl:Aggregate>
+ </dxl:GatherMotion>
+ </dxl:Aggregate>
+ </dxl:Materialize>
+ </dxl:NestedLoopJoin>
+ </dxl:RandomMotion>
+ </dxl:Sequence>
+ </dxl:GatherMotion>
</dxl:Plan>
</dxl:Thread>
</dxl:DXLMessage>
diff --git a/src/backend/gporca/libgpopt/src/xforms/CXformUtils.cpp b/src/backend/gporca/libgpopt/src/xforms/CXformUtils.cpp
index 8a08c8afdb..299a579df5 100644
--- a/src/backend/gporca/libgpopt/src/xforms/CXformUtils.cpp
+++ b/src/backend/gporca/libgpopt/src/xforms/CXformUtils.cpp
@@ -3682,6 +3682,11 @@ CXformUtils::MapPrjElemsWithDistinctAggs(
// use first argument of Distinct Agg as key
pexprKey = (*pexprChild)[0];
}
+ else if (is_distinct && COperator::EopScalarAggFunc == eopidChild)
+ {
+ // use first argument of AggFunc args list as key
+ pexprKey = (*(pexprChild->PdrgPexpr()))[EaggfuncIndexArgs];
+ }
else
{
// use constant True as key
diff --git a/src/test/regress/expected/gp_dqa.out b/src/test/regress/expected/gp_dqa.out
index 08f4530477..0e731124f7 100644
--- a/src/test/regress/expected/gp_dqa.out
+++ b/src/test/regress/expected/gp_dqa.out
@@ -2474,3 +2474,487 @@ drop table multiagg2;
reset optimizer_force_multistage_agg;
reset optimizer_enable_use_distribution_in_dqa;
drop table t_issue_659;
+-- DQA with Agg(Intermediate Agg)
+set enable_hashagg=on;
+set enable_groupagg=off;
+create table dqa_f3(a int, b int, c int, d int, e int ) distributed by (a);
+insert into dqa_f3 select i % 17, i % 5 , i % 3, i %10, i % 7 from generate_series(1,1000) i;
+analyze dqa_f3;
+/*
+ * Test distinct or group by column is distributed key
+ *
+ * 1. If the input's locus matches the DISTINCT, but not GROUP BY:
+ *
+ * HashAggregate
+ * -> Redistribute (according to GROUP BY)
+ * -> HashAggregate (to eliminate duplicates)
+ * -> input (hashed by GROUP BY + DISTINCT)
+ *
+ * 2. If the input's locus matches the GROUP BY(don't care DISTINCT any more):
+ *
+ * HashAggregate (to aggregate)
+ * -> HashAggregate (to eliminate duplicates)
+ * -> input (hashed by GROUP BY)
+ *
+ */
+explain (verbose on, costs off)select sum(Distinct a), count(b), sum(c) from dqa_f3 group by e;
+ QUERY PLAN
+--------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: (sum(a)), (count(b)), (sum(c)), e
+ -> Finalize HashAggregate
+ Output: sum(a), count(b), sum(c), e
+ Group Key: dqa_f3.e
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Output: e, a, (PARTIAL count(b)), (PARTIAL sum(c))
+ Hash Key: e
+ -> Partial HashAggregate
+ Output: e, a, PARTIAL count(b), PARTIAL sum(c)
+ Group Key: dqa_f3.e, dqa_f3.a
+ -> Seq Scan on public.dqa_f3
+ Output: a, b, c, d, e
+ Optimizer: Postgres query optimizer
+ Settings: enable_groupagg = 'off', gp_motion_cost_per_row = '1'
+(15 rows)
+
+select sum(Distinct a), count(b), sum(c) from dqa_f3 group by e;
+ sum | count | sum
+-----+-------+-----
+ 136 | 143 | 142
+ 136 | 143 | 143
+ 136 | 143 | 142
+ 136 | 143 | 143
+ 136 | 143 | 144
+ 136 | 142 | 142
+ 136 | 143 | 144
+(7 rows)
+
+explain (verbose on, costs off) select sum(Distinct e), count(b), sum(c) from dqa_f3 group by a;
+ QUERY PLAN
+-----------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: (sum(e)), (count(b)), (sum(c)), a
+ -> Finalize HashAggregate
+ Output: sum(e), count(b), sum(c), a
+ Group Key: dqa_f3.a
+ -> Partial HashAggregate
+ Output: a, e, PARTIAL count(b), PARTIAL sum(c)
+ Group Key: dqa_f3.a, dqa_f3.e
+ -> Seq Scan on public.dqa_f3
+ Output: a, b, c, d, e
+ Optimizer: Postgres query optimizer
+ Settings: enable_groupagg = 'off', gp_motion_cost_per_row = '1'
+(12 rows)
+
+select sum(Distinct e), count(b), sum(c) from dqa_f3 group by a;
+ sum | count | sum
+-----+-------+-----
+ 21 | 58 | 59
+ 21 | 59 | 58
+ 21 | 58 | 57
+ 21 | 59 | 59
+ 21 | 59 | 58
+ 21 | 59 | 59
+ 21 | 59 | 58
+ 21 | 59 | 60
+ 21 | 58 | 58
+ 21 | 59 | 60
+ 21 | 59 | 60
+ 21 | 59 | 58
+ 21 | 59 | 58
+ 21 | 59 | 59
+ 21 | 59 | 60
+ 21 | 59 | 59
+ 21 | 59 | 60
+(17 rows)
+
+/*
+ * Test both distinct and group by column are not distributed key
+ *
+ * HashAgg (to aggregate)
+ * -> HashAgg (to eliminate duplicates)
+ * -> Redistribute (according to GROUP BY)
+ * -> Streaming HashAgg (to eliminate duplicates)
+ * -> input
+ *
+ */
+explain (verbose on, costs off) select sum(Distinct c), count(a), sum(d) from dqa_f3 group by b;
+ QUERY PLAN
+--------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: (sum(c)), (count(a)), (sum(d)), b
+ -> Finalize HashAggregate
+ Output: sum(c), count(a), sum(d), b
+ Group Key: dqa_f3.b
+ -> Partial HashAggregate
+ Output: b, c, PARTIAL count(a), PARTIAL sum(d)
+ Group Key: dqa_f3.b, dqa_f3.c
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Output: b, c, (PARTIAL count(a)), (PARTIAL sum(d))
+ Hash Key: b
+ -> Streaming Partial HashAggregate
+ Output: b, c, PARTIAL count(a), PARTIAL sum(d)
+ Group Key: dqa_f3.b, dqa_f3.c
+ -> Seq Scan on public.dqa_f3
+ Output: a, b, c, d, e
+ Optimizer: Postgres query optimizer
+ Settings: enable_groupagg = 'off', gp_motion_cost_per_row = '1'
+(18 rows)
+
+select sum(Distinct c), count(a), sum(d) from dqa_f3 group by b;
+ sum | count | sum
+-----+-------+------
+ 3 | 200 | 1100
+ 3 | 200 | 1300
+ 3 | 200 | 900
+ 3 | 200 | 500
+ 3 | 200 | 700
+(5 rows)
+
+explain (verbose on, costs off) select sum(Distinct c), count(a), sum(d) from dqa_f3 group by b order by b;
+ QUERY PLAN
+--------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: (sum(c)), (count(a)), (sum(d)), b
+ Merge Key: b
+ -> Sort
+ Output: (sum(c)), (count(a)), (sum(d)), b
+ Sort Key: dqa_f3.b
+ -> Finalize HashAggregate
+ Output: sum(c), count(a), sum(d), b
+ Group Key: dqa_f3.b
+ -> Partial HashAggregate
+ Output: b, c, PARTIAL count(a), PARTIAL sum(d)
+ Group Key: dqa_f3.b, dqa_f3.c
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Output: b, c, (PARTIAL count(a)), (PARTIAL sum(d))
+ Hash Key: b
+ -> Streaming Partial HashAggregate
+ Output: b, c, PARTIAL count(a), PARTIAL sum(d)
+ Group Key: dqa_f3.b, dqa_f3.c
+ -> Seq Scan on public.dqa_f3
+ Output: a, b, c, d, e
+ Optimizer: Postgres query optimizer
+ Settings: enable_groupagg = 'off', gp_motion_cost_per_row = '1'
+(22 rows)
+
+select sum(Distinct c), count(a), sum(d) from dqa_f3 group by b order by b;
+ sum | count | sum
+-----+-------+------
+ 3 | 200 | 500
+ 3 | 200 | 700
+ 3 | 200 | 900
+ 3 | 200 | 1100
+ 3 | 200 | 1300
+(5 rows)
+
+explain (verbose on, costs off) select distinct sum(Distinct c), count(a), sum(d) from dqa_f3 group by b;
+ QUERY PLAN
+--------------------------------------------------------------------------------------
+ HashAggregate
+ Output: (sum(c)), (count(a)), (sum(d)), b
+ Group Key: (sum(dqa_f3.c)), (count(dqa_f3.a)), (sum(dqa_f3.d))
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ Output: (sum(c)), (count(a)), (sum(d)), b
+ -> HashAggregate
+ Output: (sum(c)), (count(a)), (sum(d)), b
+ Group Key: sum(dqa_f3.c), count(dqa_f3.a), sum(dqa_f3.d)
+ -> Finalize HashAggregate
+ Output: sum(c), count(a), sum(d), b
+ Group Key: dqa_f3.b
+ -> Partial HashAggregate
+ Output: b, c, PARTIAL count(a), PARTIAL sum(d)
+ Group Key: dqa_f3.b, dqa_f3.c
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Output: b, c, (PARTIAL count(a)), (PARTIAL sum(d))
+ Hash Key: b
+ -> Streaming Partial HashAggregate
+ Output: b, c, PARTIAL count(a), PARTIAL sum(d)
+ Group Key: dqa_f3.b, dqa_f3.c
+ -> Seq Scan on public.dqa_f3
+ Output: a, b, c, d, e
+ Optimizer: Postgres query optimizer
+ Settings: enable_groupagg = 'off', gp_motion_cost_per_row = '1'
+(24 rows)
+
+select distinct sum(Distinct c), count(a), sum(d) from dqa_f3 group by b;
+ sum | count | sum
+-----+-------+------
+ 3 | 200 | 500
+ 3 | 200 | 900
+ 3 | 200 | 700
+ 3 | 200 | 1100
+ 3 | 200 | 1300
+(5 rows)
+
+explain (verbose on, costs off) select sum(Distinct c), count(a), sum(d) from dqa_f3 group by b having avg(e) > 3;
+ QUERY PLAN
+------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: (sum(c)), (count(a)), (sum(d)), b
+ -> Finalize HashAggregate
+ Output: sum(c), count(a), sum(d), b
+ Group Key: dqa_f3.b
+ Filter: (avg(dqa_f3.e) > '3'::numeric)
+ -> Partial HashAggregate
+ Output: b, c, PARTIAL count(a), PARTIAL sum(d), PARTIAL avg(e)
+ Group Key: dqa_f3.b, dqa_f3.c
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Output: b, c, (PARTIAL count(a)), (PARTIAL sum(d)), (PARTIAL avg(e))
+ Hash Key: b
+ -> Streaming Partial HashAggregate
+ Output: b, c, PARTIAL count(a), PARTIAL sum(d), PARTIAL avg(e)
+ Group Key: dqa_f3.b, dqa_f3.c
+ -> Seq Scan on public.dqa_f3
+ Output: a, b, c, d, e
+ Optimizer: Postgres query optimizer
+ Settings: enable_groupagg = 'off', gp_motion_cost_per_row = '1'
+(19 rows)
+
+select sum(Distinct c), count(a), sum(d) from dqa_f3 group by b having avg(e) > 3;
+ sum | count | sum
+-----+-------+------
+ 3 | 200 | 1100
+ 3 | 200 | 500
+ 3 | 200 | 700
+(3 rows)
+
+explain (verbose on, costs off)
+select sum(Distinct sub.c), count(a), sum(d)
+ from dqa_f3 left join(select x, coalesce(y, 5) as c from dqa_f2) as sub
+ on sub.x = dqa_f3.e group by b;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: (sum((COALESCE(dqa_f2.y, 5)))), (count(dqa_f3.a)), (sum(dqa_f3.d)), dqa_f3.b
+ -> Finalize HashAggregate
+ Output: sum((COALESCE(dqa_f2.y, 5))), count(dqa_f3.a), sum(dqa_f3.d), dqa_f3.b
+ Group Key: dqa_f3.b
+ -> Partial HashAggregate
+ Output: dqa_f3.b, (COALESCE(dqa_f2.y, 5)), PARTIAL count(dqa_f3.a), PARTIAL sum(dqa_f3.d)
+ Group Key: dqa_f3.b, (COALESCE(dqa_f2.y, 5))
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Output: dqa_f3.b, (COALESCE(dqa_f2.y, 5)), (PARTIAL count(dqa_f3.a)), (PARTIAL sum(dqa_f3.d))
+ Hash Key: dqa_f3.b
+ -> Streaming Partial HashAggregate
+ Output: dqa_f3.b, (COALESCE(dqa_f2.y, 5)), PARTIAL count(dqa_f3.a), PARTIAL sum(dqa_f3.d)
+ Group Key: dqa_f3.b, (COALESCE(dqa_f2.y, 5))
+ -> Hash Right Join
+ Output: dqa_f3.b, (COALESCE(dqa_f2.y, 5)), dqa_f3.a, dqa_f3.d
+ Hash Cond: (dqa_f2.x = dqa_f3.e)
+ -> Seq Scan on public.dqa_f2
+ Output: dqa_f2.x, COALESCE(dqa_f2.y, 5)
+ -> Hash
+ Output: dqa_f3.a, dqa_f3.d, dqa_f3.b, dqa_f3.e
+ -> Redistribute Motion 3:3 (slice3; segments: 3)
+ Output: dqa_f3.a, dqa_f3.d, dqa_f3.b, dqa_f3.e
+ Hash Key: dqa_f3.e
+ -> Seq Scan on public.dqa_f3
+ Output: dqa_f3.a, dqa_f3.d, dqa_f3.b, dqa_f3.e
+ Optimizer: Postgres query optimizer
+ Settings: enable_groupagg = 'off', gp_motion_cost_per_row = '1'
+(28 rows)
+
+select sum(Distinct sub.c), count(a), sum(d)
+ from dqa_f3 left join(select x, coalesce(y, 5) as c from dqa_f2) as sub
+ on sub.x = dqa_f3.e group by b;
+ sum | count | sum
+-----+-------+-------
+ 10 | 15372 | 84546
+ 10 | 15371 | 99914
+ 10 | 15371 | 69167
+ 10 | 15372 | 38430
+ 10 | 15372 | 53802
+(5 rows)
+
+-- Test gp_enable_agg_distinct_pruning is off on this branch
+set gp_enable_agg_distinct_pruning = off;
+explain (verbose on, costs off) select sum(Distinct c), count(a), sum(d) from dqa_f3 group by b;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: (sum(c)), (count(a)), (sum(d)), b
+ -> Finalize HashAggregate
+ Output: sum(c), count(a), sum(d), b
+ Group Key: dqa_f3.b
+ -> Partial HashAggregate
+ Output: b, c, PARTIAL count(a), PARTIAL sum(d)
+ Group Key: dqa_f3.b, dqa_f3.c
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Output: b, c, a, d
+ Hash Key: b
+ -> Seq Scan on public.dqa_f3
+ Output: b, c, a, d
+ Optimizer: Postgres query optimizer
+ Settings: enable_groupagg = 'off', gp_enable_agg_distinct_pruning = 'off', gp_motion_cost_per_row = '1'
+(15 rows)
+
+select sum(Distinct c), count(a), sum(d) from dqa_f3 group by b;
+ sum | count | sum
+-----+-------+------
+ 3 | 200 | 500
+ 3 | 200 | 700
+ 3 | 200 | 1100
+ 3 | 200 | 1300
+ 3 | 200 | 900
+(5 rows)
+
+reset gp_enable_agg_distinct_pruning;
+/*
+ * Test multistage through Gather Motion(grouplocus cannot hashed or not exist)
+ *
+ * Finalize Aggregate
+ * -> Gather Motion
+ * -> Partial Aggregate
+ * -> HashAggregate, to remove duplicates
+ * -> Redistribute Motion (according to DISTINCT arg)
+ * -> Streaming HashAgg (to eliminate duplicates)
+ * -> input
+ */
+explain (verbose on, costs off) select sum(Distinct b), count(c), sum(a) from dqa_f3;
+ QUERY PLAN
+-----------------------------------------------------------------------
+ Finalize Aggregate
+ Output: sum(b), count(c), sum(a)
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ Output: b, (PARTIAL count(c)), (PARTIAL sum(a))
+ -> Partial HashAggregate
+ Output: b, PARTIAL count(c), PARTIAL sum(a)
+ Group Key: dqa_f3.b
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Output: b, (PARTIAL count(c)), (PARTIAL sum(a))
+ Hash Key: b
+ -> Streaming Partial HashAggregate
+ Output: b, PARTIAL count(c), PARTIAL sum(a)
+ Group Key: dqa_f3.b
+ -> Seq Scan on public.dqa_f3
+ Output: a, b, c, d, e
+ Optimizer: Postgres query optimizer
+ Settings: enable_groupagg = 'off', gp_motion_cost_per_row = '1'
+(17 rows)
+
+select sum(Distinct b), count(c), sum(a) from dqa_f3;
+ sum | count | sum
+-----+-------+------
+ 10 | 1000 | 7993
+(1 row)
+
+explain (verbose on, costs off) select distinct sum(Distinct b), count(c), sum(a) from dqa_f3;
+ QUERY PLAN
+-----------------------------------------------------------------------------------
+ Unique
+ Output: (sum(b)), (count(c)), (sum(a))
+ Group Key: (sum(b)), (count(c)), (sum(a))
+ -> Sort
+ Output: (sum(b)), (count(c)), (sum(a))
+ Sort Key: (sum(dqa_f3.b)), (count(dqa_f3.c)), (sum(dqa_f3.a))
+ -> Finalize Aggregate
+ Output: sum(b), count(c), sum(a)
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ Output: b, (PARTIAL count(c)), (PARTIAL sum(a))
+ -> Partial HashAggregate
+ Output: b, PARTIAL count(c), PARTIAL sum(a)
+ Group Key: dqa_f3.b
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Output: b, (PARTIAL count(c)), (PARTIAL sum(a))
+ Hash Key: b
+ -> Streaming Partial HashAggregate
+ Output: b, PARTIAL count(c), PARTIAL sum(a)
+ Group Key: dqa_f3.b
+ -> Seq Scan on public.dqa_f3
+ Output: a, b, c, d, e
+ Optimizer: Postgres query optimizer
+ Settings: enable_groupagg = 'off', gp_motion_cost_per_row = '1'
+(23 rows)
+
+select distinct sum(Distinct b), count(c), sum(a) from dqa_f3;
+ sum | count | sum
+-----+-------+------
+ 10 | 1000 | 7993
+(1 row)
+
+explain (verbose on, costs off) select sum(Distinct b), count(c) filter(where c > 1), sum(a) from dqa_f3;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------
+ Finalize Aggregate
+ Output: sum(b), count(c) FILTER (WHERE (c > 1)), sum(a)
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ Output: b, (PARTIAL count(c) FILTER (WHERE (c > 1))), (PARTIAL sum(a))
+ -> Partial HashAggregate
+ Output: b, PARTIAL count(c) FILTER (WHERE (c > 1)), PARTIAL sum(a)
+ Group Key: dqa_f3.b
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Output: b, (PARTIAL count(c) FILTER (WHERE (c > 1))), (PARTIAL sum(a))
+ Hash Key: b
+ -> Streaming Partial HashAggregate
+ Output: b, PARTIAL count(c) FILTER (WHERE (c > 1)), PARTIAL sum(a)
+ Group Key: dqa_f3.b
+ -> Seq Scan on public.dqa_f3
+ Output: a, b, c, d, e
+ Optimizer: Postgres query optimizer
+ Settings: enable_groupagg = 'off', gp_motion_cost_per_row = '1'
+(17 rows)
+
+select sum(Distinct b), count(c) filter(where c > 1), sum(a) from dqa_f3;
+ sum | count | sum
+-----+-------+------
+ 10 | 333 | 7993
+(1 row)
+
+drop table dqa_f3;
+-- Test some corner case of dqa ex.NULL
+create table dqa_f4(a int, b int, c int);
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table.
+HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
+insert into dqa_f4 values(null, null, null);
+insert into dqa_f4 values(1, 1, 1);
+insert into dqa_f4 values(2, 2, 2);
+select count(distinct a), count(distinct b) from dqa_f4 group by c;
+ count | count
+-------+-------
+ 0 | 0
+ 1 | 1
+ 1 | 1
+(3 rows)
+
+set optimizer_enable_multiple_distinct_aggs=on;
+explain (verbose on, costs off) select count(distinct a), count(distinct b) from dqa_f4 group by c;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------
+ Finalize HashAggregate
+ Output: count(a), count(b), c
+ Group Key: dqa_f4.c
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ Output: c, (PARTIAL count(a)), (PARTIAL count(b))
+ -> Partial HashAggregate
+ Output: c, PARTIAL count(a), PARTIAL count(b)
+ Group Key: dqa_f4.c
+ -> HashAggregate
+ Output: c, a, b, (AggExprId)
+ Group Key: (AggExprId), dqa_f4.a, dqa_f4.b, dqa_f4.c
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Output: c, a, b, (AggExprId)
+ Hash Key: c, a, b, (AggExprId)
+ -> Streaming HashAggregate
+ Output: c, a, b, (AggExprId)
+ Group Key: AggExprId, dqa_f4.a, dqa_f4.b, dqa_f4.c
+ -> TupleSplit
+ Output: c, a, b, AggExprId
+ Split by Col: (dqa_f4.a), (dqa_f4.b)
+ Group Key: dqa_f4.c
+ -> Seq Scan on public.dqa_f4
+ Output: a, b, c
+ Optimizer: Postgres query optimizer
+ Settings: enable_groupagg = 'off', gp_motion_cost_per_row = '1', optimizer = 'off', optimizer_enable_multiple_distinct_aggs = 'on'
+(25 rows)
+
+select count(distinct a), count(distinct b) from dqa_f4 group by c;
+ count | count
+-------+-------
+ 0 | 0
+ 1 | 1
+ 1 | 1
+(3 rows)
+
+reset optimizer_enable_multiple_distinct_aggs;
+drop table dqa_f4;
diff --git a/src/test/regress/expected/gp_dqa_optimizer.out b/src/test/regress/expected/gp_dqa_optimizer.out
index 1cff4210fa..6e97a37820 100644
--- a/src/test/regress/expected/gp_dqa_optimizer.out
+++ b/src/test/regress/expected/gp_dqa_optimizer.out
@@ -2636,3 +2636,559 @@ drop table multiagg2;
reset optimizer_force_multistage_agg;
reset optimizer_enable_use_distribution_in_dqa;
drop table t_issue_659;
+-- DQA with Agg(Intermediate Agg)
+set enable_hashagg=on;
+set enable_groupagg=off;
+create table dqa_f3(a int, b int, c int, d int, e int ) distributed by (a);
+insert into dqa_f3 select i % 17, i % 5 , i % 3, i %10, i % 7 from generate_series(1,1000) i;
+analyze dqa_f3;
+/*
+ * Test distinct or group by column is distributed key
+ *
+ * 1. If the input's locus matches the DISTINCT, but not GROUP BY:
+ *
+ * HashAggregate
+ * -> Redistribute (according to GROUP BY)
+ * -> HashAggregate (to eliminate duplicates)
+ * -> input (hashed by GROUP BY + DISTINCT)
+ *
+ * 2. If the input's locus matches the GROUP BY(don't care DISTINCT any more):
+ *
+ * HashAggregate (to aggregate)
+ * -> HashAggregate (to eliminate duplicates)
+ * -> input (hashed by GROUP BY)
+ *
+ */
+explain (verbose on, costs off)select sum(Distinct a), count(b), sum(c) from dqa_f3 group by e;
+ QUERY PLAN
+--------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: (sum(a)), (count(b)), (sum(c))
+ -> Finalize HashAggregate
+ Output: sum(a), count(b), sum(c)
+ Group Key: dqa_f3.e
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Output: a, e, (PARTIAL count(b)), (PARTIAL sum(c))
+ Hash Key: e
+ -> Partial HashAggregate
+ Output: a, e, PARTIAL count(b), PARTIAL sum(c)
+ Group Key: dqa_f3.e, dqa_f3.a
+ -> Seq Scan on public.dqa_f3
+ Output: a, b, c, e
+ Optimizer: Pivotal Optimizer (GPORCA)
+ Settings: enable_groupagg = 'off', gp_motion_cost_per_row = '1'
+(15 rows)
+
+select sum(Distinct a), count(b), sum(c) from dqa_f3 group by e;
+ sum | count | sum
+-----+-------+-----
+ 136 | 142 | 142
+ 136 | 143 | 144
+ 136 | 143 | 142
+ 136 | 143 | 143
+ 136 | 143 | 144
+ 136 | 143 | 142
+ 136 | 143 | 143
+(7 rows)
+
+explain (verbose on, costs off) select sum(Distinct e), count(b), sum(c) from dqa_f3 group by a;
+ QUERY PLAN
+-----------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: (sum(e)), (count(b)), (sum(c))
+ -> Finalize HashAggregate
+ Output: sum(e), count(b), sum(c)
+ Group Key: dqa_f3.a
+ -> Partial HashAggregate
+ Output: PARTIAL count(b), PARTIAL sum(c), a, e
+ Group Key: dqa_f3.a, dqa_f3.e
+ -> Seq Scan on public.dqa_f3
+ Output: a, b, c, e
+ Optimizer: Pivotal Optimizer (GPORCA)
+ Settings: enable_groupagg = 'off', gp_motion_cost_per_row = '1'
+(12 rows)
+
+select sum(Distinct e), count(b), sum(c) from dqa_f3 group by a;
+ sum | count | sum
+-----+-------+-----
+ 21 | 58 | 57
+ 21 | 58 | 59
+ 21 | 59 | 58
+ 21 | 59 | 59
+ 21 | 59 | 59
+ 21 | 59 | 58
+ 21 | 59 | 60
+ 21 | 59 | 60
+ 21 | 58 | 58
+ 21 | 59 | 58
+ 21 | 59 | 60
+ 21 | 59 | 58
+ 21 | 59 | 58
+ 21 | 59 | 59
+ 21 | 59 | 60
+ 21 | 59 | 59
+ 21 | 59 | 60
+(17 rows)
+
+/*
+ * Test both distinct and group by column are not distributed key
+ *
+ * HashAgg (to aggregate)
+ * -> HashAgg (to eliminate duplicates)
+ * -> Redistribute (according to GROUP BY)
+ * -> Streaming HashAgg (to eliminate duplicates)
+ * -> input
+ *
+ */
+explain (verbose on, costs off) select sum(Distinct c), count(a), sum(d) from dqa_f3 group by b;
+ QUERY PLAN
+--------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: (sum(c)), (count(a)), (sum(d))
+ -> Finalize HashAggregate
+ Output: sum(c), count(a), sum(d)
+ Group Key: dqa_f3.b
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Output: b, c, (PARTIAL count(a)), (PARTIAL sum(d))
+ Hash Key: b
+ -> Partial GroupAggregate
+ Output: b, c, PARTIAL count(a), PARTIAL sum(d)
+ Group Key: dqa_f3.b, dqa_f3.c
+ -> Sort
+ Output: b, c, (PARTIAL count(a)), (PARTIAL sum(d))
+ Sort Key: dqa_f3.b, dqa_f3.c
+ -> Redistribute Motion 3:3 (slice3; segments: 3)
+ Output: b, c, (PARTIAL count(a)), (PARTIAL sum(d))
+ Hash Key: b, c
+ -> Streaming Partial HashAggregate
+ Output: b, c, PARTIAL count(a), PARTIAL sum(d)
+ Group Key: dqa_f3.b, dqa_f3.c
+ -> Seq Scan on public.dqa_f3
+ Output: a, b, c, d
+ Optimizer: Pivotal Optimizer (GPORCA)
+ Settings: enable_groupagg = 'off', gp_motion_cost_per_row = '1'
+(24 rows)
+
+select sum(Distinct c), count(a), sum(d) from dqa_f3 group by b;
+ sum | count | sum
+-----+-------+------
+ 3 | 200 | 1100
+ 3 | 200 | 900
+ 3 | 200 | 1300
+ 3 | 200 | 500
+ 3 | 200 | 700
+(5 rows)
+
+explain (verbose on, costs off) select sum(Distinct c), count(a), sum(d) from dqa_f3 group by b order by b;
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------
+ Result
+ Output: (sum(c)), (count(a)), (sum(d))
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ Output: (sum(c)), (count(a)), (sum(d)), b
+ Merge Key: b
+ -> Sort
+ Output: (sum(c)), (count(a)), (sum(d)), b
+ Sort Key: dqa_f3.b
+ -> Finalize HashAggregate
+ Output: sum(c), count(a), sum(d), b
+ Group Key: dqa_f3.b
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Output: b, c, (PARTIAL count(a)), (PARTIAL sum(d))
+ Hash Key: b
+ -> Partial GroupAggregate
+ Output: b, c, PARTIAL count(a), PARTIAL sum(d)
+ Group Key: dqa_f3.b, dqa_f3.c
+ -> Sort
+ Output: b, c, (PARTIAL count(a)), (PARTIAL sum(d))
+ Sort Key: dqa_f3.b, dqa_f3.c
+ -> Redistribute Motion 3:3 (slice3; segments: 3)
+ Output: b, c, (PARTIAL count(a)), (PARTIAL sum(d))
+ Hash Key: b, c
+ -> Streaming Partial HashAggregate
+ Output: b, c, PARTIAL count(a), PARTIAL sum(d)
+ Group Key: dqa_f3.b, dqa_f3.c
+ -> Seq Scan on public.dqa_f3
+ Output: a, b, c, d
+ Optimizer: Pivotal Optimizer (GPORCA)
+ Settings: enable_groupagg = 'off', gp_motion_cost_per_row = '1'
+(30 rows)
+
+select sum(Distinct c), count(a), sum(d) from dqa_f3 group by b order by b;
+ sum | count | sum
+-----+-------+------
+ 3 | 200 | 500
+ 3 | 200 | 700
+ 3 | 200 | 900
+ 3 | 200 | 1100
+ 3 | 200 | 1300
+(5 rows)
+
+explain (verbose on, costs off) select distinct sum(Distinct c), count(a), sum(d) from dqa_f3 group by b;
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: (sum(c)), (count(a)), (sum(d))
+ -> GroupAggregate
+ Output: (sum(c)), (count(a)), (sum(d))
+ Group Key: (sum(dqa_f3.c)), (count(dqa_f3.a)), (sum(dqa_f3.d))
+ -> Sort
+ Output: (sum(c)), (count(a)), (sum(d))
+ Sort Key: (sum(dqa_f3.c)), (count(dqa_f3.a)), (sum(dqa_f3.d))
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Output: (sum(c)), (count(a)), (sum(d))
+ Hash Key: (sum(c)), (count(a)), (sum(d))
+ -> GroupAggregate
+ Output: (sum(c)), (count(a)), (sum(d))
+ Group Key: (sum(dqa_f3.c)), (count(dqa_f3.a)), (sum(dqa_f3.d))
+ -> Sort
+ Output: (sum(c)), (count(a)), (sum(d)), b
+ Sort Key: (sum(dqa_f3.c)), (count(dqa_f3.a)), (sum(dqa_f3.d))
+ -> Finalize HashAggregate
+ Output: sum(c), count(a), sum(d), b
+ Group Key: dqa_f3.b
+ -> Redistribute Motion 3:3 (slice3; segments: 3)
+ Output: b, c, (PARTIAL count(a)), (PARTIAL sum(d))
+ Hash Key: b
+ -> Partial GroupAggregate
+ Output: b, c, PARTIAL count(a), PARTIAL sum(d)
+ Group Key: dqa_f3.b, dqa_f3.c
+ -> Sort
+ Output: b, c, (PARTIAL count(a)), (PARTIAL sum(d))
+ Sort Key: dqa_f3.b, dqa_f3.c
+ -> Redistribute Motion 3:3 (slice4; segments: 3)
+ Output: b, c, (PARTIAL count(a)), (PARTIAL sum(d))
+ Hash Key: b, c
+ -> Streaming Partial HashAggregate
+ Output: b, c, PARTIAL count(a), PARTIAL sum(d)
+ Group Key: dqa_f3.b, dqa_f3.c
+ -> Seq Scan on public.dqa_f3
+ Output: a, b, c, d
+ Optimizer: Pivotal Optimizer (GPORCA)
+ Settings: enable_groupagg = 'off', gp_motion_cost_per_row = '1'
+(39 rows)
+
+select distinct sum(Distinct c), count(a), sum(d) from dqa_f3 group by b;
+ sum | count | sum
+-----+-------+------
+ 3 | 200 | 700
+ 3 | 200 | 900
+ 3 | 200 | 500
+ 3 | 200 | 1300
+ 3 | 200 | 1100
+(5 rows)
+
+explain (verbose on, costs off) select sum(Distinct c), count(a), sum(d) from dqa_f3 group by b having avg(e) > 3;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: (sum(c)), (count(a)), (sum(d))
+ -> Result
+ Output: (sum(c)), (count(a)), (sum(d))
+ Filter: ((avg(dqa_f3.e)) > '3'::numeric)
+ -> Finalize HashAggregate
+ Output: sum(c), count(a), sum(d), avg(e), b
+ Group Key: dqa_f3.b
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Output: b, c, (PARTIAL count(a)), (PARTIAL sum(d)), (PARTIAL avg(e))
+ Hash Key: b
+ -> Partial HashAggregate
+ Output: b, c, PARTIAL count(a), PARTIAL sum(d), PARTIAL avg(e)
+ Group Key: dqa_f3.b, dqa_f3.c
+ -> Redistribute Motion 3:3 (slice3; segments: 3)
+ Output: b, c, (PARTIAL count(a)), (PARTIAL sum(d)), (PARTIAL avg(e))
+ Hash Key: b, c
+ -> Streaming Partial HashAggregate
+ Output: b, c, PARTIAL count(a), PARTIAL sum(d), PARTIAL avg(e)
+ Group Key: dqa_f3.b, dqa_f3.c
+ -> Seq Scan on public.dqa_f3
+ Output: a, b, c, d, e
+ Optimizer: Pivotal Optimizer (GPORCA)
+ Settings: enable_groupagg = 'off', gp_motion_cost_per_row = '1'
+(24 rows)
+
+select sum(Distinct c), count(a), sum(d) from dqa_f3 group by b having avg(e) > 3;
+ sum | count | sum
+-----+-------+------
+ 3 | 200 | 500
+ 3 | 200 | 700
+ 3 | 200 | 1100
+(3 rows)
+
+explain (verbose on, costs off)
+select sum(Distinct sub.c), count(a), sum(d)
+ from dqa_f3 left join(select x, coalesce(y, 5) as c from dqa_f2) as sub
+ on sub.x = dqa_f3.e group by b;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: (sum((COALESCE(dqa_f2.y, 5)))), (count(dqa_f3.a)), (sum(dqa_f3.d))
+ -> Finalize HashAggregate
+ Output: sum((COALESCE(dqa_f2.y, 5))), count(dqa_f3.a), sum(dqa_f3.d)
+ Group Key: dqa_f3.b
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Output: dqa_f3.b, (COALESCE(dqa_f2.y, 5)), (PARTIAL count(dqa_f3.a)), (PARTIAL sum(dqa_f3.d))
+ Hash Key: dqa_f3.b
+ -> Partial GroupAggregate
+ Output: dqa_f3.b, (COALESCE(dqa_f2.y, 5)), PARTIAL count(dqa_f3.a), PARTIAL sum(dqa_f3.d)
+ Group Key: dqa_f3.b, (COALESCE(dqa_f2.y, 5))
+ -> Sort
+ Output: dqa_f3.b, (COALESCE(dqa_f2.y, 5)), (PARTIAL count(dqa_f3.a)), (PARTIAL sum(dqa_f3.d))
+ Sort Key: dqa_f3.b, (COALESCE(dqa_f2.y, 5))
+ -> Redistribute Motion 3:3 (slice3; segments: 3)
+ Output: dqa_f3.b, (COALESCE(dqa_f2.y, 5)), (PARTIAL count(dqa_f3.a)), (PARTIAL sum(dqa_f3.d))
+ Hash Key: dqa_f3.b, (COALESCE(dqa_f2.y, 5))
+ -> Streaming Partial HashAggregate
+ Output: dqa_f3.b, (COALESCE(dqa_f2.y, 5)), PARTIAL count(dqa_f3.a), PARTIAL sum(dqa_f3.d)
+ Group Key: dqa_f3.b, (COALESCE(dqa_f2.y, 5))
+ -> Hash Left Join
+ Output: dqa_f3.a, dqa_f3.b, dqa_f3.d, (COALESCE(dqa_f2.y, 5))
+ Hash Cond: (dqa_f3.e = dqa_f2.x)
+ -> Seq Scan on public.dqa_f3
+ Output: dqa_f3.a, dqa_f3.b, dqa_f3.d, dqa_f3.e
+ -> Hash
+ Output: (COALESCE(dqa_f2.y, 5)), dqa_f2.x
+ -> Result
+ Output: COALESCE(dqa_f2.y, 5), dqa_f2.x
+ -> Broadcast Motion 3:3 (slice4; segments: 3)
+ Output: dqa_f2.x, dqa_f2.y
+ -> Seq Scan on public.dqa_f2
+ Output: dqa_f2.x, dqa_f2.y
+ Optimizer: Pivotal Optimizer (GPORCA)
+ Settings: enable_groupagg = 'off', gp_motion_cost_per_row = '1'
+(35 rows)
+
+select sum(Distinct sub.c), count(a), sum(d)
+ from dqa_f3 left join(select x, coalesce(y, 5) as c from dqa_f2) as sub
+ on sub.x = dqa_f3.e group by b;
+ sum | count | sum
+-----+-------+-------
+ 10 | 15372 | 38430
+ 10 | 15372 | 53802
+ 10 | 15372 | 84546
+ 10 | 15371 | 99914
+ 10 | 15371 | 69167
+(5 rows)
+
+-- Test gp_enable_agg_distinct_pruning is off on this branch
+set gp_enable_agg_distinct_pruning = off;
+explain (verbose on, costs off) select sum(Distinct c), count(a), sum(d) from dqa_f3 group by b;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: (sum(c)), (count(a)), (sum(d))
+ -> Finalize HashAggregate
+ Output: sum(c), count(a), sum(d)
+ Group Key: dqa_f3.b
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Output: b, c, (PARTIAL count(a)), (PARTIAL sum(d))
+ Hash Key: b
+ -> Partial GroupAggregate
+ Output: b, c, PARTIAL count(a), PARTIAL sum(d)
+ Group Key: dqa_f3.b, dqa_f3.c
+ -> Sort
+ Output: b, c, (PARTIAL count(a)), (PARTIAL sum(d))
+ Sort Key: dqa_f3.b, dqa_f3.c
+ -> Redistribute Motion 3:3 (slice3; segments: 3)
+ Output: b, c, (PARTIAL count(a)), (PARTIAL sum(d))
+ Hash Key: b, c
+ -> Streaming Partial HashAggregate
+ Output: b, c, PARTIAL count(a), PARTIAL sum(d)
+ Group Key: dqa_f3.b, dqa_f3.c
+ -> Seq Scan on public.dqa_f3
+ Output: a, b, c, d
+ Optimizer: Pivotal Optimizer (GPORCA)
+ Settings: enable_groupagg = 'off', gp_enable_agg_distinct_pruning = 'off', gp_motion_cost_per_row = '1'
+(24 rows)
+
+select sum(Distinct c), count(a), sum(d) from dqa_f3 group by b;
+ sum | count | sum
+-----+-------+------
+ 3 | 200 | 1100
+ 3 | 200 | 900
+ 3 | 200 | 1300
+ 3 | 200 | 500
+ 3 | 200 | 700
+(5 rows)
+
+reset gp_enable_agg_distinct_pruning;
+/*
+ * Test multistage through Gather Motion(grouplocus cannot hashed or not exist)
+ *
+ * Finalize Aggregate
+ * -> Gather Motion
+ * -> Partial Aggregate
+ * -> HashAggregate, to remove duplicates
+ * -> Redistribute Motion (according to DISTINCT arg)
+ * -> Streaming HashAgg (to eliminate duplicates)
+ * -> input
+ */
+explain (verbose on, costs off) select sum(Distinct b), count(c), sum(a) from dqa_f3;
+ QUERY PLAN
+-----------------------------------------------------------------------------
+ Finalize Aggregate
+ Output: sum(b), count(c), sum(a)
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ Output: b, (PARTIAL count(c)), (PARTIAL sum(a))
+ -> Partial GroupAggregate
+ Output: b, PARTIAL count(c), PARTIAL sum(a)
+ Group Key: dqa_f3.b
+ -> Sort
+ Output: b, (PARTIAL count(c)), (PARTIAL sum(a))
+ Sort Key: dqa_f3.b
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Output: b, (PARTIAL count(c)), (PARTIAL sum(a))
+ Hash Key: b
+ -> Streaming Partial HashAggregate
+ Output: b, PARTIAL count(c), PARTIAL sum(a)
+ Group Key: dqa_f3.b
+ -> Seq Scan on public.dqa_f3
+ Output: a, b, c
+ Optimizer: Pivotal Optimizer (GPORCA)
+ Settings: enable_groupagg = 'off', gp_motion_cost_per_row = '1'
+(20 rows)
+
+select sum(Distinct b), count(c), sum(a) from dqa_f3;
+ sum | count | sum
+-----+-------+------
+ 10 | 1000 | 7993
+(1 row)
+
+explain (verbose on, costs off) select distinct sum(Distinct b), count(c), sum(a) from dqa_f3;
+ QUERY PLAN
+-----------------------------------------------------------------------------
+ Finalize Aggregate
+ Output: sum(b), count(c), sum(a)
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ Output: b, (PARTIAL count(c)), (PARTIAL sum(a))
+ -> Partial GroupAggregate
+ Output: b, PARTIAL count(c), PARTIAL sum(a)
+ Group Key: dqa_f3.b
+ -> Sort
+ Output: b, (PARTIAL count(c)), (PARTIAL sum(a))
+ Sort Key: dqa_f3.b
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Output: b, (PARTIAL count(c)), (PARTIAL sum(a))
+ Hash Key: b
+ -> Streaming Partial HashAggregate
+ Output: b, PARTIAL count(c), PARTIAL sum(a)
+ Group Key: dqa_f3.b
+ -> Seq Scan on public.dqa_f3
+ Output: a, b, c
+ Optimizer: Pivotal Optimizer (GPORCA)
+ Settings: enable_groupagg = 'off', gp_motion_cost_per_row = '1'
+(20 rows)
+
+select distinct sum(Distinct b), count(c), sum(a) from dqa_f3;
+ sum | count | sum
+-----+-------+------
+ 10 | 1000 | 7993
+(1 row)
+
+explain (verbose on, costs off) select sum(Distinct b), count(c) filter(where c > 1), sum(a) from dqa_f3;
+INFO: GPORCA failed to produce a plan, falling back to planner
+DETAIL: Feature not supported: Aggregate functions with FILTER
+ QUERY PLAN
+----------------------------------------------------------------------------------------------
+ Finalize Aggregate
+ Output: sum(b), count(c) FILTER (WHERE (c > 1)), sum(a)
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ Output: b, (PARTIAL count(c) FILTER (WHERE (c > 1))), (PARTIAL sum(a))
+ -> Partial HashAggregate
+ Output: b, PARTIAL count(c) FILTER (WHERE (c > 1)), PARTIAL sum(a)
+ Group Key: dqa_f3.b
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Output: b, (PARTIAL count(c) FILTER (WHERE (c > 1))), (PARTIAL sum(a))
+ Hash Key: b
+ -> Streaming Partial HashAggregate
+ Output: b, PARTIAL count(c) FILTER (WHERE (c > 1)), PARTIAL sum(a)
+ Group Key: dqa_f3.b
+ -> Seq Scan on public.dqa_f3
+ Output: a, b, c, d, e
+ Optimizer: Postgres query optimizer
+ Settings: enable_groupagg = 'off', gp_motion_cost_per_row = '1'
+(17 rows)
+
+select sum(Distinct b), count(c) filter(where c > 1), sum(a) from dqa_f3;
+INFO: GPORCA failed to produce a plan, falling back to planner
+DETAIL: Feature not supported: Aggregate functions with FILTER
+ sum | count | sum
+-----+-------+------
+ 10 | 333 | 7993
+(1 row)
+
+drop table dqa_f3;
+-- Test some corner case of dqa ex.NULL
+create table dqa_f4(a int, b int, c int);
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table.
+HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
+insert into dqa_f4 values(null, null, null);
+insert into dqa_f4 values(1, 1, 1);
+insert into dqa_f4 values(2, 2, 2);
+select count(distinct a), count(distinct b) from dqa_f4 group by c;
+INFO: GPORCA failed to produce a plan, falling back to planner
+DETAIL: Feature not supported: Multiple Distinct Qualified Aggregates are disabled in the optimizer
+ count | count
+-------+-------
+ 0 | 0
+ 1 | 1
+ 1 | 1
+(3 rows)
+
+set optimizer_enable_multiple_distinct_aggs=on;
+explain (verbose on, costs off) select count(distinct a), count(distinct b) from dqa_f4 group by c;
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: (count(DISTINCT share0_ref3.a)), (count(DISTINCT share0_ref2.b))
+ -> Sequence
+ Output: (count(DISTINCT share0_ref3.a)), (count(DISTINCT share0_ref2.b))
+ -> Shared Scan (share slice:id 1:0)
+ Output: share0_ref1.a, share0_ref1.b, share0_ref1.c, share0_ref1.ctid, share0_ref1.xmin, share0_ref1.cmin, share0_ref1.xmax, share0_ref1.cmax, share0_ref1.tableoid, share0_ref1.gp_segment_id
+ -> Seq Scan on public.dqa_f4
+ Output: dqa_f4.a, dqa_f4.b, dqa_f4.c, dqa_f4.ctid, dqa_f4.xmin, dqa_f4.cmin, dqa_f4.xmax, dqa_f4.cmax, dqa_f4.tableoid, dqa_f4.gp_segment_id
+ -> Hash Join
+ Output: (count(DISTINCT share0_ref3.a)), (count(DISTINCT share0_ref2.b))
+ Hash Cond: (NOT (share0_ref3.c IS DISTINCT FROM share0_ref2.c))
+ -> GroupAggregate
+ Output: count(DISTINCT share0_ref3.a), share0_ref3.c
+ Group Key: share0_ref3.c
+ -> Sort
+ Output: share0_ref3.a, share0_ref3.c
+ Sort Key: share0_ref3.c
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Output: share0_ref3.a, share0_ref3.c
+ Hash Key: share0_ref3.c
+ -> Result
+ Output: share0_ref3.a, share0_ref3.c
+ -> Shared Scan (share slice:id 2:0)
+ Output: share0_ref3.a, share0_ref3.b, share0_ref3.c, share0_ref3.ctid, share0_ref3.xmin, share0_ref3.cmin, share0_ref3.xmax, share0_ref3.cmax, share0_ref3.tableoid, share0_ref3.gp_segment_id
+ -> Hash
+ Output: (count(DISTINCT share0_ref2.b)), share0_ref2.c
+ -> GroupAggregate
+ Output: count(DISTINCT share0_ref2.b), share0_ref2.c
+ Group Key: share0_ref2.c
+ -> Sort
+ Output: share0_ref2.b, share0_ref2.c
+ Sort Key: share0_ref2.c
+ -> Redistribute Motion 3:3 (slice3; segments: 3)
+ Output: share0_ref2.b, share0_ref2.c
+ Hash Key: share0_ref2.c
+ -> Result
+ Output: share0_ref2.b, share0_ref2.c
+ -> Shared Scan (share slice:id 3:0)
+ Output: share0_ref2.a, share0_ref2.b, share0_ref2.c, share0_ref2.ctid, share0_ref2.xmin, share0_ref2.cmin, share0_ref2.xmax, share0_ref2.cmax, share0_ref2.tableoid, share0_ref2.gp_segment_id
+ Optimizer: Pivotal Optimizer (GPORCA)
+ Settings: enable_groupagg = 'off', gp_motion_cost_per_row = '1', optimizer_enable_multiple_distinct_aggs = 'on'
+(41 rows)
+
+select count(distinct a), count(distinct b) from dqa_f4 group by c;
+ count | count
+-------+-------
+ 1 | 1
+ 1 | 1
+ 0 | 0
+(3 rows)
+
+reset optimizer_enable_multiple_distinct_aggs;
+drop table dqa_f4;
diff --git a/src/test/regress/sql/gp_dqa.sql b/src/test/regress/sql/gp_dqa.sql
index 1b6c2acb25..b5180e49e3 100644
--- a/src/test/regress/sql/gp_dqa.sql
+++ b/src/test/regress/sql/gp_dqa.sql
@@ -452,3 +452,107 @@ drop table multiagg2;
reset optimizer_force_multistage_agg;
reset optimizer_enable_use_distribution_in_dqa;
drop table t_issue_659;
+
+-- DQA with Agg(Intermediate Agg)
+set enable_hashagg=on;
+set enable_groupagg=off;
+
+create table dqa_f3(a int, b int, c int, d int, e int ) distributed by (a);
+insert into dqa_f3 select i % 17, i % 5 , i % 3, i %10, i % 7 from generate_series(1,1000) i;
+analyze dqa_f3;
+
+/*
+ * Test distinct or group by column is distributed key
+ *
+ * 1. If the input's locus matches the DISTINCT, but not GROUP BY:
+ *
+ * HashAggregate
+ * -> Redistribute (according to GROUP BY)
+ * -> HashAggregate (to eliminate duplicates)
+ * -> input (hashed by GROUP BY + DISTINCT)
+ *
+ * 2. If the input's locus matches the GROUP BY(don't care DISTINCT any more):
+ *
+ * HashAggregate (to aggregate)
+ * -> HashAggregate (to eliminate duplicates)
+ * -> input (hashed by GROUP BY)
+ *
+ */
+explain (verbose on, costs off)select sum(Distinct a), count(b), sum(c) from dqa_f3 group by e;
+select sum(Distinct a), count(b), sum(c) from dqa_f3 group by e;
+
+explain (verbose on, costs off) select sum(Distinct e), count(b), sum(c) from dqa_f3 group by a;
+select sum(Distinct e), count(b), sum(c) from dqa_f3 group by a;
+
+/*
+ * Test both distinct and group by column are not distributed key
+ *
+ * HashAgg (to aggregate)
+ * -> HashAgg (to eliminate duplicates)
+ * -> Redistribute (according to GROUP BY)
+ * -> Streaming HashAgg (to eliminate duplicates)
+ * -> input
+ *
+ */
+explain (verbose on, costs off) select sum(Distinct c), count(a), sum(d) from dqa_f3 group by b;
+select sum(Distinct c), count(a), sum(d) from dqa_f3 group by b;
+
+explain (verbose on, costs off) select sum(Distinct c), count(a), sum(d) from dqa_f3 group by b order by b;
+select sum(Distinct c), count(a), sum(d) from dqa_f3 group by b order by b;
+
+explain (verbose on, costs off) select distinct sum(Distinct c), count(a), sum(d) from dqa_f3 group by b;
+select distinct sum(Distinct c), count(a), sum(d) from dqa_f3 group by b;
+
+explain (verbose on, costs off) select sum(Distinct c), count(a), sum(d) from dqa_f3 group by b having avg(e) > 3;
+select sum(Distinct c), count(a), sum(d) from dqa_f3 group by b having avg(e) > 3;
+
+explain (verbose on, costs off)
+select sum(Distinct sub.c), count(a), sum(d)
+ from dqa_f3 left join(select x, coalesce(y, 5) as c from dqa_f2) as sub
+ on sub.x = dqa_f3.e group by b;
+select sum(Distinct sub.c), count(a), sum(d)
+ from dqa_f3 left join(select x, coalesce(y, 5) as c from dqa_f2) as sub
+ on sub.x = dqa_f3.e group by b;
+
+-- Test gp_enable_agg_distinct_pruning is off on this branch
+set gp_enable_agg_distinct_pruning = off;
+explain (verbose on, costs off) select sum(Distinct c), count(a), sum(d) from dqa_f3 group by b;
+select sum(Distinct c), count(a), sum(d) from dqa_f3 group by b;
+reset gp_enable_agg_distinct_pruning;
+
+/*
+ * Test multistage through Gather Motion(grouplocus cannot hashed or not exist)
+ *
+ * Finalize Aggregate
+ * -> Gather Motion
+ * -> Partial Aggregate
+ * -> HashAggregate, to remove duplicates
+ * -> Redistribute Motion (according to DISTINCT arg)
+ * -> Streaming HashAgg (to eliminate duplicates)
+ * -> input
+ */
+explain (verbose on, costs off) select sum(Distinct b), count(c), sum(a) from dqa_f3;
+select sum(Distinct b), count(c), sum(a) from dqa_f3;
+
+explain (verbose on, costs off) select distinct sum(Distinct b), count(c), sum(a) from dqa_f3;
+select distinct sum(Distinct b), count(c), sum(a) from dqa_f3;
+
+explain (verbose on, costs off) select sum(Distinct b), count(c) filter(where c > 1), sum(a) from dqa_f3;
+select sum(Distinct b), count(c) filter(where c > 1), sum(a) from dqa_f3;
+
+drop table dqa_f3;
+
+-- Test some corner case of dqa ex.NULL
+create table dqa_f4(a int, b int, c int);
+insert into dqa_f4 values(null, null, null);
+insert into dqa_f4 values(1, 1, 1);
+insert into dqa_f4 values(2, 2, 2);
+
+select count(distinct a), count(distinct b) from dqa_f4 group by c;
+
+set optimizer_enable_multiple_distinct_aggs=on;
+explain (verbose on, costs off) select count(distinct a), count(distinct b) from dqa_f4 group by c;
+select count(distinct a), count(distinct b) from dqa_f4 group by c;
+reset optimizer_enable_multiple_distinct_aggs;
+
+drop table dqa_f4;
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org