[
https://issues.apache.org/jira/browse/TRAFODION-2813?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16261060#comment-16261060
]
ASF GitHub Bot commented on TRAFODION-2813:
-------------------------------------------
Github user DaveBirdsall commented on a diff in the pull request:
https://github.com/apache/incubator-trafodion/pull/1306#discussion_r152340197
--- Diff: core/sql/optimizer/IndexDesc.cpp ---
@@ -802,10 +802,30 @@ IndexProperty::compareIndexPromise(const
IndexProperty *ixProp) const
const IndexDesc * index = getIndexDesc();
const IndexDesc * otherIndex = ixProp->getIndexDesc();
- if ( ((IndexColumn
*)(index->getIndexKey()[0]).getItemExpr())->getDefinition() !=
- ((IndexColumn
*)(otherIndex->getIndexKey()[0]).getItemExpr())->getDefinition() )
-
- return INCOMPATIBLE;
+
+ // If the two indexes have differing leading columns, consider them
incompatible.
+ // For this check, we ignore the "_SALT_" column if both are salted.
+ CollIndex columnToCheck = 0;
+ NABoolean done = FALSE;
+ while (!done)
+ {
+ if (columnToCheck >= index->getIndexKey().entries())
+ return INCOMPATIBLE; // must be one of the indexes is just
"_SALT_" (seems unlikely actually)
+ else if (columnToCheck >= otherIndex->getIndexKey().entries())
+ return INCOMPATIBLE; // must be one of the indexes is just
"_SALT_" (seems unlikely actually)
+ else
+ {
+ IndexColumn * indexCol = (IndexColumn
*)(index->getIndexKey()[columnToCheck]).getItemExpr();
+ IndexColumn * otherIndexCol = (IndexColumn
*)(otherIndex->getIndexKey()[columnToCheck]).getItemExpr();
+ if ( indexCol->getNAColumn()->isSaltColumn() &&
+ otherIndexCol->getNAColumn()->isSaltColumn() )
--- End diff --
I considered that. I think the intent of the code in
IndexProperty::compareIndexPromise is, for each user column, to retain the
"best" (in some sense) index that begins with that column. The salt column is
not a user column in any sense; with the exception of pre-partitioning rows
across regions, a user would never have a predicate on salt. A divisioning
column is a fuzzy area. It is a system-generated column, but it reflects true
user content. A user predicate may well result in a predicate on the
divisioning column. So, I thought it was more in tune with the spirit of
IndexProperty::compareIndexPromise to treat divisioning columns like user
columns. But I do not feel strongly about it; a case could be made the other
way.
> 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)
