[
https://issues.apache.org/jira/browse/TRAFODION-23?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14624987#comment-14624987
]
Qifan Chen commented on TRAFODION-23:
-------------------------------------
Tested query 2 using the latest build
(4ad81c7a115724d0378d4320791677f12a3adbb8) with osim captured from an earlier
run, and found the NJ is present in the plan.
Decided to hold the resolution of the problem until the problem can be
re-verified on a cluster.
>>
>>control osim simulate continue './tpchq2.osim';
--- SQL operation complete.
>>
>>set schema TRAFODION.JBALIGNED;
--- SQL operation complete.
>>
>>cqd query_cache '0';
--- SQL operation complete.
>>
>>--cqd parallel_num_esps '40';
>>--cqd TRAF_ALLOW_ESP_COLOCATION 'ON';
>>
>>--cqd HBASE_MAX_ESPS '0';
>>
>>prepare xx from select [last 0] s_acctbal, s_name, n_name, p_partkey, p_mfgr,
>>s_address, s_
phone, s_comment from TPCH_PART_100, TPCH_SUPPLIER_100, TPCH_PARTSUPP_100,
TPCH_NATION_100, T
PCH_REGION_100 where p_partkey = ps_partkey and s_suppkey = ps_suppkey and
p_size = 15 and p_
type like '%BRASS' and s_nationkey = n_nationkey and n_regionkey = r_regionkey
and r_name = '
EUROPE' and ps_supplycost = (select min(ps_supplycost) from TPCH_PARTSUPP_100,
TPCH_SUPPLIER_
100, TPCH_NATION_100, TPCH_REGION_100 where p_partkey = ps_partkey and
s_suppkey = ps_suppkey
and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name =
'EUROPE') order by
s_acctbal desc, n_name, s_name, p_partkey;
--- SQL command prepared.
>>
>>explain options 'f' xx;
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------
30 . 31 root 1.00E+000
29 . 30 firstn 1.00E+000
28 . 29 sort 1.00E+000
27 . 28 esp_exchange 1:20(hash2) 1.00E+000
26 . 27 hash_groupby 1.00E+000
25 2 26 hybrid_hash_join u 1.59E+004
24 4 25 hybrid_hash_join u 7.97E+004
21 23 24 nested_join 7.97E+004
22 . 23 probe_cache 1.00E+000
. . 22 trafodion_vsbb_scan TPCH_SUPPLIER_100 1.00E+000
20 . 21 esp_exchange 20(hash2):20(hash2) 7.97E+004
19 18 20 hybrid_hash_join 7.97E+004
. . 19 trafodion_scan TPCH_PARTSUPP_100 7.98E+007
17 . 18 esp_exchange 20(rep-b):20(hash2) 1.99E+004
16 6 17 hybrid_hash_join u 1.99E+004
15 8 16 hybrid_hash_join u 9.96E+004
14 9 15 hybrid_hash_join 9.96E+004
13 . 14 esp_exchange 20(hash2):20(hash2) 9.96E+004
12 11 13 hybrid_hash_join 9.96E+004
. . 12 trafodion_scan TPCH_PARTSUPP_100 7.98E+007
10 . 11 esp_exchange 20(rep-b):20(hash2) 2.49E+004
. . 10 trafodion_scan TPCH_PART_100 2.49E+004
. . 9 trafodion_scan TPCH_SUPPLIER_100 1.00E+006
7 . 8 esp_exchange 20(rep-b):1 (m) 2.50E+001
. . 7 trafodion_scan TPCH_NATION_100 2.50E+001
5 . 6 esp_exchange 20(rep-b):1 (m) 1.00E+000
. . 5 trafodion_scan TPCH_REGION_100 1.00E+000
3 . 4 esp_exchange 20(rep-b):1 (m) 2.50E+001
. . 3 trafodion_scan TPCH_NATION_100 2.50E+001
1 . 2 esp_exchange 20(rep-b):1 (m) 1.00E+000
. . 1 trafodion_scan TPCH_REGION_100 1.00E+000
--- SQL operation complete.
> Keyed nested join in TPCH query 2 and 15 disappeared recently
> -------------------------------------------------------------
>
> Key: TRAFODION-23
> URL: https://issues.apache.org/jira/browse/TRAFODION-23
> Project: Apache Trafodion
> Issue Type: Bug
> Reporter: Qifan Chen
> Assignee: Qifan Chen
> Labels: performance
>
> Good keyed nested join in TPCH query 2 and 15 disappeared in recent
> performance test (close to the ones dated 06222015). Each is replaced by a
> hash join which is more expensive to evaluate.
> To fix this case, we can take the following approach.
> 1. Modify the earlier versions of Trafodion so that they compile without
> log4cpp;
> 2. Install the versions on a cluster and run the two queries
> 3. Identity the earliest version of Trafodion in which the NJ->HJ takes place
> 4. Analyze the code change to isolate the root cause
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
