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

["Chu Tong (JIRA)" <ji...@apache.org>]

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)