[HIVE-27493] HMS optimizes the SQL query index of getPartitionsByFilter method

• Type: Improvement
• Status: In Progress
• Priority: Major
• Resolution: Unresolved
• Component/s: Metastore, Standalone Metastore
• Labels: pull-request-available
• Assignee: dzcxzl
• Reporter: dzcxzl
• Created: 10/Jul/23 06:57
• Updated: 05/Nov/23 00:20

Description

Current

[pool-4-thread-1] DEBUG metastore.MetaStoreDirectSql: Direct SQL query in 352.763678ms + 0.026622ms, the query is [select "PARTITIONS"."PART_ID" from "PARTITIONS"  inner join "TBLS" on "PARTITIONS"."TBL_ID" = "TBLS"."TBL_ID"     and "TBLS"."TBL_NAME" = ?   inner join "DBS" on "TBLS"."DB_ID" = "DBS"."DB_ID"      and "DBS"."NAME" = ? inner join "PARTITION_KEY_VALS" "FILTER0" on "FILTER0"."PART_ID" = "PARTITIONS"."PART_ID" and "FILTER0"."INTEGER_IDX" = 0 inner join "PARTITION_KEY_VALS" "FILTER1" on "FILTER1"."PART_ID" = "PARTITIONS"."PART_ID" and "FILTER1"."INTEGER_IDX" = 1 where ( (("FILTER0"."PART_KEY_VAL" = ?) and ("FILTER1"."PART_KEY_VAL" = ?)) )] 

Add PART_KEY_VAL index to table PARTITION_KEY_VALS

[pool-4-thread-2] DEBUG metastore.MetaStoreDirectSql: Direct SQL query in 13.770586ms + 0.016063ms

 

Issue Links

• links to: GitHub Pull Request #4474