You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafodion.apache.org by db...@apache.org on 2018/04/24 15:07:32 UTC
[1/4] trafodion git commit: [TRAFODION-3031] Fix two issues with
nested subquery plans
Repository: trafodion
Updated Branches:
refs/heads/master b06e0dc5d -> 3e7ad94f7
[TRAFODION-3031] Fix two issues with nested subquery plans
Project: http://git-wip-us.apache.org/repos/asf/trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/trafodion/commit/ee91b337
Tree: http://git-wip-us.apache.org/repos/asf/trafodion/tree/ee91b337
Diff: http://git-wip-us.apache.org/repos/asf/trafodion/diff/ee91b337
Branch: refs/heads/master
Commit: ee91b337ae1d23ad2b15034602e06c4d3bf1103d
Parents: 1e100f4
Author: Dave Birdsall <db...@apache.org>
Authored: Thu Apr 19 22:59:16 2018 +0000
Committer: Dave Birdsall <db...@apache.org>
Committed: Thu Apr 19 22:59:16 2018 +0000
----------------------------------------------------------------------
core/sql/optimizer/NormRelExpr.cpp | 42 ++++++++++++++++++++++++--------
core/sql/optimizer/RelExpr.cpp | 6 +++--
core/sql/sqlcomp/DefaultConstants.h | 3 +++
core/sql/sqlcomp/nadefaults.cpp | 6 +++++
4 files changed, 45 insertions(+), 12 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/trafodion/blob/ee91b337/core/sql/optimizer/NormRelExpr.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/NormRelExpr.cpp b/core/sql/optimizer/NormRelExpr.cpp
index f574b17..89504a2 100644
--- a/core/sql/optimizer/NormRelExpr.cpp
+++ b/core/sql/optimizer/NormRelExpr.cpp
@@ -2767,25 +2767,26 @@ Here t2.a is a unique key of table t2.
The following transformation is made
Semi Join {pred : t1.b = t2.a} Join {pred : t1.b = t2.a}
/ \ -------> / \
- / \ / \
-Scan t1 Scan t2 Scan t1 Scan t2
+ / \ / \
+ Scan t1 Scan t2 Scan t1 Scan t2
b) If the right child is not unique in the joining column then
we transform the semijoin into an inner join followed by a groupby
as the join's right child. This transformation is enabled by default
-only if the right side is an IN list, otherwise a CQD has to be used.
+only if the right side is an IN list or if the groupby's reduction
+ratio is greater than 5.0, otherwise a CQD has to be used.
select t1.a
from t1
where t1.b in (1,2,3,4,...,101) ;
- Semi Join {pred : t1.b = t2.a} Join {pred : t1.b = InList.col}
+ Semi Join {pred : t1.b = InList.col} Join {pred : t1.b = InList.col}
/ \ -------> / \
/ \ / \
-Scan t1 Scan t2 Scan t1 GroupBy {group cols: InList.col}
+Scan t1 TupleList Scan t1 GroupBy {group cols: InList.col}
|
|
TupleList
@@ -2826,18 +2827,39 @@ RelExpr* Join::transformSemiJoin(NormWA& normWARef)
/* Apply the transformation described in item b) above.
The transformation below is done if there are no non-equijoin preds either
- and the inner side has no base tables (i.e. is an IN LIST) or if we have
- used a CQD to turn this transformation on for a specific user. For the general
- case we are not certain if this transformation is always beneficial, so it is
- not on by default */
+ and the inner side has no base tables (i.e. is an IN LIST) OR if the groupby
+ is expected to provide a reduction > SEMIJOIN_TO_INNERJOIN_REDUCTION_RATIO
+ (default is 5.0) OR the inner row count is small OR if we have used a CQD to
+ turn this transformation on. Some rationale: A data reduction might reduce
+ the amount of data for the inner table of a hash join (or it might not!
+ hash-semi-join sometimes does duplicate elimination itself, but not always).
+ Converting to a join allows the join to be commuted; if the number of rows
+ is small, nested join might be profitably chosen in that case. */
ValueIdSet preds ;
preds += joinPred();
preds += selectionPred();
preds -= getEquiJoinPredicates() ;
+ EstLogPropSharedPtr innerEstLogProp = child(1)->getGroupAttr()->outputLogProp((*GLOBAL_EMPTY_INPUT_LOGPROP));
+ CostScalar innerRowCount = innerEstLogProp->getResultCardinality();
+ CostScalar innerUec = innerEstLogProp->getAggregateUec(equiJoinCols1);
+ NABoolean haveSignificantReduction = FALSE;
+ CostScalar reductionThreshold =
+ ((ActiveSchemaDB()->getDefaults()).getAsDouble(SEMIJOIN_TO_INNERJOIN_REDUCTION_RATIO));
+ NABoolean noInnerStats = innerEstLogProp->getColStats().containsAtLeastOneFake();
+ // have a valid value of uec, have something other than default
+ // cardinality and satisfy reduction requirement.
+ if ((innerUec > 0) && (!noInnerStats) &&
+ (innerRowCount/innerUec > reductionThreshold))
+ haveSignificantReduction = TRUE;
+ CostScalar innerAllowance =
+ ((ActiveSchemaDB()->getDefaults()).getAsDouble(SEMIJOIN_TO_INNERJOIN_INNER_ALLOWANCE));
+
if (preds.isEmpty() &&
- ((child(1)->getGroupAttr()->getNumBaseTables() == 0) ||
+ ((child(1)->getGroupAttr()->getNumBaseTables() == 0) ||
+ haveSignificantReduction ||
+ (innerRowCount < innerAllowance) ||
(CmpCommon::getDefault(SEMIJOIN_TO_INNERJOIN_TRANSFORMATION) == DF_ON)))
{
CollHeap *stmtHeap = CmpCommon::statementHeap() ;
http://git-wip-us.apache.org/repos/asf/trafodion/blob/ee91b337/core/sql/optimizer/RelExpr.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/RelExpr.cpp b/core/sql/optimizer/RelExpr.cpp
index 5096b8b..38f3d12 100644
--- a/core/sql/optimizer/RelExpr.cpp
+++ b/core/sql/optimizer/RelExpr.cpp
@@ -8792,10 +8792,12 @@ void Scan::addIndexInfo()
for (CollIndex i = 0; i < possibleIndexJoins_.entries(); i++)
{
NABoolean isASupersetIndex =
- possibleIndexJoins_[i]->outputsFromIndex_.contains(newOutputsFromIndex);
+ possibleIndexJoins_[i]->outputsFromIndex_.contains(newOutputsFromIndex) &&
+ possibleIndexJoins_[i]->indexPredicates_.contains(newIndexPredicates);
NABoolean isASubsetIndex =
- newOutputsFromIndex.contains(possibleIndexJoins_[i]->outputsFromIndex_) ;
+ newOutputsFromIndex.contains(possibleIndexJoins_[i]->outputsFromIndex_) &&
+ newIndexPredicates.contains(possibleIndexJoins_[i]->indexPredicates_);
NABoolean isASuperOrSubsetIndex = isASupersetIndex || isASubsetIndex;
http://git-wip-us.apache.org/repos/asf/trafodion/blob/ee91b337/core/sql/sqlcomp/DefaultConstants.h
----------------------------------------------------------------------
diff --git a/core/sql/sqlcomp/DefaultConstants.h b/core/sql/sqlcomp/DefaultConstants.h
index fd110de..2d31275 100644
--- a/core/sql/sqlcomp/DefaultConstants.h
+++ b/core/sql/sqlcomp/DefaultConstants.h
@@ -1965,7 +1965,10 @@ enum DefaultConstants
USTAT_FETCHCOUNT_ACTIVE,
+ SEMIJOIN_TO_INNERJOIN_INNER_ALLOWANCE,
+ SEMIJOIN_TO_INNERJOIN_REDUCTION_RATIO,
SEMIJOIN_TO_INNERJOIN_TRANSFORMATION,
+
POS_NUM_DISK_POOLS,
POS_DISKS_IN_SEGMENT,
http://git-wip-us.apache.org/repos/asf/trafodion/blob/ee91b337/core/sql/sqlcomp/nadefaults.cpp
----------------------------------------------------------------------
diff --git a/core/sql/sqlcomp/nadefaults.cpp b/core/sql/sqlcomp/nadefaults.cpp
index 244a2bc..a93cdb7 100644
--- a/core/sql/sqlcomp/nadefaults.cpp
+++ b/core/sql/sqlcomp/nadefaults.cpp
@@ -2702,6 +2702,12 @@ SDDflt0_(QUERY_CACHE_SELECTIVITY_TOLERANCE, "0"),
// SQ_SEAMONSTER which will have a value of 0 or 1.
DDkwd__(SEAMONSTER, "SYSTEM"),
+ // If the inner table of a semi-join has fewer rows than this,
+ // we'll allow it to be transformed to a join.
+ DDflt1_(SEMIJOIN_TO_INNERJOIN_INNER_ALLOWANCE, "100.0"),
+ // Ratio of right child cardinality to uec above which semijoin
+ // trans. is favored.
+ DDflt1_(SEMIJOIN_TO_INNERJOIN_REDUCTION_RATIO, "5.0"),
SDDkwd__(SEMIJOIN_TO_INNERJOIN_TRANSFORMATION, "SYSTEM"),
// Disallow/Allow semi and anti-semi joins in MultiJoin framework
DDkwd__(SEMI_JOINS_SPOIL_JBB, "OFF"),
[3/4] trafodion git commit: Change comments per Hans' suggtestion
Posted by db...@apache.org.
Change comments per Hans' suggtestion
Project: http://git-wip-us.apache.org/repos/asf/trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/trafodion/commit/a4d55224
Tree: http://git-wip-us.apache.org/repos/asf/trafodion/tree/a4d55224
Diff: http://git-wip-us.apache.org/repos/asf/trafodion/diff/a4d55224
Branch: refs/heads/master
Commit: a4d552244fc36f81530f4e083fd64cd6634f5e4d
Parents: 64d1e33
Author: Dave Birdsall <db...@apache.org>
Authored: Mon Apr 23 22:09:56 2018 +0000
Committer: Dave Birdsall <db...@apache.org>
Committed: Mon Apr 23 22:09:56 2018 +0000
----------------------------------------------------------------------
core/sql/optimizer/NormRelExpr.cpp | 15 +++++++++++++++
1 file changed, 15 insertions(+)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/trafodion/blob/a4d55224/core/sql/optimizer/NormRelExpr.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/NormRelExpr.cpp b/core/sql/optimizer/NormRelExpr.cpp
index 115b406..6d1ef4c 100644
--- a/core/sql/optimizer/NormRelExpr.cpp
+++ b/core/sql/optimizer/NormRelExpr.cpp
@@ -2778,6 +2778,8 @@ as the join's right child. This transformation is enabled by default
only if the right side is an IN list or if the groupby's reduction
ratio is greater than 5.0, otherwise a CQD has to be used.
+Examples:
+
select t1.a
from t1
where t1.b in (1,2,3,4,...,101) ;
@@ -2791,6 +2793,19 @@ Scan t1 TupleList Scan t1 GroupBy {group cols: InList.col}
|
TupleList
+select t1.a
+from t1
+where t1.b in (select t2.c from t2 where whatever) ;
+
+
+ Semi Join {pred : t1.b = t2.c } Join {pred : t1.b = t2.c}
+ / \ -------> / \
+/ \ / \
+Scan t1 Scan t2 Scan t1 GroupBy {group cols: t2.c}
+ |
+ |
+ Scan t2
+
*/
RelExpr* Join::transformSemiJoin(NormWA& normWARef)
[4/4] trafodion git commit: Merge [TRAFODION-3031] PR 1530 Fix two
issues with nested subquery plans
Posted by db...@apache.org.
Merge [TRAFODION-3031] PR 1530 Fix two issues with nested subquery plans
Project: http://git-wip-us.apache.org/repos/asf/trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/trafodion/commit/3e7ad94f
Tree: http://git-wip-us.apache.org/repos/asf/trafodion/tree/3e7ad94f
Diff: http://git-wip-us.apache.org/repos/asf/trafodion/diff/3e7ad94f
Branch: refs/heads/master
Commit: 3e7ad94f726cd4797e662ebf47bb1701d4f57e13
Parents: b06e0dc a4d5522
Author: Dave Birdsall <db...@apache.org>
Authored: Tue Apr 24 15:06:19 2018 +0000
Committer: Dave Birdsall <db...@apache.org>
Committed: Tue Apr 24 15:06:19 2018 +0000
----------------------------------------------------------------------
core/sql/optimizer/NormRelExpr.cpp | 60 ++++++++++++++++++++----
core/sql/optimizer/RelExpr.cpp | 6 ++-
core/sql/regress/compGeneral/EXPECTED015.SB | 18 +++----
core/sql/sqlcomp/DefaultConstants.h | 3 ++
core/sql/sqlcomp/nadefaults.cpp | 6 +++
5 files changed, 72 insertions(+), 21 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/trafodion/blob/3e7ad94f/core/sql/optimizer/RelExpr.cpp
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/trafodion/blob/3e7ad94f/core/sql/sqlcomp/nadefaults.cpp
----------------------------------------------------------------------
[2/4] trafodion git commit: Reworks
Posted by db...@apache.org.
Reworks
Project: http://git-wip-us.apache.org/repos/asf/trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/trafodion/commit/64d1e330
Tree: http://git-wip-us.apache.org/repos/asf/trafodion/tree/64d1e330
Diff: http://git-wip-us.apache.org/repos/asf/trafodion/diff/64d1e330
Branch: refs/heads/master
Commit: 64d1e33063bd2957b5c71b47c2608a7ac2852c9f
Parents: ee91b33
Author: Dave Birdsall <db...@apache.org>
Authored: Mon Apr 23 21:51:24 2018 +0000
Committer: Dave Birdsall <db...@apache.org>
Committed: Mon Apr 23 21:51:24 2018 +0000
----------------------------------------------------------------------
core/sql/optimizer/NormRelExpr.cpp | 5 ++++-
core/sql/regress/compGeneral/EXPECTED015.SB | 18 +++++++++---------
2 files changed, 13 insertions(+), 10 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/trafodion/blob/64d1e330/core/sql/optimizer/NormRelExpr.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/NormRelExpr.cpp b/core/sql/optimizer/NormRelExpr.cpp
index 89504a2..115b406 100644
--- a/core/sql/optimizer/NormRelExpr.cpp
+++ b/core/sql/optimizer/NormRelExpr.cpp
@@ -2855,11 +2855,14 @@ RelExpr* Join::transformSemiJoin(NormWA& normWARef)
haveSignificantReduction = TRUE;
CostScalar innerAllowance =
((ActiveSchemaDB()->getDefaults()).getAsDouble(SEMIJOIN_TO_INNERJOIN_INNER_ALLOWANCE));
+ NABoolean haveSmallInner = FALSE;
+ if ((innerRowCount < innerAllowance) && (!noInnerStats))
+ haveSmallInner = TRUE;
if (preds.isEmpty() &&
((child(1)->getGroupAttr()->getNumBaseTables() == 0) ||
haveSignificantReduction ||
- (innerRowCount < innerAllowance) ||
+ haveSmallInner ||
(CmpCommon::getDefault(SEMIJOIN_TO_INNERJOIN_TRANSFORMATION) == DF_ON)))
{
CollHeap *stmtHeap = CmpCommon::statementHeap() ;
http://git-wip-us.apache.org/repos/asf/trafodion/blob/64d1e330/core/sql/regress/compGeneral/EXPECTED015.SB
----------------------------------------------------------------------
diff --git a/core/sql/regress/compGeneral/EXPECTED015.SB b/core/sql/regress/compGeneral/EXPECTED015.SB
index b5e11d4..c1a3076 100755
--- a/core/sql/regress/compGeneral/EXPECTED015.SB
+++ b/core/sql/regress/compGeneral/EXPECTED015.SB
@@ -535,8 +535,8 @@ TRAFODION_SCAN T10 1.000E+005 100000 fragm
OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY
---------------- ---------- ----------- ---------------
-ROOT 1.250E+004 100000 fragm
-TRAFODION_SCAN T10 1.250E+004 100000 fragm
+ROOT 1.000E+004 10000 fragme
+TRAFODION_SCAN T10 1.000E+004 10000 fragme
--- 2 row(s) selected.
>>select count(*) from (SELECT * FROM t10 WHERE d like 'one%') as t;
@@ -557,8 +557,8 @@ TRAFODION_SCAN T10 1.250E+004 100000 fragm
OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY
---------------- ---------- ----------- ---------------
-ROOT 8.750E+004 100000 fragm
-TRAFODION_SCAN T10 8.750E+004 100000 fragm
+ROOT 1.000E+005 100000 fragm
+TRAFODION_SCAN T10 1.000E+005 100000 fragm
--- 2 row(s) selected.
>>select count(*) from (SELECT * FROM t10 WHERE d not like 'one%') as t;
@@ -1174,8 +1174,8 @@ TRAFODION_SCAN T10 1.000E+000 1 fragment_i
OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY
---------------- ---------- ----------- ---------------
-ROOT 1.250E+004 12500 fragme
-TRAFODION_SCAN T10 1.250E+004 12500 fragme
+ROOT 1.000E+004 10000 fragme
+TRAFODION_SCAN T10 1.000E+004 10000 fragme
--- 2 row(s) selected.
>> -- min(1e5, 1e4) should = 1e4
@@ -1202,8 +1202,8 @@ TRAFODION_SCAN T10 1.000E+000 1 fragment_i
OPERATOR TAB_NAME CARDINALITY MAX_CARDINALITY
---------------- ---------- ----------- ---------------
-ROOT 1.000E+000 10000 fragme
-TRAFODION_SCAN T10 1.000E+000 10000 fragme
+ROOT 1.000E+000 100000 fragm
+TRAFODION_SCAN T10 1.000E+000 100000 fragm
--- 2 row(s) selected.
>> -- min(1e4, 1e5) should = 1e4
@@ -2297,7 +2297,7 @@ OPERATOR
----------------
ROOT
-HYBRID_HASH_SEMI
+HYBRID_HASH_JOIN
--- 2 row(s) selected.
>>