hive-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Sushanth Sowmyan (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (HIVE-9713) CBO : inefficient join order created for left join outer condition
Date Fri, 08 May 2015 23:32:59 GMT

     [ https://issues.apache.org/jira/browse/HIVE-9713?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Sushanth Sowmyan updated HIVE-9713:
-----------------------------------
    Fix Version/s:     (was: 1.2.0)

> CBO : inefficient join order created for left join outer condition
> ------------------------------------------------------------------
>
>                 Key: HIVE-9713
>                 URL: https://issues.apache.org/jira/browse/HIVE-9713
>             Project: Hive
>          Issue Type: Bug
>          Components: CBO
>    Affects Versions: 0.14.0
>            Reporter: Mostafa Mokhtar
>            Assignee: Laljo John Pullokkaran
>
> For the query below which is a subset of TPC-DS Query 80, CBO joins catalog_sales with
catalog_returns first although the CE of the join is relatively high.
> catalog_sales should be joined with the selective dimension tables first.
> {code}
> select  cp_catalog_page_id as catalog_page_id,
>           sum(cs_ext_sales_price) as sales,
>           sum(coalesce(cr_return_amount, 0)) as returns,
>           sum(cs_net_profit - coalesce(cr_net_loss, 0)) as profit
>   from catalog_sales left outer join catalog_returns on
>          (cs_item_sk = cr_item_sk and cs_order_number = cr_order_number),
>      date_dim,
>      catalog_page,
>      item,
>      promotion
>  where cs_sold_date_sk = d_date_sk
>        and d_date between cast('1998-08-04' as date)
>                   and (cast('1998-09-04' as date))
>         and cs_catalog_page_sk = cp_catalog_page_sk
>        and cs_item_sk = i_item_sk
>        and i_current_price > 50
>        and cs_promo_sk = p_promo_sk
>        and p_channel_tv = 'N'
> group by cp_catalog_page_id
> {code}
> Logical plan from CBO debug logs 
> {code}
> 2015-02-17 22:34:04,577 DEBUG [main]: parse.CalcitePlanner (CalcitePlanner.java:apply(743))
- Plan After Join Reordering:
> HiveProject(catalog_page_id=[$0], sales=[$1], returns=[$2], profit=[$3]): rowcount =
10590.0, cumulative cost = {8.25242586823495E15 rows, 0.0 cpu, 0.0 io}, id = 1395
>   HiveAggregate(group=[{0}], agg#0=[sum($1)], agg#1=[sum($2)], agg#2=[sum($3)]): rowcount
= 10590.0, cumulative cost = {8.25242586823495E15 rows, 0.0 cpu, 0.0 io}, id = 1393
>     HiveProject($f0=[$14], $f1=[$5], $f2=[coalesce($9, 0)], $f3=[-($6, coalesce($10,
0))]): rowcount = 1.368586152225262E8, cumulative cost = {8.25242586823495E15 rows, 0.0 cpu,
0.0 io}, id = 1391
>       HiveJoin(condition=[=($3, $17)], joinType=[inner]): rowcount = 1.368586152225262E8,
cumulative cost = {8.25242586823495E15 rows, 0.0 cpu, 0.0 io}, id = 1508
>         HiveJoin(condition=[=($2, $15)], joinType=[inner]): rowcount = 2.737172304450524E8,
cumulative cost = {8.252425594517495E15 rows, 0.0 cpu, 0.0 io}, id = 1506
>           HiveJoin(condition=[=($1, $13)], joinType=[inner]): rowcount = 8.211516913351573E8,
cumulative cost = {8.252424773349804E15 rows, 0.0 cpu, 0.0 io}, id = 1504
>             HiveJoin(condition=[=($0, $11)], joinType=[inner]): rowcount = 1.1296953399027347E11,
cumulative cost = {8.252311803804096E15 rows, 0.0 cpu, 0.0 io}, id = 1418
>               HiveJoin(condition=[AND(=($2, $7), =($4, $8))], joinType=[left]): rowcount
= 8.252311488455487E15, cumulative cost = {3.15348608E8 rows, 0.0 cpu, 0.0 io}, id = 1413
>                 HiveProject(cs_sold_date_sk=[$0], cs_catalog_page_sk=[$12], cs_item_sk=[$15],
cs_promo_sk=[$16], cs_order_number=[$17], cs_ext_sales_price=[$23], cs_net_profit=[$33]):
rowcount = 2.86549727E8, cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io}, id = 1324
>                   HiveTableScan(table=[[tpcds_bin_orc_200.catalog_sales]]): rowcount
= 2.86549727E8, cumulative cost = {0}, id = 1136
>                 HiveProject(cr_item_sk=[$2], cr_order_number=[$16], cr_return_amount=[$18],
cr_net_loss=[$26]): rowcount = 2.8798881E7, cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io},
id = 1327
>                   HiveTableScan(table=[[tpcds_bin_orc_200.catalog_returns]]): rowcount
= 2.8798881E7, cumulative cost = {0}, id = 1137
>               HiveProject(d_date_sk=[$0], d_date=[$2]): rowcount = 1.0, cumulative cost
= {0.0 rows, 0.0 cpu, 0.0 io}, id = 1371
>                 HiveFilter(condition=[between(false, $2, CAST('1998-08-04'):DATE, CAST('1998-09-04'):DATE)]):
rowcount = 1.0, cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io}, id = 1369
>                   HiveTableScan(table=[[tpcds_bin_orc_200.date_dim]]): rowcount = 73049.0,
cumulative cost = {0}, id = 1138
>             HiveProject(cp_catalog_page_sk=[$0], cp_catalog_page_id=[$1]): rowcount =
11718.0, cumulative cost = {0.0 rows, 0.0 cpu, 0.0 io}, id = 1375
>               HiveTableScan(table=[[tpcds_bin_orc_200.catalog_page]]): rowcount = 11718.0,
cumulative cost = {0}, id = 1139
>           HiveProject(i_item_sk=[$0], i_current_price=[$5]): rowcount = 16000.0, cumulative
cost = {0.0 rows, 0.0 cpu, 0.0 io}, id = 1381
>             HiveFilter(condition=[>($5, 5E1)]): rowcount = 16000.0, cumulative cost
= {0.0 rows, 0.0 cpu, 0.0 io}, id = 1379
>               HiveTableScan(table=[[tpcds_bin_orc_200.item]]): rowcount = 48000.0, cumulative
cost = {0}, id = 1140
>         HiveProject(p_promo_sk=[$0], p_channel_tv=[$11]): rowcount = 225.0, cumulative
cost = {0.0 rows, 0.0 cpu, 0.0 io}, id = 1387
>           HiveFilter(condition=[=($11, 'N')]): rowcount = 225.0, cumulative cost = {0.0
rows, 0.0 cpu, 0.0 io}, id = 1385
>             HiveTableScan(table=[[tpcds_bin_orc_200.promotion]]): rowcount = 450.0, cumulative
cost = {0}, id = 1141
> {code}
> Explain plan 
> {code}
> STAGE DEPENDENCIES:
>   Stage-1 is a root stage
>   Stage-0 depends on stages: Stage-1
> STAGE PLANS:
>   Stage: Stage-1
>     Tez
>       Edges:
>         Map 1 <- Map 2 (BROADCAST_EDGE)
>         Map 3 <- Map 1 (BROADCAST_EDGE)
>         Map 4 <- Map 3 (BROADCAST_EDGE), Map 6 (BROADCAST_EDGE), Map 7 (BROADCAST_EDGE)
>         Reducer 5 <- Map 4 (SIMPLE_EDGE)
>       DagName: mmokhtar_20150306141010_d8c1b2d5-f05f-4039-8261-a69b6f18a2ac:1
>       Vertices:
>         Map 1
>             Map Operator Tree:
>                 TableScan
>                   alias: catalog_sales
>                   filterExpr: (((cs_sold_date_sk is not null and cs_catalog_page_sk is
not null) and cs_item_sk is not null) and cs_promo_sk is not null) (type: boolean)
>                   Statistics: Num rows: 286549727 Data size: 65825832570 Basic stats:
COMPLETE Column stats: COMPLETE
>                   Filter Operator
>                     predicate: (((cs_sold_date_sk is not null and cs_catalog_page_sk
is not null) and cs_item_sk is not null) and cs_promo_sk is not null) (type: boolean)
>                     Statistics: Num rows: 285112475 Data size: 7974560516 Basic stats:
COMPLETE Column stats: COMPLETE
>                     Select Operator
>                       expressions: cs_sold_date_sk (type: int), cs_catalog_page_sk (type:
int), cs_item_sk (type: int), cs_promo_sk (type: int), cs_order_number (type: int), cs_ext_sales_price
(type: float), cs_net_profit (type: float)
>                       outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6
>                       Statistics: Num rows: 285112475 Data size: 7974560516 Basic stats:
COMPLETE Column stats: COMPLETE
>                       Map Join Operator
>                         condition map:
>                              Left Outer Join0 to 1
>                         keys:
>                           0 _col2 (type: int), _col4 (type: int)
>                           1 _col0 (type: int), _col1 (type: int)
>                         outputColumnNames: _col0, _col1, _col2, _col3, _col5, _col6,
_col9, _col10
>                         input vertices:
>                           1 Map 2
>                         Statistics: Num rows: 2911 Data size: 93152 Basic stats: COMPLETE
Column stats: COMPLETE
>                         Reduce Output Operator
>                           key expressions: _col0 (type: int)
>                           sort order: +
>                           Map-reduce partition columns: _col0 (type: int)
>                           Statistics: Num rows: 2911 Data size: 93152 Basic stats: COMPLETE
Column stats: COMPLETE
>                           value expressions: _col1 (type: int), _col2 (type: int), _col3
(type: int), _col5 (type: float), _col6 (type: float), _col9 (type: float), _col10 (type:
float)
>             Execution mode: vectorized
>         Map 2
>             Map Operator Tree:
>                 TableScan
>                   alias: catalog_returns
>                   filterExpr: cr_item_sk is not null (type: boolean)
>                   Statistics: Num rows: 28798881 Data size: 5764329494 Basic stats: COMPLETE
Column stats: COMPLETE
>                   Filter Operator
>                     predicate: cr_item_sk is not null (type: boolean)
>                     Statistics: Num rows: 28798881 Data size: 456171072 Basic stats:
COMPLETE Column stats: COMPLETE
>                     Select Operator
>                       expressions: cr_item_sk (type: int), cr_order_number (type: int),
cr_return_amount (type: float), cr_net_loss (type: float)
>                       outputColumnNames: _col0, _col1, _col2, _col3
>                       Statistics: Num rows: 28798881 Data size: 456171072 Basic stats:
COMPLETE Column stats: COMPLETE
>                       Reduce Output Operator
>                         key expressions: _col0 (type: int), _col1 (type: int)
>                         sort order: ++
>                         Map-reduce partition columns: _col0 (type: int), _col1 (type:
int)
>                         Statistics: Num rows: 28798881 Data size: 456171072 Basic stats:
COMPLETE Column stats: COMPLETE
>                         value expressions: _col2 (type: float), _col3 (type: float)
>             Execution mode: vectorized
>         Map 3
>             Map Operator Tree:
>                 TableScan
>                   alias: date_dim
>                   filterExpr: (d_date BETWEEN 1998-08-04 AND 1998-09-04 and d_date_sk
is not null) (type: boolean)
>                   Statistics: Num rows: 73049 Data size: 81741831 Basic stats: COMPLETE
Column stats: COMPLETE
>                   Filter Operator
>                     predicate: (d_date BETWEEN 1998-08-04 AND 1998-09-04 and d_date_sk
is not null) (type: boolean)
>                     Statistics: Num rows: 36524 Data size: 3579352 Basic stats: COMPLETE
Column stats: COMPLETE
>                     Select Operator
>                       expressions: d_date_sk (type: int)
>                       outputColumnNames: _col0
>                       Statistics: Num rows: 36524 Data size: 146096 Basic stats: COMPLETE
Column stats: COMPLETE
>                       Map Join Operator
>                         condition map:
>                              Inner Join 0 to 1
>                         keys:
>                           0 _col0 (type: int)
>                           1 _col0 (type: int)
>                         outputColumnNames: _col1, _col2, _col3, _col5, _col6, _col9,
_col10
>                         input vertices:
>                           0 Map 1
>                         Statistics: Num rows: 1456 Data size: 40768 Basic stats: COMPLETE
Column stats: COMPLETE
>                         Reduce Output Operator
>                           key expressions: _col1 (type: int)
>                           sort order: +
>                           Map-reduce partition columns: _col1 (type: int)
>                           Statistics: Num rows: 1456 Data size: 40768 Basic stats: COMPLETE
Column stats: COMPLETE
>                           value expressions: _col2 (type: int), _col3 (type: int), _col5
(type: float), _col6 (type: float), _col9 (type: float), _col10 (type: float)
>             Execution mode: vectorized
>         Map 4
>             Map Operator Tree:
>                 TableScan
>                   alias: catalog_page
>                   filterExpr: cp_catalog_page_sk is not null (type: boolean)
>                   Statistics: Num rows: 11718 Data size: 5400282 Basic stats: COMPLETE
Column stats: COMPLETE
>                   Filter Operator
>                     predicate: cp_catalog_page_sk is not null (type: boolean)
>                     Statistics: Num rows: 11718 Data size: 1218672 Basic stats: COMPLETE
Column stats: COMPLETE
>                     Select Operator
>                       expressions: cp_catalog_page_sk (type: int), cp_catalog_page_id
(type: string)
>                       outputColumnNames: _col0, _col1
>                       Statistics: Num rows: 11718 Data size: 1218672 Basic stats: COMPLETE
Column stats: COMPLETE
>                       Map Join Operator
>                         condition map:
>                              Inner Join 0 to 1
>                         keys:
>                           0 _col1 (type: int)
>                           1 _col0 (type: int)
>                         outputColumnNames: _col2, _col3, _col5, _col6, _col9, _col10,
_col14
>                         input vertices:
>                           0 Map 3
>                         Statistics: Num rows: 1456 Data size: 180544 Basic stats: COMPLETE
Column stats: COMPLETE
>                         Map Join Operator
>                           condition map:
>                                Inner Join 0 to 1
>                           keys:
>                             0 _col2 (type: int)
>                             1 _col0 (type: int)
>                           outputColumnNames: _col3, _col5, _col6, _col9, _col10, _col14
>                           input vertices:
>                             1 Map 6
>                           Statistics: Num rows: 486 Data size: 58320 Basic stats: COMPLETE
Column stats: COMPLETE
>                           Map Join Operator
>                             condition map:
>                                  Inner Join 0 to 1
>                             keys:
>                               0 _col3 (type: int)
>                               1 _col0 (type: int)
>                             outputColumnNames: _col5, _col6, _col9, _col10, _col14
>                             input vertices:
>                               1 Map 7
>                             Statistics: Num rows: 243 Data size: 28188 Basic stats: COMPLETE
Column stats: COMPLETE
>                             Select Operator
>                               expressions: _col14 (type: string), _col5 (type: float),
COALESCE(_col9,0) (type: float), (_col6 - COALESCE(_col10,0)) (type: float)
>                               outputColumnNames: _col0, _col1, _col2, _col3
>                               Statistics: Num rows: 243 Data size: 28188 Basic stats:
COMPLETE Column stats: COMPLETE
>                               Group By Operator
>                                 aggregations: sum(_col1), sum(_col2), sum(_col3)
>                                 keys: _col0 (type: string)
>                                 mode: hash
>                                 outputColumnNames: _col0, _col1, _col2, _col3
>                                 Statistics: Num rows: 121 Data size: 15004 Basic stats:
COMPLETE Column stats: COMPLETE
>                                 Reduce Output Operator
>                                   key expressions: _col0 (type: string)
>                                   sort order: +
>                                   Map-reduce partition columns: _col0 (type: string)
>                                   Statistics: Num rows: 121 Data size: 15004 Basic stats:
COMPLETE Column stats: COMPLETE
>                                   value expressions: _col1 (type: double), _col2 (type:
double), _col3 (type: double)
>             Execution mode: vectorized
>         Map 6
>             Map Operator Tree:
>                 TableScan
>                   alias: item
>                   filterExpr: ((i_current_price > 50.0) and i_item_sk is not null)
(type: boolean)
>                   Statistics: Num rows: 48000 Data size: 68732712 Basic stats: COMPLETE
Column stats: COMPLETE
>                   Filter Operator
>                     predicate: ((i_current_price > 50.0) and i_item_sk is not null)
(type: boolean)
>                     Statistics: Num rows: 16000 Data size: 127832 Basic stats: COMPLETE
Column stats: COMPLETE
>                     Select Operator
>                       expressions: i_item_sk (type: int)
>                       outputColumnNames: _col0
>                       Statistics: Num rows: 16000 Data size: 64000 Basic stats: COMPLETE
Column stats: COMPLETE
>                       Reduce Output Operator
>                         key expressions: _col0 (type: int)
>                         sort order: +
>                         Map-reduce partition columns: _col0 (type: int)
>                         Statistics: Num rows: 16000 Data size: 64000 Basic stats: COMPLETE
Column stats: COMPLETE
>             Execution mode: vectorized
>         Map 7
>             Map Operator Tree:
>                 TableScan
>                   alias: promotion
>                   filterExpr: ((p_channel_tv = 'N') and p_promo_sk is not null) (type:
boolean)
>                   Statistics: Num rows: 450 Data size: 530848 Basic stats: COMPLETE Column
stats: COMPLETE
>                   Filter Operator
>                     predicate: ((p_channel_tv = 'N') and p_promo_sk is not null) (type:
boolean)
>                     Statistics: Num rows: 225 Data size: 20025 Basic stats: COMPLETE
Column stats: COMPLETE
>                     Select Operator
>                       expressions: p_promo_sk (type: int)
>                       outputColumnNames: _col0
>                       Statistics: Num rows: 225 Data size: 900 Basic stats: COMPLETE
Column stats: COMPLETE
>                       Reduce Output Operator
>                         key expressions: _col0 (type: int)
>                         sort order: +
>                         Map-reduce partition columns: _col0 (type: int)
>                         Statistics: Num rows: 225 Data size: 900 Basic stats: COMPLETE
Column stats: COMPLETE
>             Execution mode: vectorized
>         Reducer 5
>             Reduce Operator Tree:
>               Group By Operator
>                 aggregations: sum(VALUE._col0), sum(VALUE._col1), sum(VALUE._col2)
>                 keys: KEY._col0 (type: string)
>                 mode: mergepartial
>                 outputColumnNames: _col0, _col1, _col2, _col3
>                 Statistics: Num rows: 121 Data size: 15004 Basic stats: COMPLETE Column
stats: COMPLETE
>                 File Output Operator
>                   compressed: false
>                   Statistics: Num rows: 121 Data size: 15004 Basic stats: COMPLETE Column
stats: COMPLETE
>                   table:
>                       input format: org.apache.hadoop.mapred.TextInputFormat
>                       output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
>                       serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
>   Stage: Stage-0
>     Fetch Operator
>       limit: -1
>       Processor Tree:
>         ListSink
> {code}



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Mime
View raw message