[jira] [Updated] (TRAFODION-2913) Tweak some MDAM-related heuristics

["David Wayne Birdsall (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/TRAFODION-2913?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

David Wayne Birdsall updated TRAFODION-2913:
--------------------------------------------
    Description: 
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.

> 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)