[ https://issues.apache.org/jira/browse/HIVE-21075?focusedWorklogId=602960&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-602960 ]
ASF GitHub Bot logged work on HIVE-21075: ----------------------------------------- Author: ASF GitHub Bot Created on: 27/May/21 13:04 Start Date: 27/May/21 13:04 Worklog Time Spent: 10m Work Description: oleksiy-sayankin commented on pull request #2323: URL: https://github.com/apache/hive/pull/2323#issuecomment-849617289 **MySQL** MySQL does support limit. See [here](https://www.mysqltutorial.org/mysql-limit.aspx) > SELECT > select_list > FROM > table_name > LIMIT [offset,] row_count; **MS SQL** Does not support limit. See [here](https://stackoverflow.com/questions/603724/how-to-implement-limit-with-sql-server). There is an alternative > SELECT TOP 10 * FROM (SELECT TOP 20 FROM Table ORDER BY Id) ORDER BY Id DESC **ORACLE** Does not support limits. See [here](https://stackoverflow.com/questions/470542/how-do-i-limit-the-number-of-rows-returned-by-an-oracle-query-after-ordering). There is an alternative: > SELECT * > FROM sometable > ORDER BY name > OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY So I can re-fix this way: if (dbProduct.isPOSTGRES() || dbProduct.isMYSQL()) { // use limit clause here } to make it work faster on MySQL. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org Issue Time Tracking ------------------- Worklog Id: (was: 602960) Time Spent: 2h 20m (was: 2h 10m) > 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: 2h 20m > 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)