Major Description
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.
Attachments
Issue Links
- links to
