[
https://issues.apache.org/jira/browse/TRAFODION-2661?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16072914#comment-16072914
]
David Wayne Birdsall commented on TRAFODION-2661:
-------------------------------------------------
While doing further due diligence on this issue, I encountered another bug. In
this bug, we have a table with several key columns. We have a query with key
predicates on the first two columns (ignoring "_SALT_"), and also a key
predicate on the last key column. An MDAM plan on the first two columns would
be a good choice (and in fact the Optimizer chooses this), but the plan that
gets generated traverses to the last key column (resulting in terrible
performance). The bug is that on a partitioned table, at pre-code-gen time, we
would bypass the code that passes "stop column" information to the generator.
The script below reproduces the problem:
?section setup
drop table if exists mdamex;
create table mdamex
(a int not null,
b int not null,
c int not null,
d int not null,
e int not null,
f int not null,
g int,
primary key (a, b, c, d, e, f))
salt using 4 partitions;
upsert using load into mdamex
select c0, c1, c2, c3, c4, c5, c0 + 3*c1 + 7*c2 + 11*c3 + 17*c4 + 29*c5
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,10,11 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,10,11,12,13,14,15 as c4
transpose 0,1,2,3,4,5,6,7,8,9 as c5;
update statistics for table mdamex on every column;
?section query
prepare xx from
select * from mdamex
where a in (3,5) and b > 3 and f in (4,6,8);
explain xx;
showshape
select * from mdamex
where a in (3,5) and b > 3 and f in (4,6,8);
prepare yy from
select * from mdamex
where a in (3,5) and b > 3;
explain yy;
showshape
select * from mdamex
where a in (3,5) and b > 3;
In the Explain for query xx, notice the MDAM disjunct contains the predicate on
F. This reflects the generated plan. The showshape output, however, happens to
show what the Optimizer chose; that is, MDAM only on the first three columns
("_SALT_", A and B). In the Explain for query yy, the explain output correctly
shows an MDAM plan on just the first three columns.
> MDAM not chosen for OR predicate of leading index column
> --------------------------------------------------------
>
> Key: TRAFODION-2661
> URL: https://issues.apache.org/jira/browse/TRAFODION-2661
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
> Affects Versions: 2.0-incubating
> Reporter: Hans Zeller
> Assignee: David Wayne Birdsall
>
> We saw the following issue in a customer scenario:
> SELECT SUM(c)
> from tbl2
> where a in ('A','B')
> AND b >= '8';
> The table has a salted index on column a. That index has many more columns
> (13 in this case, including the salt column).
> The plan we would expect is an MDAM plan with no predicate on _SALT_ and the
> OR predicate on column a.
> However, we get a full index scan (we can use an index-only scan in this
> case).
> When we force an MDAM plan, we see a vastly higher cost (250,000 in our
> example) than for the single subset plan (cost of 9 in our example).
> Dave has already mentioned that this has to do with RangeSpecs. The following
> workaround gives the MDAM plan for us:
> cqd RANGESPEC_TRANSFORMATION 'off';
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
