Explain Plan not optimized by CBO. Vertex dependency in root stage Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 4 (SIMPLE_EDGE) Reducer 3 <- Reducer 2 (SIMPLE_EDGE) Stage-0 Fetch Operator limit:-1 Stage-1 Reducer 3 File Output Operator [FS_139] compressed:false Statistics:Num rows: 1 Data size: 152 Basic stats: COMPLETE Column stats: PARTIAL 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"} Select Operator [SEL_138] outputColumnNames:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9","_col10","_col11","_col12","_col13","_col14","_col15","_col16","_col17","_col18","_col19","_col20","_col21","_col22","_col23","_col24","_col25","_col26","_col27","_col28"] Statistics:Num rows: 1 Data size: 152 Basic stats: COMPLETE Column stats: PARTIAL Group By Operator [GBY_137] | aggregations:["min(VALUE._col0)","sum(VALUE._col1)","sum(VALUE._col2)","sum(VALUE._col3)","sum(VALUE._col4)","sum(VALUE._col5)","sum(VALUE._col6)","sum(VALUE._col7)"] | keys:KEY._col0 (type: int), KEY._col1 (type: int), KEY._col2 (type: int), KEY._col3 (type: varchar(50)), KEY._col4 (type: timestamp), KEY._col5 (type: int) | outputColumnNames:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9","_col10","_col11","_col12","_col13"] | Statistics:Num rows: 1 Data size: 104 Basic stats: COMPLETE Column stats: PARTIAL |<-Reducer 2 [SIMPLE_EDGE] Reduce Output Operator [RS_136] key expressions:_col0 (type: int), _col1 (type: int), _col2 (type: int), _col3 (type: varchar(50)), _col4 (type: timestamp), _col5 (type: int) Map-reduce partition columns:_col0 (type: int), _col1 (type: int), _col2 (type: int), _col3 (type: varchar(50)), _col4 (type: timestamp), _col5 (type: int) sort order:++++++ Statistics:Num rows: 2738 Data size: 284752 Basic stats: COMPLETE Column stats: PARTIAL value expressions:_col6 (type: timestamp), _col7 (type: bigint), _col8 (type: bigint), _col9 (type: bigint), _col10 (type: bigint), _col11 (type: bigint), _col12 (type: bigint), _col13 (type: bigint) Group By Operator [GBY_135] aggregations:["min(_col3)","sum(CASE WHEN ((_col19 = 17)) THEN (_col18) ELSE (0) END)","sum(CASE WHEN ((_col19 = 18)) THEN (_col18) ELSE (0) END)","sum(CASE WHEN ((_col19 = 17)) THEN (1) ELSE (0) END)","sum(CASE WHEN ((_col19 = 18)) THEN (1) ELSE (0) END)","sum(_col18)","sum(CASE WHEN ((_col19) IN (20, 22, 24, 25, 26, 27)) THEN (_col18) ELSE (0) END)","sum(CASE WHEN ((_col19) IN (20, 22, 24, 25, 26, 27)) THEN (1) ELSE (0) END)"] keys:_col8 (type: int), _col5 (type: int), _col6 (type: int), _col0 (type: varchar(50)), _col2 (type: timestamp), _col1 (type: int) outputColumnNames:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9","_col10","_col11","_col12","_col13"] Statistics:Num rows: 2738 Data size: 284752 Basic stats: COMPLETE Column stats: PARTIAL Select Operator [SEL_134] outputColumnNames:["_col8","_col5","_col6","_col0","_col2","_col1","_col3","_col19","_col18"] Statistics:Num rows: 91839821142 Data size: 367359284568 Basic stats: COMPLETE Column stats: PARTIAL Filter Operator [FIL_142] predicate:((_col26 > _col3) and (_col26 <= _col2)) (type: boolean) Statistics:Num rows: 91839821142 Data size: 367359284568 Basic stats: COMPLETE Column stats: PARTIAL Merge Join Operator [MERGEJOIN_151] | condition map:[{"":"Inner Join 0 to 1"}] | keys:{"0":"siteidnumber (type: int), epsinfoseqnum (type: int), legalconfignumber (type: int), rsid (type: int)","1":"siteidnumber (type: int), epsinfoseqnum (type: int), legalconfignumber (type: int), rsid (type: int)"} | outputColumnNames:["_col0","_col1","_col2","_col3","_col5","_col6","_col8","_col18","_col19","_col26"] | Statistics:Num rows: 826558390283 Data size: 160352327714902 Basic stats: COMPLETE Column stats: PARTIAL |<-Map 1 [SIMPLE_EDGE] | Reduce Output Operator [RS_129] | key expressions:siteidnumber (type: int), epsinfoseqnum (type: int), legalconfignumber (type: int), rsid (type: int) | Map-reduce partition columns:siteidnumber (type: int), epsinfoseqnum (type: int), legalconfignumber (type: int), rsid (type: int) | sort order:++++ | Statistics:Num rows: 947890 Data size: 3791560 Basic stats: COMPLETE Column stats: PARTIAL | value expressions:epsname (type: varchar(50)), endofcurrentsessiondt (type: timestamp), endoflastsessiondt (type: timestamp) | Filter Operator [FIL_143] | predicate:(epsinfoseqnum is not null and legalconfignumber is not null and rsid is not null) (type: boolean) | Statistics:Num rows: 947890 Data size: 3791560 Basic stats: COMPLETE Column stats: PARTIAL | TableScan [TS_126] | alias:s | Statistics:Num rows: 7583120 Data size: 60664960 Basic stats: COMPLETE Column stats: PARTIAL |<-Map 4 [SIMPLE_EDGE] Reduce Output Operator [RS_131] key expressions:siteidnumber (type: int), epsinfoseqnum (type: int), legalconfignumber (type: int), rsid (type: int) Map-reduce partition columns:siteidnumber (type: int), epsinfoseqnum (type: int), legalconfignumber (type: int), rsid (type: int) sort order:++++ Statistics:Num rows: 20055959 Data size: 80223836 Basic stats: COMPLETE Column stats: PARTIAL value expressions:amount (type: int), cashintype (type: int), gameserverinsertdt (type: timestamp) Filter Operator [FIL_144] predicate:(epsinfoseqnum is not null and legalconfignumber is not null and rsid is not null and gameserverinsertdt BETWEEN '2018-01-01' AND '2018-01-31') (type: boolean) Statistics:Num rows: 20055959 Data size: 80223836 Basic stats: COMPLETE Column stats: PARTIAL TableScan [TS_127] alias:ci Statistics:Num rows: 320895350 Data size: 6097011650 Basic stats: COMPLETE Column stats: PARTIAL Dynamic Partitioning Event Operator [EVENT_147] Statistics:Num rows: 192 Data size: 768 Basic stats: COMPLETE Column stats: PARTIAL Group By Operator [GBY_146] keys:_col0 (type: int) outputColumnNames:["_col0"] Statistics:Num rows: 192 Data size: 768 Basic stats: COMPLETE Column stats: PARTIAL Select Operator [SEL_145] outputColumnNames:["_col0"] Statistics:Num rows: 20055959 Data size: 80223836 Basic stats: COMPLETE Column stats: PARTIAL Please refer to the previous Filter Operator [FIL_144]