[
https://issues.apache.org/jira/browse/TRAFODION-2913?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16329073#comment-16329073
]
ASF GitHub Bot commented on TRAFODION-2913:
-------------------------------------------
GitHub user DaveBirdsall opened a pull request:
https://github.com/apache/trafodion/pull/1403
[TRAFODION-2913] Tweak some MDAM-related heuristics
There are two changes in this pull request.
1. The default value for CQD MDAM_SELECTION_DEFAULT has been changed from
0.5 to 8.0. This is a recalibration of this default; it had not been changed
since predecessor product days. The old value resulted in MDAM not being
considered at times when it is the better plan. Details of how this
recalibration was done will be documented in the JIRA.
2. The heuristic implemented by IndexDesc::pruneMdam is now limited to
index elimination logic and no longer used by the scan optimizer. As a
practical matter this was already true in most circumstances:
ScanOptimizer::getMdamStatus ignored the mdamFlag when CQD
RANGESPEC_TRANSFORMATION is set to 'ON'. (And 'ON' is the default value for
that CQD.) The one case when this was not true was if CQD FSO_IN_USE was set to
'0'. So, ironically, when the user is trying to encourage MDAM using CQD
FSO_IN_USE, we would pay attention to this heuristic that prevents MDAM.
It should be noted that the heuristic in IndexDesc::pruneMdam depends on
counting the number of leading columns without a key predicate. It does so
without taking into account parallelization, which means it does not see
potential equality predicates on a leading "_SALT_" column, which can cause it
to reject MDAM when MDAM would be useful. Even so, the heuristic there seems
reasonable for narrowing the set of indexes that we might consider so it is
still good for index elimination purposes though not perfect. But once an index
is being considered, it is better to let the costing code, which does know
about parallelization, to have the final word.
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/DaveBirdsall/trafodion Trafodion2913
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/trafodion/pull/1403.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 #1403
----
commit ae17030878f48aab2be36db7a57d7b11c2413016
Author: Dave Birdsall <dbirdsall@...>
Date: 2018-01-17T17:22:24Z
[TRAFODION-2913] Tweak some MDAM-related heuristics
----
> Tweak some MDAM-related heuristics
> ----------------------------------
>
> Key: TRAFODION-2913
> URL: https://issues.apache.org/jira/browse/TRAFODION-2913
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
> Affects Versions: 2.3
> Reporter: David Wayne Birdsall
> Assignee: David Wayne Birdsall
> Priority: Major
>
> While debugging a plan choice issue on a customer query, two issues were
> noted with MDAM heuristics.
> # When CQD FSO_TO_USE is set to '0', FileScanOptimizer::optimize attempts to
> perform logic similar to that in ScanOptimizer::getMdamStatus, checking the
> mdamFlag that is stored in the index descriptor. But the logic is not the
> same (the inevitable result of having two copies of something!); in the
> latter case the mdamFlag is ignored if CQD RANGESPEC_TRANSFORMATION is 'ON'
> while in the FileScanOptimizer::optimize logic no such additional check is
> made. Now, 'ON' is presently the default for RANGESPACE_TRANSFORMATION. So,
> we have the anomaly that using CQD FSO_TO_USE '0' to force consideration of
> MDAM might still not get MDAM because of a flag that we would ignore
> otherwise.
> # The mdamFlag in the IndexDesc object is set by IndexDesc :: pruneMdam
> (optimizer/IndexDesc.cpp). There is heuristic logic there to guess whether
> MDAM will be useful for a given access path. The major purpose of this logic
> is index elimination: if we have several indexes, and some look like good
> choices for MDAM and others not, we eliminate the ones that are not. Only
> secondarily is this mdam flag later looked at by the scan optimizer, as
> described above in 1. The major purpose of this logic still seems reasonable,
> though the computation logic itself can be criticized for not considering the
> possibility of a parallel predicate on a leading "_SALT_" column, for
> example. But the computation involves a CQD, MDAM_SELECTION_DEFAULT, which is
> set to a low value by default. The customer query involved showed that the
> value used is too low; this flag ended up eliminating a favorable MDAM plan.
> The default was likely last determined in the predecessor product; given that
> the HBase engine has different execution dynamics this value needs to be
> recalibrated.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
