[jira] [Updated] (HIVE-21075) Metastore: Drop partition performance downgrade with Postgres DB

["ASF GitHub Bot (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/HIVE-21075?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

ASF GitHub Bot updated HIVE-21075:
----------------------------------
    Labels: pull-request-available  (was: )

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