[
https://issues.apache.org/jira/browse/TRAFODION-1468?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14723729#comment-14723729
]
Qifan Chen commented on TRAFODION-1468:
---------------------------------------
The root cause is in method MDAMOptimalDisjunctPrefixWA::updateMinPrefix()
where a heuristics is used to decide whether a key column should be included in
the MDAM prefix (a set of key columns useful for MDAM). The
containsINListOrRanges() checks both AND and OR predicate as the condition for
IN List, which is wrong. The correct test should be done for OR predicate only.
if ( (CmpCommon::getDefault(RANGESPEC_TRANSFORMATION) == DF_ON ) &&
(containsINListOrRanges(&optimizer_.getDisjuncts())) &&
( prefixColumnPosition_ == (lastColumnPosition_ - 1) )
)
newMinimumFound = TRUE;
else
newMinimumFound = (pMinCost_ == NULL) ? TRUE :
(scmCost->scmCompareCosts(*pMinCost_) == LESS);
In the fix, the code above is written as follows, with some code refactoring
done.
//
// This is a heuristics in that we unconditionally include the last
key column
// with IN list (OR preds) predicate without going through the cost
comparison
// step.
if ( (CmpCommon::getDefault(RANGESPEC_TRANSFORMATION) == DF_ON ) &&
optimizer_.getDisjuncts().containsOrPredsInRanges() &&
prefixColumnPosition_ == (lastColumnPosition_ - 1)
) {
newMinimumFound = TRUE;
} else
newMinimumFound = (pMinCost_ == NULL) ? TRUE :
(scmCost->scmCompareCosts(*pMinCost_) == LESS);
}
With the changes, we realize the MDAM scan.
TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ...............
REQUESTS_IN .............. 1
ROWS_OUT ............ 11,565
EST_OPER_COST ............ 3.91
EST_TOTAL_COST ........... 3.91
DESCRIPTION
max_card_est ..... 835,001
fragment_id ............ 2
parent_frag ............ 0
fragment_type .......... esp
scan_type .............. subset scan limited by mdam of table
object_type ............ Trafodion
cache_size ........ 10,000
probes ................. 1
rows_accessed ..... 92,759
key_columns ............ _SALT_, _DIVISION_1_,
mdam_disjunct .......... .. ... ... ...
(_SALT_ >= (\:_sys_HostVarLoHashPart Hash2Distrib
16)) and (_SALT_ <= (\:_sys_HostVarHiHashPart
Hash2Distrib 16))
part_key_predicates ....
> MDAM plan is not chosen
> ------------------------
>
> Key: TRAFODION-1468
> URL: https://issues.apache.org/jira/browse/TRAFODION-1468
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
> Reporter: Qifan Chen
> Assignee: Qifan Chen
> Labels: performance
>
> The query is as follows.
> select "cliente_CIC", "tipoMovimiento", sum("monto") from T where
> ("cliente_CIC" = '000003566661') and "fechaOrigen" between cast('2013-12-01'
> as timestamp(6)) and cast('2013-12-31' as timestamp(6)) group by 1, 2 order
> by 1, 2;
> Part of the DDL looks like:
> create table T
> (... ...
> , PRIMARY KEY
> (
> "cliente_CIC"
> , "operacion_identificacionOperacion"
> , "fechaOrigen"
> , "nemonico"
> , "claveUnicaRegistroJNL"
> )
> )
> SALT USING 16 PARTITIONS ON
> (
> "cliente_CIC"
> , "operacion_identificacionOperacion"
> )
> DIVISION BY
> (
> date_part('YEARMONTH', "fechaOrigen")
> )
> And stats are available for all leading key column groups.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
