[jira] [Work logged] (TRAFODION-3325) Inefficient plan when using a join to a tuple list

["ASF GitHub Bot (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/TRAFODION-3325?focusedWorklogId=309346&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-309346 ]

ASF GitHub Bot logged work on TRAFODION-3325:
---------------------------------------------

                Author: ASF GitHub Bot
            Created on: 09/Sep/19 23:05
            Start Date: 09/Sep/19 23:05
    Worklog Time Spent: 10m 
      Work Description: DaveBirdsall commented on pull request #1855: [TRAFODION-3325] Enable index access for non-VEG equality join predicates
URL: https://github.com/apache/trafodion/pull/1855
 
 
   The problem is that sometimes join equality predicates are not converted into VEGs, and therefore do not get pushed down to their children. That prevents Scan::addIndexInfo from considering indexes when there are column references in the join predicate. That can result in missed opportunities to use efficient index plans and instead doing full table scans + hash joins.
   
   The fix is to look for equality predicates that didn't get pushed down, and if we find one and it has a child that is covered, we create an IS NOT NULL predicate on that child and push that down. That allows Scan::addIndexInfo to notice that an index might be useful. Later, if the join is converted to a TSJ, the equality predicate will be pushed down and the Optimizer can make use of the index.
   
   An example of a join equality predicate that is not converted to a VEG is a join predicate involving a tuple list.
   
   I added a CQD, COMP_BOOL_194, which if set to 'OFF' turns off this fix. The default is 'ON'.
   
   When unit testing this fix, I noticed a bug in some of the optimizer debugging code that caused a core. I fixed that too (optimizer/opt.cpp).
 
----------------------------------------------------------------
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:
users@infra.apache.org


Issue Time Tracking
-------------------

            Worklog Id:     (was: 309346)
    Remaining Estimate: 0h
            Time Spent: 10m

> 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: 10m
>  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)