[
https://issues.apache.org/jira/browse/TRAFODION-2661?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16063857#comment-16063857
]
ASF GitHub Bot commented on TRAFODION-2661:
-------------------------------------------
GitHub user DaveBirdsall opened a pull request:
https://github.com/apache/incubator-trafodion/pull/1146
[TRAFODION-2661] Improve MDAM costing for RangeSpecs
JIRA TRAFODION-1641 fixed an issue with MDAM costing, where MDAM costing
was not accounting for the cost of recursion. That is, it was not accounting
for the accumulated costs of probes as we recurse through a set of key columns.
When analyzing that JIRA, I uncovered an issue with RangeSpec costing which I
partially fixed.
JIRA TRAFODION-2655 tuned the above fix further, increasing the weight of
the cost of accumulated probes. So for plans lacking RangeSpecs, or plans whose
RangeSpecs lack ORs, MDAM costing should now be doing a much better job of
picking prefixes when it should.
This fix addresses the last bit of the problem by addressing RangeSpecs.
There was some code that was forcing MDAM plans to traverse all key columns
when a RangeSpec containing an OR was present. I have simply deleted that code.
I do not know why that code was originally there. It does predate the fixes
noted above. I will speculate that before that change, we were picking MDAM in
some cases where we should not, and adding that bit of code increased the total
cost of MDAM to such a point that we no longer did.
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/DaveBirdsall/incubator-trafodion Trafodion2661
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/incubator-trafodion/pull/1146.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #1146
----
----
> 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)
