[jira] [Commented] (TRAFODION-2813) Salting + heuristics prevent MDAM plan on base table from being considered

["David Wayne Birdsall (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/TRAFODION-2813?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16259908#comment-16259908 ] 

David Wayne Birdsall commented on TRAFODION-2813:
-------------------------------------------------

I've debugged this. Here are my findings:

There is a layer of heuristics that tries to reduce the number of indexes that the Optimizer will consider during its costing phases. I will guess these heuristics were added in Neoview days when compilation time was a concern. The layer begins at Scan::addIndexInfo (optimizer/RelExpr.cpp). It analyzes the available indexes for such things as whether they cover all the required columns (so we can do index-only access), whether the index satisfies a required order, and so on. When INDEX_ELIMINATION_LEVEL is AGGRESSIVE, it also tries to prune out indexes that begin with the same column, keeping only the one with the smallest I/O. The latter logic is in IndexProperty::updatePossibleIndexes (optimizer/IndexDesc.cpp) and IndexProperty::compareIndexPromise (optimizer/IndexDesc.cpp). The latter routine has a very curious check:

    // currently it is the same for indexOnlyScans ans alternateIndexScans.
    // If index key starts from the same (single column) then the smaller index 
    // (having smaller KbPerVolume attribute) is MORE promising, the bigger index
    // is LESS promising, and the same index size has the SAME promise.


    const IndexDesc * index = getIndexDesc();
    const IndexDesc * otherIndex = ixProp->getIndexDesc();
    if ( ((IndexColumn *)(index->getIndexKey()[0]).getItemExpr())->getDefinition() != 
         ((IndexColumn *)(otherIndex->getIndexKey()[0]).getItemExpr())->getDefinition() )

      return INCOMPATIBLE;

The goal of this code appears to be to keep around a representative of each class of indexes that begin on a particular column. So, for example, if the base table had primary key A, B, C, and we had an index B, C, A, this logic would keep both. Though if there were two indexes, B, A, C and B, C, A, this code would only keep one of them.

That code was written before we had salting and divisioning, however. In the case of Q3, both the base table and the index begin with "_SALT_", so this "if" was not taken. Logic after this attempts to compute how much I/O would be done through each index, and gets it wrong, and we keep the wrong index.

So, I think this code should be changed so we skip any matching leading salt or divisioning columns before doing the comparison. That will get us back to the original intent of this code. But this is a tentative conclusion; this particular code is new to me so there may be a better solution.

As a result of this analysis, I found a work-around: By setting CQD INDEX_ELIMINATION_LEVEL to 'MAXIMUM', the pruning heuristic is bypassed, the costing code considers both base table and index paths, and correctly picks an efficient MDAM plan on the base table. (The default for that CQD is 'AGGRESSIVE'.)

> Salting + heuristics prevent MDAM plan on base table from being considered
> --------------------------------------------------------------------------
>
>                 Key: TRAFODION-2813
>                 URL: https://issues.apache.org/jira/browse/TRAFODION-2813
>             Project: Apache Trafodion
>          Issue Type: Bug
>          Components: sql-cmp
>    Affects Versions: 2.3-incubating
>         Environment: All
>            Reporter: David Wayne Birdsall
>            Assignee: David Wayne Birdsall
>
> When a salted table also has salted indexes, it may happen that a query that has a good MDAM plan on the base table gets a full scan on a salted index instead.
> The problem is that there is a defective heuristic that rules out base table access before we get to the costing code. So the costing code only sees one access path, namely the index, and (correctly) picks a full scan on it.
> The script below reproduces the problem:
> ?section setup
> drop table if exists trafodion.seabase.t1 cascade;
> CREATE TABLE TRAFODION.SEABASE.T1
>   ( 
>     key1                       NUMERIC(9, 0) NO DEFAULT NOT NULL NOT
>       DROPPABLE NOT SERIALIZED
>   , key2                      VARCHAR(64) CHARACTER SET ISO88591 COLLATE
>       DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
>   , key3                       TIMESTAMP(6) NO DEFAULT NOT NULL NOT
>       DROPPABLE NOT SERIALIZED
>   , nonkey                       VARCHAR(1024) CHARACTER SET ISO88591
>       COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
>   , PRIMARY KEY (key1 ASC, key2 ASC, key3 ASC)
>   )
>   SALT USING 4 PARTITIONS
>        ON (key2)
>   DIVISION BY (DATE_TRUNC('HOUR',key3)
>      NAMED AS ("_DIVISION_1_"))
>  ATTRIBUTES ALIGNED FORMAT 
>   HBASE_OPTIONS 
>   ( 
>     DATA_BLOCK_ENCODING = 'FAST_DIFF',
>     MEMSTORE_FLUSH_SIZE = '1073741824' 
>   ) 
> ;
> upsert using load into TRAFODION.SEABASE.T1
> select 50000 + c0,
>  'user ' || cast(c5 + 10*c0 + 100*c1 + 1000*c2 + 10000*c3 as varchar(20)),
>  timestamp '2017-07-01 12:00:00.000000' + cast(c1 + 10*c2 + 100*c3 + 1000*c4 + 1000*c5 as interval minute(6)),
>  'valid prod ' || cast(7*c3 + 4*c4 + 11*c2 as varchar(20))
> from (values(1)) t
> transpose 0,1,2,3,4,5,6,7,8,9 as c0
> transpose 0,1,2,3,4,5,6,7,8,9 as c1
> transpose 0,1,2,3,4,5,6,7,8,9 as c2
> transpose 0,1,2,3,4,5,6,7,8,9 as c3
> transpose 0,1,2,3,4,5,6,7,8,9 as c4
> transpose 0,1,2,3,4,5,6,7,8,9 as c5;
> update statistics for table TRAFODION.SEABASE.T1 on every column;
> CREATE INDEX T1_IDX1 ON TRAFODION.SEABASE.T1
>   ( 
>     key2 ASC
>   , key3 ASC
>   )
>  ATTRIBUTES ALIGNED FORMAT 
>   HBASE_OPTIONS 
>   ( 
>     DATA_BLOCK_ENCODING = 'FAST_DIFF',
>     MEMSTORE_FLUSH_SIZE = '1073741824' 
>   ) 
>  SALT LIKE TABLE 
> ;
> ?section queries
> prepare s1 from
> SELECT key3, key2 FROM trafodion.seabase.t1 WHERE key1 = 50001 AND key3 
> >= to_date(cast (20170705080000 as char(20)),'YYYYMMDDHH24MISS') AND key3 
> <= to_date(cast (20170705085959 as char(20)),'YYYYMMDDHH24MISS') ;
> explain s1;
> In this example, S1 gets full scans on the index, even though an MDAM plan on the base table would be far more efficient.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)