[ https://issues.apache.org/jira/browse/HIVE-21075?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17351720#comment-17351720 ]
Oleksiy Sayankin edited comment on HIVE-21075 at 5/26/21, 11:23 AM: -------------------------------------------------------------------- Created [PR|https://github.com/apache/hive/pull/2323]. Solution is simple: {code} if (isPostgres()){ // use SELECT * FROM "SDS" "A0" WHERE "A0"."CD_ID" = $1 limit 1 } else { // use select count(1) from org.apache.hadoop.hive.metastore.model.MStorageDescriptor where (this.cd == inCD) } {code} was (Author: osayankin): Created [PR|https://github.com/apache/hive/pull/2323]. Solution is simple: {code} if (isPostgres()){ // use SELECT * FROM "SDS" "A0" WHERE "A0"."CD_ID" = $1 limit 1 } else { // use select count(1) from org.apache.hadoop.hive.metastore.model.MStorageDescriptor where (this.cd == inCD) } {code} > Metastore: Drop partition performance downgrade with Postgres DB > ---------------------------------------------------------------- > > Key: HIVE-21075 > URL: https://issues.apache.org/jira/browse/HIVE-21075 > Project: Hive > Issue Type: Bug > Components: Metastore > Affects Versions: 3.0.0 > Reporter: Yongzhi Chen > Assignee: Oleksiy Sayankin > Priority: Major > Labels: pull-request-available > Time Spent: 10m > Remaining Estimate: 0h > > In order to workaround oracle not supporting limit statement caused > performance issue, HIVE-9447 makes all the backend DB run select count(1) > from SDS where SDS.CD_ID=? to check if the specific CD_ID is referenced in > SDS table before drop a partition. This select count(1) statement does not > scale well in Postgres, and there is no index for CD_ID column in SDS table. > For a SDS table with with 1.5 million rows, select count(1) has average 700ms > without index, while in 10-20ms with index. But the statement before > HIVE-9447( SELECT * FROM "SDS" "A0" WHERE "A0"."CD_ID" = $1 limit 1) uses > less than 10ms . -- This message was sent by Atlassian Jira (v8.3.4#803005)