[
https://issues.apache.org/jira/browse/TRAFODION-2645?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16116766#comment-16116766
]
David Wayne Birdsall commented on TRAFODION-2645:
-------------------------------------------------
I experimented with the suggested change a few days ago. When I ran the
development regression suite I encountered two plan changes that were not
beneficial. In seabase/TEST011, one query that formerly did not use MDAM
started using MDAM with a disjunct of (V1 < 100000) AND (V2 < 99) AND (V3 <
500) which intuitively does not seem like a good plan to me. And indeed when I
executed the query with old and new plans I found the execution time lengthened
from 0.332512 seconds to 0.739676 seconds on my workstation. This was not a
controlled environment so more careful testing would be needed to prove that it
is detrimental but it seems like a plausible result to me. In test
seabase/TEST026, I saw three queries switch from a full index-only scan against
the OBJECTS_UNIQ_IDX to an MDAM plan against the OBJECTS table. The MDAM plans
look better, actually, but we get different results! It turns out the testware
needs upgrading. The test is a CLEANUP test, and the OBJECTS_UNIQ_IDX was
intentionally made to be inconsistent with the OBJECTS table. The testware
should be upgraded to execute the queries twice, with the access path forced
each way.
So, the change suggested in this JIRA may still be a good one but additional
changes might be needed. Further development testing is needed.
> 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
>
> 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
(v6.4.14#64029)
