You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "David Wayne Birdsall (JIRA)" <ji...@apache.org> on 2015/11/13 22:07:11 UTC
[jira] [Commented] (TRAFODION-1585) MDAM plan is not chosen unless
NJ is turned off
[ https://issues.apache.org/jira/browse/TRAFODION-1585?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15004718#comment-15004718 ]
David Wayne Birdsall commented on TRAFODION-1585:
-------------------------------------------------
The problem query did a join of two scalar roll-ups (that is, two selects that each were grouped and then aggregated again into a single row). Both of the selects happened to be against the same table -- though this was not essential to the problem -- and the table was salted. The primary key was three columns, and in both selects we covered the first two columns with equality predicates to constants. This is a perfect application for MDAM, as the clustering key consists of the salt column "_SALT_" followed by the primary key columns. MDAM would let us materialize distinct salt values, then do direct access, as opposed to doing a full table scan.
If a hash join was forced (e.g. by using CQD NESTED_JOINS 'OFF'), then the Optimizer would pick MDAM for both legs of the join. However, by default, the Optimizer would pick a nested join, and would only do MDAM on the outer leg, and do a full table scan on the inner leg. This is inefficient.
It turns out that this Optimizer behavior was intended. One problem with nested joins is that mis-estimating the number of probes on the inner leg can lead to bad performance. (That is, if the Optimizer thinks there will be just a few probes and then chooses nested join, and then it turns out there are a large number of probes instead, the nested join can quickly become far more expensive than, say, a hash join.) When MDAM is in the mix on the inner table, the performance issue can be compounded, particularly if the number of MDAM probes is also mis-estimated.
So, a knob was added to the Optimizer some time ago, CQD MDAM_UNDER_NJ_PROCESS_THRESHOLD, to put a bound on when MDAM would be considered for the inner leg of a nested join. Currently, this CQD defaults to '0', meaning that MDAM is never chosen for the inner leg.
There are circumstances, however, when we are certain of the number of probes for the inner leg (that is, we can estimate with certainty), and the problem query is an example of that. The outer leg has a scalar aggregate at the top, so we are guaranteed there is exactly one row. In this case, it makes sense to make an exception to the limit imposed by CQD MDAM_UNDER_NJ_PROCESS_THRESHOLD, and allow an MDAM plan to be considered.
The fix to this JIRA makes exactly this change. It checks for a cardinality constraint on the outer child of a nested join, and if one is present, passes it to the inner child via the InputPhysicalProperty. That property gets propagated down the inner child subtree to the Scan node(s). The Scan node already knows via the InputPhysicalProperty that it is in the inner leg of a nested join. With this fix it now also knows whether there is a cardinality constraint in the number of probes to the Scan node. And if that cardinality constraint is at most one, it will allow MDAM in spite of any CQD MDAM_UNDER_NJ_PROCESS_THRESHOLD setting.
> MDAM plan is not chosen unless NJ is turned off
> -----------------------------------------------
>
> Key: TRAFODION-1585
> URL: https://issues.apache.org/jira/browse/TRAFODION-1585
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
> Reporter: Qifan Chen
> Assignee: David Wayne Birdsall
>
> MDAM is used in scan node #8 in the following plan.
> >explain options 'f' xx;
> LC RC OP OPERATOR OPT DESCRIPTION CARD
> ---- ---- ---- -------------------- -------- -------------------- ---------
> 15 . 16 root 1.00E+000
> 14 7 15 hybrid_hash_join 1.00E+000
> 13 . 14 hash_partial_groupby 1.00E+000
> 12 . 13 esp_exchange 1:21(hash2) 1.00E+000
> 11 . 12 sort_partial_groupby 1.00E+000
> 10 . 11 hash_groupby 6.60E+004
> 9 . 10 hash_groupby 1.35E+005
> 8 . 9 esp_exchange 21(hash2):16(hash2) 1.40E+006
> . . 8 trafodion_scan OP 1.40E+006
> 6 . 7 sort_groupby 1.00E+000
> 5 . 6 hash_partial_groupby 3.09E+001
> 4 . 5 esp_exchange 1:21(hash2) 3.09E+001
> 3 . 4 hash_partial_groupby 3.09E+001
> 2 . 3 hash_groupby 1.35E+005
> 1 . 2 esp_exchange 21(hash2):16(hash2) 1.40E+006
> . . 1 trafodion_scan OP 1.40E+006
> The same MDAM scan is absent if CQD NESTED_JOINS 'off' is not used.
> TRAFODION_SCAN ============================ SEQ_NO 8 NO CHILDREN
> TABLE_NAME ............... P
> REQUESTS_IN .............. 1
> ROWS_OUT ......... 1,401,802
> EST_OPER_COST ............ 0.21
> EST_TOTAL_COST ........... 0.21
> DESCRIPTION
> max_card_est ........... 1.4018e+06
> fragment_id ............ 5
> parent_frag ............ 4
> fragment_type .......... esp
> scan_type .............. subset scan limited by mdam of table
> OP
> object_type ............ Trafodion
> cache_size ........ 10,000
> probes ................. 1
> rows_accessed .......... 1.4018e+06
> key_columns ............ _SALT_, SITE_ID, PANEL, DWD_ID
> executor_predicates .... (PANEL = '1') and (SITE_ID = 450)
> mdam_disjunct .......... (PANEL = '1') and (SITE_ID = 450) and (_SALT_
> >= (\:_sys_HostVarLoHashPart Hash2Distrib 64)) and
> (_SALT_ <= (\:_sys_HostVarHiHashPart Hash2Distrib
> 64))
> part_key_predicates .... (PANEL = '1' = PANEL) and (SITE_ID = 450
> = SITE_ID)
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)