[HIVE-8000] Hive query plan suboptimal on select from partitioned table with partition id as filters

• Type: Bug
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Component/s: Metastore
• Assignee: Unassigned
• Reporter: Chu Tong
• Created: 05/Sep/14 05:20
• Updated: 05/Sep/14 05:20

Description

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)) )