[
https://issues.apache.org/jira/browse/TRAFODION-3325?focusedWorklogId=312140&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-312140
]
ASF GitHub Bot logged work on TRAFODION-3325:
---------------------------------------------
Author: ASF GitHub Bot
Created on: 13/Sep/19 15:44
Start Date: 13/Sep/19 15:44
Worklog Time Spent: 10m
Work Description: nonstop-qfchen commented on pull request #1856:
[TRAFODION-3325] Pass down index hints for non-VEG equality predicates
URL: https://github.com/apache/trafodion/pull/1856#discussion_r324241186
##########
File path: core/sql/optimizer/RelExpr.cpp
##########
@@ -1434,6 +1449,44 @@ void RelExpr::pushdownCoveredExpr(const ValueIdSet &
outputExpr,
computeValuesReqdForPredicates(predicatesOnParent,
exprToEvalOnParent);
+ // -----------------------------------------------------------------
+ // Check for equality predicates that could not be pushed down.
+ // This may happen if we have an equality predicate that was not
+ // transformed into a VEG predicate.
+ //
+ // If there are column references in the equality predicate, it
+ // may prove useful to tell the leaf nodes about it, as the equality
+ // predicate might be pushed down later during a Join to TSJ
+ // transformation. Telling the leaf nodes about it might allow the
+ // use of an index in this case (see Scan::addIndexInfo; this is
+ // called once at the beginning of optimization, before any Join
+ // to TSJ transformations have been attempted).
+ // -----------------------------------------------------------------
+ ValueId pred;
+ for (pred = predicatesOnParent.init();
+ predicatesOnParent.next(pred);
+ predicatesOnParent.advance(pred))
+ {
+ ItemExpr * ie = pred.getItemExpr();
+ if (ie->getOperatorType() == ITM_EQUAL) // non-VEG, equality
predicate
+ {
+ for (iter = firstChild; iter < lastChild; iter++)
+ {
+ for (CollIndex i = 0; i < ie->getArity(); i++)
+ {
+ // If the child is covered, it might contain a column
reference
+ // that is useful for index selection. Push that down.
+ ItemExpr * ieChildi = ie->child(i);
+ if ((CmpCommon::getDefault(COMP_BOOL_194) == DF_ON) &&
+
(coveredSubExprNotUsed[iter].contains(ieChildi->getValueId())))
Review comment:
It may be more efficient to move the check on CB_194 out of the topmost FOR
loop.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
[email protected]
Issue Time Tracking
-------------------
Worklog Id: (was: 312140)
Time Spent: 2h (was: 1h 50m)
> Inefficient plan when using a join to a tuple list
> --------------------------------------------------
>
> Key: TRAFODION-3325
> URL: https://issues.apache.org/jira/browse/TRAFODION-3325
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
> Reporter: David Wayne Birdsall
> Assignee: David Wayne Birdsall
> Priority: Major
> Attachments: repro.sql.txt
>
> Time Spent: 2h
> Remaining Estimate: 0h
>
> In the example below, statement s1 gets a hash join plan that does a full
> scan on table T1, even though a far more efficient nested join plan using
> index IT1 is possible. The problem is that the Optimizer is not considering
> an index scan using IT1 when the join predicates are between T1 and a tuple
> list.
> In contrasting examples below where we use an IN list instead of a join to a
> tuple list, and where we join to a table T2 instead of a tuple list, we do
> get efficient index access plans.
> To reproduce, execute the attached script to create the test tables and
> compile the test queries.
> When executed, we see the following plans:
> {quote}>>obey repro.sql(testit);
> >>
> >>prepare s1 from select t1.* from t1 join (values (3),(4)) f(b) on t1.b =
> >>f.b;
> --- SQL command prepared.
> >>explain options 'f' s1;
> LC RC OP OPERATOR OPT DESCRIPTION CARD
> ---- ---- ---- -------------------- -------- -------------------- ---------
> 5 . 6 root 4.00E+002
> 4 . 5 esp_exchange 1:2(hash2) 4.00E+002
> 3 2 4 hybrid_hash_join 4.00E+002
> . . 3 trafodion_scan T1 2.00E+006
> 1 . 2 esp_exchange 2(rep-b):1 2.00E+000
> . . 1 tuplelist 2.00E+000
> --- SQL operation complete.
> >>showshape select * from t1 join (values (3),(4)) f(b) on t1.b = f.b;
> control query shape esp_exchange(hybrid_hash_join(
> scan(path 'TRAFODION.SCH.T1', forward, blocks_per_access 489 , mdam off),
> esp_exchange(anything)));
> --- SQL operation complete.
> >>
> >>prepare s2 from select t1.* from t1 where b in (3,4);
> --- SQL command prepared.
> >>explain options 'f' s2;
> LC RC OP OPERATOR OPT DESCRIPTION CARD
> ---- ---- ---- -------------------- -------- -------------------- ---------
> 3 . 4 root 4.00E+002
> 1 2 3 nested_join 4.00E+002
> . . 2 trafodion_vsbb_scan T1 1.00E+000
> . . 1 trafodion_index_scan IT1 4.00E+002
> --- SQL operation complete.
> >>showshape select * from t1 where b in (3,4);
> control query shape nested_join(scan(path 'TRAFODION.SCH.IT1', forward
> , blocks_per_access 1 , mdam forced, mdam_columns (dense, sparse)),
> scan(path 'TRAFODION.SCH.T1', forward, blocks_per_access 400 , mdam off)
> ,INDEXJOIN);
> --- SQL operation complete.
> >>
> >>prepare s3 from select t1.* from t1 join t2 on t1.b = t2.b where t2.b in
> >>(3,4);
> --- SQL command prepared.
> >>explain options 'f' s3;
> LC RC OP OPERATOR OPT DESCRIPTION CARD
> ---- ---- ---- -------------------- -------- -------------------- ---------
> 5 . 6 root 4.00E+002
> 1 4 5 nested_join 4.00E+002
> 2 3 4 nested_join 2.00E+002
> . . 3 trafodion_vsbb_scan T1 1.00E+000
> . . 2 trafodion_index_scan IT1 2.00E+002
> . . 1 trafodion_scan T2 2.00E+000
> --- SQL operation complete.
> >>showshape select t1.* from t1 join t2 on t1.b = t2.b where t2.b in (3,4);
> control query shape nested_join(scan(path 'TRAFODION.SCH.T2', forward
> , blocks_per_access 2 , mdam forced, mdam_columns all(dense)),nested_join(
> scan(path 'TRAFODION.SCH.IT1', forward, blocks_per_access 1 , mdam off),
> scan(path 'TRAFODION.SCH.T1', forward, blocks_per_access 400 , mdam off)
> ,INDEXJOIN));
> --- SQL operation complete.
> >>
> >>exit;
> End of MXCI Session
> {quote}
>
--
This message was sent by Atlassian Jira
(v8.3.2#803003)
