This is an automated email from the ASF dual-hosted git repository.
morningman pushed a commit to branch tpcds
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/tpcds by this push:
new c13d2a5cb5f [opt](nereids) if column stats are unknown, 10-20
table-join optimization use cascading instead of dphyp (#29902)
c13d2a5cb5f is described below
commit c13d2a5cb5f9600585d2c2a90828aecf9efa1eda
Author: minghong <[email protected]>
AuthorDate: Tue Jan 16 15:22:36 2024 +0800
[opt](nereids) if column stats are unknown, 10-20 table-join optimization
use cascading instead of dphyp (#29902)
* if column stats are unknown, do not use dphyp
tpcds query64 is optimized in case of no stats
sf500, query64 improved from 15sec to 7sec on hdfs, and from 4sec to
3.85sec on olaptable
---
.../org/apache/doris/nereids/StatementContext.java | 15 ++
.../doris/nereids/jobs/executor/Optimizer.java | 14 +-
.../doris/nereids/stats/StatsCalculator.java | 7 +
.../noStatsRfPrune/query64.out | 165 ++++++++++-----------
.../no_stats_shape/query64.out | 165 ++++++++++-----------
5 files changed, 195 insertions(+), 171 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/StatementContext.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/StatementContext.java
index f2e5370d952..c4a6e37bbec 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/StatementContext.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/StatementContext.java
@@ -74,6 +74,13 @@ public class StatementContext {
private boolean isDpHyp = false;
private boolean isOtherJoinReorder = false;
+ // hasUnknownColStats true if any column stats in the tables used by this
sql is unknown
+ // the algorithm to derive plan when column stats are unknown is
implemented in cascading framework, not in dphyper.
+ // And hence, when column stats are unknown, even if the tables used by a
sql is more than
+ // MAX_TABLE_COUNT_USE_CASCADES_JOIN_REORDER, join reorder should choose
cascading framework.
+ // Thus hasUnknownColStats has higher priority than isDpHyp
+ private boolean hasUnknownColStats = false;
+
private final IdGenerator<ExprId> exprIdGenerator =
ExprId.createGenerator();
private final IdGenerator<ObjectId> objectIdGenerator =
ObjectId.createGenerator();
private final IdGenerator<RelationId> relationIdGenerator =
RelationId.createGenerator();
@@ -261,4 +268,12 @@ public class StatementContext {
public void addJoinFilters(Collection<Expression> newJoinFilters) {
this.joinFilters.addAll(newJoinFilters);
}
+
+ public boolean isHasUnknownColStats() {
+ return hasUnknownColStats;
+ }
+
+ public void setHasUnknownColStats(boolean hasUnknownColStats) {
+ this.hasUnknownColStats = hasUnknownColStats;
+ }
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/executor/Optimizer.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/executor/Optimizer.java
index 19dd3b00bba..4f042c527cb 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/executor/Optimizer.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/executor/Optimizer.java
@@ -22,6 +22,7 @@ import org.apache.doris.nereids.jobs.cascades.DeriveStatsJob;
import org.apache.doris.nereids.jobs.cascades.OptimizeGroupJob;
import org.apache.doris.nereids.jobs.joinorder.JoinOrderJob;
import org.apache.doris.nereids.memo.Group;
+import org.apache.doris.qe.ConnectContext;
import org.apache.doris.qe.SessionVariable;
import java.util.Objects;
@@ -49,11 +50,22 @@ public class Optimizer {
cascadesContext.pushJob(new
DeriveStatsJob(cascadesContext.getMemo().getRoot().getLogicalExpression(),
cascadesContext.getCurrentJobContext()));
cascadesContext.getJobScheduler().executeJobPool(cascadesContext);
+ boolean optimizeWithUnknownColStats = false;
+ if (ConnectContext.get() != null &&
ConnectContext.get().getStatementContext() != null) {
+ if
(ConnectContext.get().getStatementContext().isHasUnknownColStats()) {
+ optimizeWithUnknownColStats = true;
+ }
+ }
// DPHyp optimize
+ int maxTableCount =
getSessionVariable().getMaxTableCountUseCascadesJoinReorder();
+ if (optimizeWithUnknownColStats) {
+ // if column stats are unknown, 10~20 table-join is optimized by
cascading framework
+ maxTableCount = 2 * maxTableCount;
+ }
int maxJoinCount = cascadesContext.getMemo().countMaxContinuousJoin();
cascadesContext.getStatementContext().setMaxContinuousJoin(maxJoinCount);
boolean isDpHyp = getSessionVariable().enableDPHypOptimizer
- || maxJoinCount >
getSessionVariable().getMaxTableCountUseCascadesJoinReorder();
+ || maxJoinCount > maxTableCount;
cascadesContext.getStatementContext().setDpHyp(isDpHyp);
cascadesContext.getStatementContext().setOtherJoinReorder(false);
if (!getSessionVariable().isDisableJoinReorder() && isDpHyp
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/StatsCalculator.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/StatsCalculator.java
index 9812dc0f0f6..14ead9990aa 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/StatsCalculator.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/StatsCalculator.java
@@ -623,6 +623,7 @@ public class StatsCalculator extends
DefaultPlanVisitor<Statistics, Void> {
Map<Expression, ColumnStatistic> columnStatisticMap = new HashMap<>();
TableIf table = catalogRelation.getTable();
double rowCount = catalogRelation.getTable().estimatedRowCount();
+ boolean hasUnknownCol = false;
for (SlotReference slotReference : slotSet) {
String colName = slotReference.getName();
boolean shouldIgnoreThisCol =
StatisticConstants.shouldIgnoreCol(table, slotReference.getColumn().get());
@@ -644,13 +645,19 @@ public class StatsCalculator extends
DefaultPlanVisitor<Statistics, Void> {
}
if (!cache.isUnKnown) {
rowCount = Math.max(rowCount, cache.count);
+ } else {
+ hasUnknownCol = true;
}
if (ConnectContext.get() != null &&
ConnectContext.get().getSessionVariable().enableStats) {
columnStatisticMap.put(slotReference, cache);
} else {
columnStatisticMap.put(slotReference, ColumnStatistic.UNKNOWN);
+ hasUnknownCol = true;
}
}
+ if (hasUnknownCol && ConnectContext.get() != null &&
ConnectContext.get().getStatementContext() != null) {
+
ConnectContext.get().getStatementContext().setHasUnknownColStats(true);
+ }
Statistics stats = new Statistics(rowCount, columnStatisticMap);
stats = normalizeCatalogRelationColumnStatsRowCount(stats);
return stats;
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/noStatsRfPrune/query64.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/noStatsRfPrune/query64.out
index 1494aa9d683..eb79094ac60 100644
---
a/regression-test/data/nereids_tpcds_shape_sf100_p0/noStatsRfPrune/query64.out
+++
b/regression-test/data/nereids_tpcds_shape_sf100_p0/noStatsRfPrune/query64.out
@@ -5,105 +5,100 @@ PhysicalCteAnchor ( cteId=CTEId#1 )
----PhysicalProject
------hashAgg[LOCAL]
--------PhysicalProject
-----------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_item_sk =
cs_ui.cs_item_sk)) otherCondition=() build RFs:RF19
cs_item_sk->[ss_item_sk,i_item_sk,sr_item_sk]
-------------PhysicalProject
---------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_promo_sk =
promotion.p_promo_sk)) otherCondition=()
-----------------PhysicalProject
-------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_sold_date_sk = d1.d_date_sk)) otherCondition=()
build RFs:RF17 d_date_sk->[ss_sold_date_sk]
---------------------PhysicalProject
-----------------------hashJoin[INNER_JOIN]
hashCondition=((hd2.hd_income_band_sk = ib2.ib_income_band_sk))
otherCondition=()
-------------------------PhysicalProject
---------------------------hashJoin[INNER_JOIN]
hashCondition=((hd1.hd_income_band_sk = ib1.ib_income_band_sk))
otherCondition=()
-----------------------------PhysicalProject
-------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_hdemo_sk = hd1.hd_demo_sk)) otherCondition=()
+----------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_item_sk =
item.i_item_sk)) otherCondition=() build RFs:RF19
i_item_sk->[ss_item_sk,sr_item_sk,cs_item_sk]
+------------PhysicalDistribute[DistributionSpecHash]
+--------------PhysicalProject
+----------------hashJoin[INNER_JOIN] hashCondition=((hd2.hd_income_band_sk =
ib2.ib_income_band_sk)) otherCondition=()
+------------------PhysicalProject
+--------------------hashJoin[INNER_JOIN] hashCondition=((hd1.hd_income_band_sk
= ib1.ib_income_band_sk)) otherCondition=()
+----------------------PhysicalProject
+------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_store_sk = store.s_store_sk)) otherCondition=()
+--------------------------PhysicalProject
+----------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_current_hdemo_sk = hd2.hd_demo_sk)) otherCondition=()
+------------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_current_addr_sk = ad2.ca_address_sk))
otherCondition=()
--------------------------------PhysicalProject
-----------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_addr_sk = ad1.ca_address_sk)) otherCondition=()
-------------------------------------PhysicalProject
---------------------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_current_hdemo_sk = hd2.hd_demo_sk)) otherCondition=()
-----------------------------------------PhysicalProject
-------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_store_sk = store.s_store_sk)) otherCondition=()
---------------------------------------------PhysicalProject
-----------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_item_sk = store_returns.sr_item_sk) and
(store_sales.ss_ticket_number = store_returns.sr_ticket_number))
otherCondition=()
-------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_item_sk = item.i_item_sk)) otherCondition=()
build RFs:RF8 i_item_sk->[ss_item_sk]
+----------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_promo_sk = promotion.p_promo_sk))
otherCondition=()
+------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_hdemo_sk = hd1.hd_demo_sk)) otherCondition=()
+--------------------------------------PhysicalProject
+----------------------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_first_shipto_date_sk = d3.d_date_sk))
otherCondition=()
+------------------------------------------PhysicalProject
+--------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_first_sales_date_sk = d2.d_date_sk))
otherCondition=()
+----------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_addr_sk = ad1.ca_address_sk)) otherCondition=()
+------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_current_cdemo_sk = cd2.cd_demo_sk))
otherCondition=(( not (cd_marital_status = cd_marital_status)))
--------------------------------------------------PhysicalDistribute[DistributionSpecHash]
----------------------------------------------------PhysicalProject
-------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_first_shipto_date_sk = d3.d_date_sk))
otherCondition=()
---------------------------------------------------------PhysicalProject
-----------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_first_sales_date_sk = d2.d_date_sk))
otherCondition=()
-------------------------------------------------------------PhysicalProject
---------------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_current_addr_sk = ad2.ca_address_sk))
otherCondition=()
+------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_sold_date_sk = d1.d_date_sk)) otherCondition=()
build RFs:RF7 d_date_sk->[ss_sold_date_sk]
+--------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_cdemo_sk = cd1.cd_demo_sk)) otherCondition=()
+----------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_customer_sk = customer.c_customer_sk))
otherCondition=()
+--------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
----------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_cdemo_sk = cd1.cd_demo_sk)) otherCondition=((
not (cd_marital_status = cd_marital_status)))
---------------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
-----------------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_customer_sk = customer.c_customer_sk))
otherCondition=()
---------------------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
-----------------------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------------------PhysicalOlapScan[store_sales]
apply RFs: RF8 RF17 RF19
---------------------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
-----------------------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_current_cdemo_sk = cd2.cd_demo_sk))
otherCondition=()
---------------------------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
-----------------------------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------------------------PhysicalOlapScan[customer]
---------------------------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
-----------------------------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------------------------PhysicalOlapScan[customer_demographics]
---------------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_item_sk = cs_ui.cs_item_sk)) otherCondition=()
build RFs:RF4 cs_item_sk->[ss_item_sk,sr_item_sk]
+--------------------------------------------------------------------PhysicalProject
+----------------------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_item_sk = store_returns.sr_item_sk) and
(store_sales.ss_ticket_number = store_returns.sr_ticket_number))
otherCondition=()
+------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------PhysicalOlapScan[store_sales]
apply RFs: RF4 RF7 RF19
+------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------PhysicalOlapScan[store_returns]
apply RFs: RF4 RF19
+--------------------------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
----------------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------------PhysicalOlapScan[customer_demographics]
-----------------------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
-------------------------------------------------------------------PhysicalProject
---------------------------------------------------------------------PhysicalOlapScan[customer_address]
-------------------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
---------------------------------------------------------------PhysicalProject
-----------------------------------------------------------------PhysicalOlapScan[date_dim]
+------------------------------------------------------------------------filter((sale
> (2 * refund)))
+--------------------------------------------------------------------------hashAgg[GLOBAL]
+----------------------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------------------------------------------------hashAgg[LOCAL]
+--------------------------------------------------------------------------------PhysicalProject
+----------------------------------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((catalog_sales.cs_item_sk = catalog_returns.cr_item_sk) and
(catalog_sales.cs_order_number = catalog_returns.cr_order_number))
otherCondition=()
+------------------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------------------PhysicalOlapScan[catalog_sales]
apply RFs: RF19
+------------------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------------------PhysicalOlapScan[catalog_returns]
+--------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+----------------------------------------------------------------PhysicalProject
+------------------------------------------------------------------PhysicalOlapScan[customer]
+----------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------PhysicalOlapScan[customer_demographics]
--------------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
----------------------------------------------------------PhysicalProject
-------------------------------------------------------------PhysicalOlapScan[date_dim]
+------------------------------------------------------------filter(d_year IN
(2001, 2002))
+--------------------------------------------------------------PhysicalOlapScan[date_dim]
--------------------------------------------------PhysicalDistribute[DistributionSpecHash]
----------------------------------------------------PhysicalProject
-------------------------------------------------------filter((item.i_current_price
<= 33.00) and (item.i_current_price >= 24.00) and i_color IN ('blanched',
'brown', 'burlywood', 'chocolate', 'drab', 'medium'))
---------------------------------------------------------PhysicalOlapScan[item]
apply RFs: RF9 RF19
-------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------------------------PhysicalOlapScan[customer_demographics]
+------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
--------------------------------------------------PhysicalProject
-----------------------------------------------------PhysicalOlapScan[store_returns]
apply RFs: RF19
---------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
-----------------------------------------------PhysicalProject
-------------------------------------------------PhysicalOlapScan[store]
-----------------------------------------PhysicalDistribute[DistributionSpecReplicated]
-------------------------------------------PhysicalProject
---------------------------------------------PhysicalOlapScan[household_demographics]
+----------------------------------------------------PhysicalOlapScan[customer_address]
+----------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
+------------------------------------------------PhysicalProject
+--------------------------------------------------PhysicalOlapScan[date_dim]
+------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
+--------------------------------------------PhysicalProject
+----------------------------------------------PhysicalOlapScan[date_dim]
+--------------------------------------PhysicalDistribute[DistributionSpecReplicated]
+----------------------------------------PhysicalProject
+------------------------------------------PhysicalOlapScan[household_demographics]
------------------------------------PhysicalDistribute[DistributionSpecReplicated]
--------------------------------------PhysicalProject
-----------------------------------------PhysicalOlapScan[customer_address]
+----------------------------------------PhysicalOlapScan[promotion]
--------------------------------PhysicalDistribute[DistributionSpecReplicated]
----------------------------------PhysicalProject
-------------------------------------PhysicalOlapScan[household_demographics]
-----------------------------PhysicalDistribute[DistributionSpecReplicated]
-------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[income_band]
-------------------------PhysicalDistribute[DistributionSpecReplicated]
---------------------------PhysicalProject
-----------------------------PhysicalOlapScan[income_band]
---------------------PhysicalDistribute[DistributionSpecReplicated]
-----------------------PhysicalProject
-------------------------filter(d_year IN (2001, 2002))
---------------------------PhysicalOlapScan[date_dim]
-----------------PhysicalDistribute[DistributionSpecReplicated]
-------------------PhysicalProject
---------------------PhysicalOlapScan[promotion]
-------------PhysicalProject
---------------filter((sale > (2 * refund)))
-----------------hashAgg[GLOBAL]
-------------------PhysicalDistribute[DistributionSpecHash]
---------------------hashAgg[LOCAL]
-----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN]
hashCondition=((catalog_sales.cs_item_sk = catalog_returns.cr_item_sk) and
(catalog_sales.cs_order_number = catalog_returns.cr_order_number))
otherCondition=()
---------------------------PhysicalProject
-----------------------------PhysicalOlapScan[catalog_sales]
---------------------------PhysicalProject
-----------------------------PhysicalOlapScan[catalog_returns]
+------------------------------------PhysicalOlapScan[customer_address]
+------------------------------PhysicalDistribute[DistributionSpecReplicated]
+--------------------------------PhysicalProject
+----------------------------------PhysicalOlapScan[household_demographics]
+--------------------------PhysicalDistribute[DistributionSpecReplicated]
+----------------------------PhysicalProject
+------------------------------PhysicalOlapScan[store]
+----------------------PhysicalDistribute[DistributionSpecReplicated]
+------------------------PhysicalProject
+--------------------------PhysicalOlapScan[income_band]
+------------------PhysicalDistribute[DistributionSpecReplicated]
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[income_band]
+------------PhysicalDistribute[DistributionSpecHash]
+--------------PhysicalProject
+----------------filter((item.i_current_price <= 33.00) and
(item.i_current_price >= 24.00) and i_color IN ('blanched', 'brown',
'burlywood', 'chocolate', 'drab', 'medium'))
+------------------PhysicalOlapScan[item]
--PhysicalResultSink
----PhysicalQuickSort[MERGE_SORT]
------PhysicalDistribute[DistributionSpecGather]
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/no_stats_shape/query64.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/no_stats_shape/query64.out
index 2caec5e3f93..e3b2f84df37 100644
---
a/regression-test/data/nereids_tpcds_shape_sf100_p0/no_stats_shape/query64.out
+++
b/regression-test/data/nereids_tpcds_shape_sf100_p0/no_stats_shape/query64.out
@@ -5,105 +5,100 @@ PhysicalCteAnchor ( cteId=CTEId#1 )
----PhysicalProject
------hashAgg[LOCAL]
--------PhysicalProject
-----------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_item_sk =
cs_ui.cs_item_sk)) otherCondition=() build RFs:RF19
cs_item_sk->[ss_item_sk,i_item_sk,sr_item_sk]
-------------PhysicalProject
---------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_promo_sk =
promotion.p_promo_sk)) otherCondition=() build RFs:RF18
p_promo_sk->[ss_promo_sk]
-----------------PhysicalProject
-------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_sold_date_sk = d1.d_date_sk)) otherCondition=()
build RFs:RF17 d_date_sk->[ss_sold_date_sk]
---------------------PhysicalProject
-----------------------hashJoin[INNER_JOIN]
hashCondition=((hd2.hd_income_band_sk = ib2.ib_income_band_sk))
otherCondition=() build RFs:RF16 ib_income_band_sk->[hd_income_band_sk]
-------------------------PhysicalProject
---------------------------hashJoin[INNER_JOIN]
hashCondition=((hd1.hd_income_band_sk = ib1.ib_income_band_sk))
otherCondition=() build RFs:RF15 ib_income_band_sk->[hd_income_band_sk]
-----------------------------PhysicalProject
-------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_hdemo_sk = hd1.hd_demo_sk)) otherCondition=()
build RFs:RF14 hd_demo_sk->[ss_hdemo_sk]
+----------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_item_sk =
item.i_item_sk)) otherCondition=() build RFs:RF19
i_item_sk->[ss_item_sk,sr_item_sk,cs_item_sk]
+------------PhysicalDistribute[DistributionSpecHash]
+--------------PhysicalProject
+----------------hashJoin[INNER_JOIN] hashCondition=((hd2.hd_income_band_sk =
ib2.ib_income_band_sk)) otherCondition=() build RFs:RF18
ib_income_band_sk->[hd_income_band_sk]
+------------------PhysicalProject
+--------------------hashJoin[INNER_JOIN] hashCondition=((hd1.hd_income_band_sk
= ib1.ib_income_band_sk)) otherCondition=() build RFs:RF17
ib_income_band_sk->[hd_income_band_sk]
+----------------------PhysicalProject
+------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_store_sk = store.s_store_sk)) otherCondition=()
build RFs:RF16 s_store_sk->[ss_store_sk]
+--------------------------PhysicalProject
+----------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_current_hdemo_sk = hd2.hd_demo_sk))
otherCondition=() build RFs:RF15 hd_demo_sk->[c_current_hdemo_sk]
+------------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_current_addr_sk = ad2.ca_address_sk))
otherCondition=() build RFs:RF14 ca_address_sk->[c_current_addr_sk]
--------------------------------PhysicalProject
-----------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_addr_sk = ad1.ca_address_sk)) otherCondition=()
build RFs:RF13 ca_address_sk->[ss_addr_sk]
-------------------------------------PhysicalProject
---------------------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_current_hdemo_sk = hd2.hd_demo_sk))
otherCondition=() build RFs:RF12 hd_demo_sk->[c_current_hdemo_sk]
-----------------------------------------PhysicalProject
-------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_store_sk = store.s_store_sk)) otherCondition=()
build RFs:RF11 s_store_sk->[ss_store_sk]
---------------------------------------------PhysicalProject
-----------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_item_sk = store_returns.sr_item_sk) and
(store_sales.ss_ticket_number = store_returns.sr_ticket_number))
otherCondition=() build RFs:RF9 sr_item_sk->[ss_item_sk,i_item_sk];RF10
sr_ticket_number->[ss_ticket_number]
-------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_item_sk = item.i_item_sk)) otherCondition=()
build RFs:RF8 i_item_sk->[ss_item_sk]
+----------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_promo_sk = promotion.p_promo_sk))
otherCondition=() build RFs:RF13 p_promo_sk->[ss_promo_sk]
+------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_hdemo_sk = hd1.hd_demo_sk)) otherCondition=()
build RFs:RF12 hd_demo_sk->[ss_hdemo_sk]
+--------------------------------------PhysicalProject
+----------------------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_first_shipto_date_sk = d3.d_date_sk))
otherCondition=() build RFs:RF11 d_date_sk->[c_first_shipto_date_sk]
+------------------------------------------PhysicalProject
+--------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_first_sales_date_sk = d2.d_date_sk))
otherCondition=() build RFs:RF10 d_date_sk->[c_first_sales_date_sk]
+----------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_addr_sk = ad1.ca_address_sk)) otherCondition=()
build RFs:RF9 ca_address_sk->[ss_addr_sk]
+------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_current_cdemo_sk = cd2.cd_demo_sk))
otherCondition=(( not (cd_marital_status = cd_marital_status))) build RFs:RF8
cd_demo_sk->[c_current_cdemo_sk]
--------------------------------------------------PhysicalDistribute[DistributionSpecHash]
----------------------------------------------------PhysicalProject
-------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_first_shipto_date_sk = d3.d_date_sk))
otherCondition=() build RFs:RF7 d_date_sk->[c_first_shipto_date_sk]
---------------------------------------------------------PhysicalProject
-----------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_first_sales_date_sk = d2.d_date_sk))
otherCondition=() build RFs:RF6 d_date_sk->[c_first_sales_date_sk]
-------------------------------------------------------------PhysicalProject
---------------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_current_addr_sk = ad2.ca_address_sk))
otherCondition=() build RFs:RF5 ca_address_sk->[c_current_addr_sk]
+------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_sold_date_sk = d1.d_date_sk)) otherCondition=()
build RFs:RF7 d_date_sk->[ss_sold_date_sk]
+--------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_cdemo_sk = cd1.cd_demo_sk)) otherCondition=()
build RFs:RF6 cd_demo_sk->[ss_cdemo_sk]
+----------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF5 c_customer_sk->[ss_customer_sk]
+--------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
----------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_cdemo_sk = cd1.cd_demo_sk)) otherCondition=((
not (cd_marital_status = cd_marital_status))) build RFs:RF4
cd_demo_sk->[ss_cdemo_sk]
---------------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
-----------------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF3 c_customer_sk->[ss_customer_sk]
---------------------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
-----------------------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------------------PhysicalOlapScan[store_sales]
apply RFs: RF3 RF4 RF8 RF9 RF10 RF11 RF13 RF14 RF17 RF18 RF19
---------------------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
-----------------------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_current_cdemo_sk = cd2.cd_demo_sk))
otherCondition=() build RFs:RF2 cd_demo_sk->[c_current_cdemo_sk]
---------------------------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
-----------------------------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------------------------PhysicalOlapScan[customer]
apply RFs: RF2 RF5 RF6 RF7 RF12
---------------------------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
-----------------------------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------------------------PhysicalOlapScan[customer_demographics]
---------------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_item_sk = cs_ui.cs_item_sk)) otherCondition=()
build RFs:RF4 cs_item_sk->[ss_item_sk,sr_item_sk]
+--------------------------------------------------------------------PhysicalProject
+----------------------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_item_sk = store_returns.sr_item_sk) and
(store_sales.ss_ticket_number = store_returns.sr_ticket_number))
otherCondition=() build RFs:RF2 sr_item_sk->[ss_item_sk];RF3
sr_ticket_number->[ss_ticket_number]
+------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------PhysicalOlapScan[store_sales]
apply RFs: RF2 RF3 RF4 RF5 RF6 RF7 RF9 RF12 RF13 RF16 RF19
+------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------PhysicalOlapScan[store_returns]
apply RFs: RF4 RF19
+--------------------------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
----------------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------------PhysicalOlapScan[customer_demographics]
-----------------------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
-------------------------------------------------------------------PhysicalProject
---------------------------------------------------------------------PhysicalOlapScan[customer_address]
-------------------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
---------------------------------------------------------------PhysicalProject
-----------------------------------------------------------------PhysicalOlapScan[date_dim]
+------------------------------------------------------------------------filter((sale
> (2 * refund)))
+--------------------------------------------------------------------------hashAgg[GLOBAL]
+----------------------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------------------------------------------------hashAgg[LOCAL]
+--------------------------------------------------------------------------------PhysicalProject
+----------------------------------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((catalog_sales.cs_item_sk = catalog_returns.cr_item_sk) and
(catalog_sales.cs_order_number = catalog_returns.cr_order_number))
otherCondition=() build RFs:RF0 cr_order_number->[cs_order_number];RF1
cr_item_sk->[cs_item_sk]
+------------------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------------------PhysicalOlapScan[catalog_sales]
apply RFs: RF0 RF1 RF19
+------------------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------------------PhysicalOlapScan[catalog_returns]
+--------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+----------------------------------------------------------------PhysicalProject
+------------------------------------------------------------------PhysicalOlapScan[customer]
apply RFs: RF8 RF10 RF11 RF14 RF15
+----------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------PhysicalOlapScan[customer_demographics]
--------------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
----------------------------------------------------------PhysicalProject
-------------------------------------------------------------PhysicalOlapScan[date_dim]
+------------------------------------------------------------filter(d_year IN
(2001, 2002))
+--------------------------------------------------------------PhysicalOlapScan[date_dim]
--------------------------------------------------PhysicalDistribute[DistributionSpecHash]
----------------------------------------------------PhysicalProject
-------------------------------------------------------filter((item.i_current_price
<= 33.00) and (item.i_current_price >= 24.00) and i_color IN ('blanched',
'brown', 'burlywood', 'chocolate', 'drab', 'medium'))
---------------------------------------------------------PhysicalOlapScan[item]
apply RFs: RF9 RF19
-------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------------------------PhysicalOlapScan[customer_demographics]
+------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
--------------------------------------------------PhysicalProject
-----------------------------------------------------PhysicalOlapScan[store_returns]
apply RFs: RF19
---------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
-----------------------------------------------PhysicalProject
-------------------------------------------------PhysicalOlapScan[store]
-----------------------------------------PhysicalDistribute[DistributionSpecReplicated]
-------------------------------------------PhysicalProject
---------------------------------------------PhysicalOlapScan[household_demographics]
apply RFs: RF16
+----------------------------------------------------PhysicalOlapScan[customer_address]
+----------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
+------------------------------------------------PhysicalProject
+--------------------------------------------------PhysicalOlapScan[date_dim]
+------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
+--------------------------------------------PhysicalProject
+----------------------------------------------PhysicalOlapScan[date_dim]
+--------------------------------------PhysicalDistribute[DistributionSpecReplicated]
+----------------------------------------PhysicalProject
+------------------------------------------PhysicalOlapScan[household_demographics]
apply RFs: RF17
------------------------------------PhysicalDistribute[DistributionSpecReplicated]
--------------------------------------PhysicalProject
-----------------------------------------PhysicalOlapScan[customer_address]
+----------------------------------------PhysicalOlapScan[promotion]
--------------------------------PhysicalDistribute[DistributionSpecReplicated]
----------------------------------PhysicalProject
-------------------------------------PhysicalOlapScan[household_demographics]
apply RFs: RF15
-----------------------------PhysicalDistribute[DistributionSpecReplicated]
-------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[income_band]
-------------------------PhysicalDistribute[DistributionSpecReplicated]
---------------------------PhysicalProject
-----------------------------PhysicalOlapScan[income_band]
---------------------PhysicalDistribute[DistributionSpecReplicated]
-----------------------PhysicalProject
-------------------------filter(d_year IN (2001, 2002))
---------------------------PhysicalOlapScan[date_dim]
-----------------PhysicalDistribute[DistributionSpecReplicated]
-------------------PhysicalProject
---------------------PhysicalOlapScan[promotion]
-------------PhysicalProject
---------------filter((sale > (2 * refund)))
-----------------hashAgg[GLOBAL]
-------------------PhysicalDistribute[DistributionSpecHash]
---------------------hashAgg[LOCAL]
-----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN]
hashCondition=((catalog_sales.cs_item_sk = catalog_returns.cr_item_sk) and
(catalog_sales.cs_order_number = catalog_returns.cr_order_number))
otherCondition=() build RFs:RF0 cr_order_number->[cs_order_number];RF1
cr_item_sk->[cs_item_sk]
---------------------------PhysicalProject
-----------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF0 RF1
---------------------------PhysicalProject
-----------------------------PhysicalOlapScan[catalog_returns]
+------------------------------------PhysicalOlapScan[customer_address]
+------------------------------PhysicalDistribute[DistributionSpecReplicated]
+--------------------------------PhysicalProject
+----------------------------------PhysicalOlapScan[household_demographics]
apply RFs: RF18
+--------------------------PhysicalDistribute[DistributionSpecReplicated]
+----------------------------PhysicalProject
+------------------------------PhysicalOlapScan[store]
+----------------------PhysicalDistribute[DistributionSpecReplicated]
+------------------------PhysicalProject
+--------------------------PhysicalOlapScan[income_band]
+------------------PhysicalDistribute[DistributionSpecReplicated]
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[income_band]
+------------PhysicalDistribute[DistributionSpecHash]
+--------------PhysicalProject
+----------------filter((item.i_current_price <= 33.00) and
(item.i_current_price >= 24.00) and i_color IN ('blanched', 'brown',
'burlywood', 'chocolate', 'drab', 'medium'))
+------------------PhysicalOlapScan[item]
--PhysicalResultSink
----PhysicalQuickSort[MERGE_SORT]
------PhysicalDistribute[DistributionSpecGather]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
