Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Partition Pruning doesn't work with a query on a subquery with union all

Partition Pruning doesn't work with a query on a subquery with union all

New Contributor

Hi,

When a SELECT with WHERE on partition columns is run on a subquery with Union all. Below are the table DDLs.

CREATE TABLE IF NOT EXISTS t4( a int, b int) PARTITIONED BY ( `y` int, `m` int, `d` int) ;

CREATE TABLE IF NOT EXISTS t5( a int, b int) PARTITIONED BY ( `y` int, `m` int, `d` int) ;

Query:

=====

select a,b from

( select * from t4

union all

select * from t5) tmp

where y = 2018 and m = 4 and d = 12; 0:

In the explain for above query, the partition columns are used as the Filter Predicates NOT for partition pruning.

So, its doing a full table scan and then applying the filters, so the query is very slow.

Any suggestions on how can I fix it please? Thanks.


Query Explain:

===========

explain

select a,b from

( select * from t4

union all

select * from t5) tmp

where y = 2018 and m = 4 and d = 12; 0:

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+ | Explain | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+ | Plan not optimized by CBO. | | | | Vertex dependency in root stage | | Map 1 <- Union 2 (CONTAINS) | | Map 3 <- Union 2 (CONTAINS) | | | | Stage-0 | | Fetch Operator | | limit:-1 | | Stage-1 | | Union 2 | | |<-Map 3 [CONTAINS] vectorized | | | File Output Operator [FS_4205] | | | compressed:false | | | Statistics:Num rows: 2 Data size: 0 Basic stats: PARTIAL 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 [OP_4204] | | | outputColumnNames:["_col0","_col1"] | | | Statistics:Num rows: 2 Data size: 0 Basic stats: PARTIAL Column stats: PARTIAL | | | Select Operator [OP_4203] | | | outputColumnNames:["_col0","_col1"] | | | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: PARTIAL | | | Filter Operator [FIL_4202] | | | predicate:(((y = 2018) and (m = 4)) and (d = 12)) (type: boolean) | | | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: PARTIAL | | | TableScan [TS_4172] | | | alias:t5 | | | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: PARTIAL | | |<-Map 1 [CONTAINS] vectorized | | File Output Operator [FS_4201] | | compressed:false | | Statistics:Num rows: 2 Data size: 0 Basic stats: PARTIAL 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 [OP_4200] | | outputColumnNames:["_col0","_col1"] | | Statistics:Num rows: 2 Data size: 0 Basic stats: PARTIAL Column stats: PARTIAL | | Select Operator [OP_4199] | | outputColumnNames:["_col0","_col1"] | | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: PARTIAL | | Filter Operator [FIL_4198] | | predicate:(((y = 2018) and (m = 4)) and (d = 12)) (type: boolean) | | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: PARTIAL | | TableScan [TS_4170] | | alias:t4 | | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: PARTIAL | | | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+

Don't have an account?
Coming from Hortonworks? Activate your account here