Chu Tong created HIVE-8000:
------------------------------
Summary: Hive query plan suboptimal on select from partitioned
table with partition id as filters
Key: HIVE-8000
URL: https://issues.apache.org/jira/browse/HIVE-8000
Project: Hive
Issue Type: Bug
Components: Metastore
Reporter: Chu Tong
Priority: Minor
When it comes to issue "SELECT * FROM test where id = 100 OR id = 101 OR id =
102" on table test with a large number of partitions, most of the load on the
metastore comes from this query:
select "PARTITIONS"."PART_ID" from "PARTITIONS" inner join "TBLS" on
"PARTITIONS"."TBL_ID" = "TBLS"."TBL_ID" and "TBLS"."TBL_NAME" = @P0 inner join
"DBS" on "TBLS"."DB_ID" = "DBS"."DB_ID" and "DBS"."NAME" = @P1 inner join
"PARTITION_KEY_VALS" "FILTER0" on "FILTER0"."PART_ID" = "PARTITIONS"."PART_ID"
and "FILTER0"."INTEGER_IDX" = 0 where ( ( (((case when "TBLS"."TBL_NAME" = @P2
and "DBS"."NAME" = @P3 then cast("FILTER0"."PART_KEY_VAL" as decimal(21,0))
else null end) = @P4) or ((case when "TBLS"."TBL_NAME" = @P5 and "DBS"."NAME" =
@P6 then cast("FILTER0"."PART_KEY_VAL" as decimal(21,0)) else null end) = @P7))
or ((case when "TBLS"."TBL_NAME" = @P8 and "DBS"."NAME" = @P9 then
cast("FILTER0"."PART_KEY_VAL" as decimal(21,0)) else null end) = @P10)) )
',N'test',N'default',N'test',N'default',250000,N'test',N'default',200000,N'test',N'default',200001
>From the query plan, it shows several index scans can be done in seeks by
>pushing down the filter operator on PART_KEY_VAL early by putting in
>(FILTER0.PART_KEY_VAL in (@p4,@P7,@P10)) before case statements.
And resulting query becomes:
select "PARTITIONS"."PART_ID" from "PARTITIONS" inner join "TBLS" on
"PARTITIONS"."TBL_ID" = "TBLS"."TBL_ID" and "TBLS"."TBL_NAME" = @P0 inner join
"DBS" on "TBLS"."DB_ID" = "DBS"."DB_ID" and "DBS"."NAME" = @P1 inner join
"PARTITION_KEY_VALS" "FILTER0" on "FILTER0"."PART_ID" = "PARTITIONS"."PART_ID"
and "FILTER0"."INTEGER_IDX" = 0 where ( (("TBLS"."TBL_NAME" = @P2 and
"DBS"."NAME" = @P3 and cast("FILTER0"."PART_KEY_VAL" as decimal(21,0)) = @P4)
or ("TBLS"."TBL_NAME" = @P5 and "DBS"."NAME" = @P6 and
cast("FILTER0"."PART_KEY_VAL" as decimal(21,0)) = @P7)) )
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
