[ 
https://issues.apache.org/jira/browse/TRAFODION-2645?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16363306#comment-16363306
 ] 

ASF GitHub Bot commented on TRAFODION-2645:
-------------------------------------------

GitHub user DaveBirdsall opened a pull request:

    https://github.com/apache/trafodion/pull/1443

    [TRAFODION-2645] Fixes to MDAM costing code found during inner join tests

    This set of changes fixes some issues that were found in the new MDAM 
costing code while testing it for the case of an inner table of a nested join.
    
    The fixes are:
    
    1. We were way too pessimistic in calculating the number of seeks. We 
assumed we would do a seek per block, but it is obvious that the number of 
seeks is bounded from above by the number of subsets. This showed up when 
testing queries with tiny numbers of probes (4) but large numbers of rows per 
sequential subset (over 100 blocks worth in total).
    
    2. We were applying some rowsize adjustment factors twice, once in 
NewMDAMCostWA::compute, and again in 
FileScanOptimizer::scmComputeMDAMCostForHbase, which it calls. The duplicate 
logic was removed in NewMDAMCostWA::compute.
    
    3. We were overestimating the number of fetch subset rows when costing MDAM 
for the inner table of a nested join. It turns out that the histogram handling 
does a cross product scale-up of the histograms in order to calculate the 
effect of a join predicate. But this scale-up looks at things from the 
standpoint of the complete result of the query, while the MDAM costing logic is 
looking at things from the standpoint of a single outer table join probe. 
Dividing fetch row counts by the number of outer table join probes corrects the 
problem. Then at a higher level we multiply by the number of outer table join 
probes (covering both MDAM probes and MDAM fetches) to calculate the total cost 
of the MDAM scan.
    
    Notes: The new MDAM costing code is still off by default. To turn it on, 
use CQD MDAM_COSTING_REWRITE 'ON'. To test MDAM on the inner table of nested 
joins, one must either force such plans with control query shape or turn off 
heuristics that prevent MDAM in these cases. There are two of main interest: 
CQD MDAM_UNDER_NJ_PROBES_THRESHOLD turns off MDAM when the number of outer 
table probes is higher than its value. It defaults to '0' so by default MDAM is 
(almost) always turned off on the inner table of nested joins. Setting this to 
a positive value (I used '1000' for my testing) allows MDAM. Secondly, there is 
a heuristic in the JoinToTSJ transformation rule that prevents nested joins if 
no inner table key prefix is covered by some predicate. This prevents 
consideration of nested joins where MDAM may help when a predicate is missing 
on the leading column. Setting CQD KEYLESS_NESTED_JOINS 'ON' overrides this 
heuristic. Alternatively, turning off hash joins and merge joins also overrides 
this heuristic.
    
    Summarizing, if one wishes to test the new MDAM costing code, and wishes to 
benefit from nested joins using MDAM on the inner table, then one should 
presently set these CQDs:
    
    ```
    CQD MDAM_UNDER_NJ_PROBES_THRESHOLD '1000';  -- (some positive value greater 
than one)
    CQD KEYLESS_NESTED_JOINS 'ON';
    CQD MDAM_COSTING_REWRITE 'ON';
    ```
    


You can merge this pull request into a Git repository by running:

    $ git pull https://github.com/DaveBirdsall/trafodion MdamRewriteNJ

Alternatively you can review and apply these changes as the patch at:

    https://github.com/apache/trafodion/pull/1443.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 #1443
    
----
commit c008ebe519f51e5344b650a9c88e66e973320084
Author: Dave Birdsall <dbirdsall@...>
Date:   2018-02-14T00:42:41Z

    [TRAFODION-2645] Fixes to MDAM costing code found during inner join testing

----


> MDAM costing overestimates I/O cost
> -----------------------------------
>
>                 Key: TRAFODION-2645
>                 URL: https://issues.apache.org/jira/browse/TRAFODION-2645
>             Project: Apache Trafodion
>          Issue Type: Bug
>          Components: sql-cmp
>    Affects Versions: 2.2-incubating
>         Environment: All
>            Reporter: David Wayne Birdsall
>            Assignee: David Wayne Birdsall
>            Priority: Major
>
> The method MDAMCostWA::compute (optimizer/ScanOptimizer.cpp) has logic to 
> calculate the total cost of an MDAM plan and compare it to the cost of a 
> single subset plan. In the case of a Trafodion table, the cost logic for MDAM 
> cost has an error. It is adding the total number of probes (totalRqsts) to 
> the number of seeks (totalSeeks) in order to calculate I/O costs.
> TotalSeeks models direct access disk seeks. These happen at the block level 
> and are an I/O cost.
> TotalRqsts models the number of probes, which is a CPU cost. A probe is a 
> direct access within a block that has already been read into memory. This is 
> a vastly smaller unit of cost than an I/O operation.
> Note that TotalRqsts is already modeled as a message cost since it is added 
> to TotalRows.
> The effect of the bug is to vastly penalize MDAM plans having large numbers 
> of probes. This will show up the most on the largest tables.
> The fix is simply not to add totalRqsts to totalSeeks. However, in making 
> this fix, it is likely that other MDAM-related parameters will need tuning. 
> So this change needs to be done carefully.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

Reply via email to