David Wayne Birdsall created TRAFODION-2813: -----------------------------------------------
Summary: 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)