[
https://issues.apache.org/jira/browse/HIVE-21075?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16730691#comment-16730691
]
Yongzhi Chen commented on HIVE-21075:
-------------------------------------
[~karthik.manamcheri], we can add the index, but adding the index alone can not
speed up the count(1) query fast enough to match the performance of query
SELECT * FROM "SDS" "A0" WHERE "A0"."CD_ID" = $1 limit 1 .
> 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
> Priority: Major
>
> 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
(v7.6.3#76005)
