You are viewing a plain text version of this content. The canonical link for it is here.
Posted to gitbox@hive.apache.org by "cxzl25 (via GitHub)" <gi...@apache.org> on 2023/07/10 07:01:44 UTC
[GitHub] [hive] cxzl25 opened a new pull request, #4474: HIVE-27493: HMS optimizes the SQL query index of getPartitionsByFilter method
cxzl25 opened a new pull request, #4474:
URL: https://github.com/apache/hive/pull/4474
### What changes were proposed in this pull request?
Add `PART_KEY_VAL` index to table `PARTITION_KEY_VALS`
### Why are the changes needed?
Improve performance
#### 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 index
```
[pool-4-thread-2] DEBUG metastore.MetaStoreDirectSql: Direct SQL query in 13.770586ms + 0.016063ms
```
### Does this PR introduce _any_ user-facing change?
No
### Is the change a dependency upgrade?
No
### How was this patch tested?
Validation in production environment
--
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.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org
Re: [PR] HIVE-27493: HMS optimizes the SQL query index of getPartitionsByFilter method [hive]
Posted by "sonarqubecloud[bot] (via GitHub)" <gi...@apache.org>.
sonarqubecloud[bot] commented on PR #4474:
URL: https://github.com/apache/hive/pull/4474#issuecomment-6218417176
## [](https://sonarcloud.io/dashboard?id=apache_hive&pullRequest=4474) **Quality Gate passed**
Issues
 [6 New issues](https://sonarcloud.io/project/issues?id=apache_hive&pullRequest=4474&issueStatuses=OPEN,CONFIRMED&sinceLeakPeriod=true)
 [0 Accepted issues](https://sonarcloud.io/project/issues?id=apache_hive&pullRequest=4474&issueStatuses=ACCEPTED)
Measures
 [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_hive&pullRequest=4474&issueStatuses=OPEN,CONFIRMED&sinceLeakPeriod=true)
 [0.0% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_hive&pullRequest=4474&metric=new_coverage&view=list)
 [0.0% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_hive&pullRequest=4474&metric=new_duplicated_lines_density&view=list)
<!-- sqra-placement-anchor -->
[See analysis details on SonarQube Cloud](https://sonarcloud.io/dashboard?id=apache_hive&pullRequest=4474)
--
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.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org
[GitHub] [hive] cxzl25 commented on a diff in pull request #4474: HIVE-27493: HMS optimizes the SQL query index of getPartitionsByFilter method
Posted by "cxzl25 (via GitHub)" <gi...@apache.org>.
cxzl25 commented on code in PR #4474:
URL: https://github.com/apache/hive/pull/4474#discussion_r1286844660
##########
standalone-metastore/metastore-server/src/main/sql/derby/hive-schema-4.0.0-beta-1.derby.sql:
##########
@@ -306,6 +306,8 @@ CREATE INDEX "APP"."CONSTRAINTS_PARENT_TBL_ID_INDEX" ON "APP"."KEY_CONSTRAINTS"(
CREATE INDEX "APP"."CONSTRAINTS_CONSTRAINT_TYPE_INDEX" ON "APP"."KEY_CONSTRAINTS"("CONSTRAINT_TYPE");
+CREATE INDEX "APP"."PARTITION_KEY_VALS_IDX" ON "APP"."PARTITION_KEY_VALS"("PART_KEY_VAL");
Review Comment:
Thanks, updated.
--
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.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org
[GitHub] [hive] deniskuzZ commented on pull request #4474: HIVE-27493: HMS optimizes the SQL query index of getPartitionsByFilter method
Posted by "deniskuzZ (via GitHub)" <gi...@apache.org>.
deniskuzZ commented on PR #4474:
URL: https://github.com/apache/hive/pull/4474#issuecomment-1693318741
@cxzl25 thanks for the patch!
Could you please update the sql script versions, cause beta1 was already released?
Patch LGTM, however, 1 question: as we already have PRIMARY KEY (`PART_ID`,`INTEGER_IDX`) why do we need an extra index on `PART_ID`?
--
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.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org
Re: [PR] HIVE-27493: HMS optimizes the SQL query index of getPartitionsByFilter method [hive]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] closed pull request #4474: HIVE-27493: HMS optimizes the SQL query index of getPartitionsByFilter method
URL: https://github.com/apache/hive/pull/4474
--
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.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org
[GitHub] [hive] sonarcloud[bot] commented on pull request #4474: HIVE-27493: HMS optimizes the SQL query index of getPartitionsByFilter method
Posted by "sonarcloud[bot] (via GitHub)" <gi...@apache.org>.
sonarcloud[bot] commented on PR #4474:
URL: https://github.com/apache/hive/pull/4474#issuecomment-1628529784
Kudos, SonarCloud Quality Gate passed! [](https://sonarcloud.io/dashboard?id=apache_hive&pullRequest=4474)
[](https://sonarcloud.io/project/issues?id=apache_hive&pullRequest=4474&resolved=false&types=BUG) [](https://sonarcloud.io/project/issues?id=apache_hive&pullRequest=4474&resolved=false&types=BUG) [0 Bugs](https://sonarcloud.io/project/issues?id=apache_hive&pullRequest=4474&resolved=false&types=BUG)
[](https://sonarcloud.io/project/issues?id=apache_hive&pullRequest=4474&resolved=false&types=VULNERABILITY) [](https://sonarcloud.io/project/issues?id=apache_hive&pullRequest=4474&resolved=false&types=VULNERABILITY) [0 Vulnerabilities](https://sonarcloud.io/project/issues?id=apache_hive&pullRequest=4474&resolved=false&types=VULNERABILITY)
[](https://sonarcloud.io/project/security_hotspots?id=apache_hive&pullRequest=4474&resolved=false&types=SECURITY_HOTSPOT) [](https://sonarcloud.io/project/security_hotspots?id=apache_hive&pullRequest=4474&resolved=false&types=SECURITY_HOTSPOT) [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_hive&pullRequest=4474&resolved=false&types=SECURITY_HOTSPOT)
[](https://sonarcloud.io/project/issues?id=apache_hive&pullRequest=4474&resolved=false&types=CODE_SMELL) [](https://sonarcloud.io/project/issues?id=apache_hive&pullRequest=4474&resolved=false&types=CODE_SMELL) [9 Code Smells](https://sonarcloud.io/project/issues?id=apache_hive&pullRequest=4474&resolved=false&types=CODE_SMELL)
[](https://sonarcloud.io/component_measures?id=apache_hive&pullRequest=4474&metric=coverage&view=list) No Coverage information
[](https://sonarcloud.io/component_measures?id=apache_hive&pullRequest=4474&metric=duplicated_lines_density&view=list) No Duplication information
--
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.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org
[GitHub] [hive] zhangbutao commented on a diff in pull request #4474: HIVE-27493: HMS optimizes the SQL query index of getPartitionsByFilter method
Posted by "zhangbutao (via GitHub)" <gi...@apache.org>.
zhangbutao commented on code in PR #4474:
URL: https://github.com/apache/hive/pull/4474#discussion_r1285159125
##########
standalone-metastore/metastore-server/src/main/sql/derby/hive-schema-4.0.0-beta-1.derby.sql:
##########
@@ -306,6 +306,8 @@ CREATE INDEX "APP"."CONSTRAINTS_PARENT_TBL_ID_INDEX" ON "APP"."KEY_CONSTRAINTS"(
CREATE INDEX "APP"."CONSTRAINTS_CONSTRAINT_TYPE_INDEX" ON "APP"."KEY_CONSTRAINTS"("CONSTRAINT_TYPE");
+CREATE INDEX "APP"."PARTITION_KEY_VALS_IDX" ON "APP"."PARTITION_KEY_VALS"("PART_KEY_VAL");
Review Comment:
If we change the latest version schema, we also should make sure the privious schema to upgrade smoothly.
That is to say, we need to add this change in `upgrade-4.0.0-alpha-2-to-4.0.0-beta-1.derby.sql`.
Maybe you can refer to HIVE-19605 to add the change.
--
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.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org
[GitHub] [hive] sonarcloud[bot] commented on pull request #4474: HIVE-27493: HMS optimizes the SQL query index of getPartitionsByFilter method
Posted by "sonarcloud[bot] (via GitHub)" <gi...@apache.org>.
sonarcloud[bot] commented on PR #4474:
URL: https://github.com/apache/hive/pull/4474#issuecomment-1670041483
Kudos, SonarCloud Quality Gate passed! [](https://sonarcloud.io/dashboard?id=apache_hive&pullRequest=4474)
[](https://sonarcloud.io/project/issues?id=apache_hive&pullRequest=4474&resolved=false&types=BUG) [](https://sonarcloud.io/project/issues?id=apache_hive&pullRequest=4474&resolved=false&types=BUG) [0 Bugs](https://sonarcloud.io/project/issues?id=apache_hive&pullRequest=4474&resolved=false&types=BUG)
[](https://sonarcloud.io/project/issues?id=apache_hive&pullRequest=4474&resolved=false&types=VULNERABILITY) [](https://sonarcloud.io/project/issues?id=apache_hive&pullRequest=4474&resolved=false&types=VULNERABILITY) [0 Vulnerabilities](https://sonarcloud.io/project/issues?id=apache_hive&pullRequest=4474&resolved=false&types=VULNERABILITY)
[](https://sonarcloud.io/project/security_hotspots?id=apache_hive&pullRequest=4474&resolved=false&types=SECURITY_HOTSPOT) [](https://sonarcloud.io/project/security_hotspots?id=apache_hive&pullRequest=4474&resolved=false&types=SECURITY_HOTSPOT) [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_hive&pullRequest=4474&resolved=false&types=SECURITY_HOTSPOT)
[](https://sonarcloud.io/project/issues?id=apache_hive&pullRequest=4474&resolved=false&types=CODE_SMELL) [](https://sonarcloud.io/project/issues?id=apache_hive&pullRequest=4474&resolved=false&types=CODE_SMELL) [0 Code Smells](https://sonarcloud.io/project/issues?id=apache_hive&pullRequest=4474&resolved=false&types=CODE_SMELL)
[](https://sonarcloud.io/component_measures?id=apache_hive&pullRequest=4474&metric=coverage&view=list) No Coverage information
[](https://sonarcloud.io/component_measures?id=apache_hive&pullRequest=4474&metric=duplicated_lines_density&view=list) No Duplication information
 The version of Java (11.0.8) you have used to run this analysis is deprecated and we will stop accepting it soon. Please update to at least Java 17.
Read more [here](https://docs.sonarcloud.io/appendices/scanner-environment/)
--
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.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org
[GitHub] [hive] sonarcloud[bot] commented on pull request #4474: HIVE-27493: HMS optimizes the SQL query index of getPartitionsByFilter method
Posted by "sonarcloud[bot] (via GitHub)" <gi...@apache.org>.
sonarcloud[bot] commented on PR #4474:
URL: https://github.com/apache/hive/pull/4474#issuecomment-1694514283
Kudos, SonarCloud Quality Gate passed! [](https://sonarcloud.io/dashboard?id=apache_hive&pullRequest=4474)
[](https://sonarcloud.io/project/issues?id=apache_hive&pullRequest=4474&resolved=false&types=BUG) [](https://sonarcloud.io/project/issues?id=apache_hive&pullRequest=4474&resolved=false&types=BUG) [0 Bugs](https://sonarcloud.io/project/issues?id=apache_hive&pullRequest=4474&resolved=false&types=BUG)
[](https://sonarcloud.io/project/issues?id=apache_hive&pullRequest=4474&resolved=false&types=VULNERABILITY) [](https://sonarcloud.io/project/issues?id=apache_hive&pullRequest=4474&resolved=false&types=VULNERABILITY) [0 Vulnerabilities](https://sonarcloud.io/project/issues?id=apache_hive&pullRequest=4474&resolved=false&types=VULNERABILITY)
[](https://sonarcloud.io/project/security_hotspots?id=apache_hive&pullRequest=4474&resolved=false&types=SECURITY_HOTSPOT) [](https://sonarcloud.io/project/security_hotspots?id=apache_hive&pullRequest=4474&resolved=false&types=SECURITY_HOTSPOT) [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_hive&pullRequest=4474&resolved=false&types=SECURITY_HOTSPOT)
[](https://sonarcloud.io/project/issues?id=apache_hive&pullRequest=4474&resolved=false&types=CODE_SMELL) [](https://sonarcloud.io/project/issues?id=apache_hive&pullRequest=4474&resolved=false&types=CODE_SMELL) [0 Code Smells](https://sonarcloud.io/project/issues?id=apache_hive&pullRequest=4474&resolved=false&types=CODE_SMELL)
[](https://sonarcloud.io/component_measures?id=apache_hive&pullRequest=4474&metric=coverage&view=list) No Coverage information
[](https://sonarcloud.io/component_measures?id=apache_hive&pullRequest=4474&metric=duplicated_lines_density&view=list) No Duplication information
 The version of Java (11.0.8) you have used to run this analysis is deprecated and we will stop accepting it soon. Please update to at least Java 17.
Read more [here](https://docs.sonarcloud.io/appendices/scanner-environment/)
--
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.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org
[GitHub] [hive] cxzl25 commented on pull request #4474: HIVE-27493: HMS optimizes the SQL query index of getPartitionsByFilter method
Posted by "cxzl25 (via GitHub)" <gi...@apache.org>.
cxzl25 commented on PR #4474:
URL: https://github.com/apache/hive/pull/4474#issuecomment-1669245047
> could you elaborate how the PR improves query? e.g. What's the type of quey
In the case where we use filter conditions to prune partitions, such as `get_partitions_by_filter` RPC.
It is mainly filtered with the field `PARTITION_KEY_VALS` of `PARTITION_KEY_VALS`, but it does not have an index, which leads to poor performance, especially if a table has more partitions.
```sql
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" = ?
```
--
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.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org
Re: [PR] HIVE-27493: HMS optimizes the SQL query index of getPartitionsByFilter method [hive]
Posted by "zhangbutao (via GitHub)" <gi...@apache.org>.
zhangbutao commented on PR #4474:
URL: https://github.com/apache/hive/pull/4474#issuecomment-6212355963
@cxzl25 Would you mind rebasing the PR? Thanks.
--
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.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org
[GitHub] [hive] zhangbutao commented on pull request #4474: HIVE-27493: HMS optimizes the SQL query index of getPartitionsByFilter method
Posted by "zhangbutao (via GitHub)" <gi...@apache.org>.
zhangbutao commented on PR #4474:
URL: https://github.com/apache/hive/pull/4474#issuecomment-1670526027
Ping @ayushtkn @dengzhhu653 @deniskuzZ who may be interested in this small but good change. Thanks.
--
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.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org
[GitHub] [hive] deniskuzZ commented on pull request #4474: HIVE-27493: HMS optimizes the SQL query index of getPartitionsByFilter method
Posted by "deniskuzZ (via GitHub)" <gi...@apache.org>.
deniskuzZ commented on PR #4474:
URL: https://github.com/apache/hive/pull/4474#issuecomment-1697238538
> > update the sql script versions
>
> Done
>
> > Patch LGTM, however, 1 question: as we already have PRIMARY KEY (`PART_ID`,`INTEGER_IDX`) why do we need an extra index on `PART_ID`?
>
> The index I added is `PART_KEY_VAL`, not `PART_ID`.
that's true, i was just wondering why we need `PART_ID` if it's already part of PK
--
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.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org
Re: [PR] HIVE-27493: HMS optimizes the SQL query index of getPartitionsByFilter method [hive]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #4474:
URL: https://github.com/apache/hive/pull/4474#issuecomment-1783950605
This pull request has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
Feel free to reach out on the dev@hive.apache.org list if the patch is in need of reviews.
--
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.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org
[GitHub] [hive] cxzl25 commented on pull request #4474: HIVE-27493: HMS optimizes the SQL query index of getPartitionsByFilter method
Posted by "cxzl25 (via GitHub)" <gi...@apache.org>.
cxzl25 commented on PR #4474:
URL: https://github.com/apache/hive/pull/4474#issuecomment-1694400812
> update the sql script versions
Done
> Patch LGTM, however, 1 question: as we already have PRIMARY KEY (`PART_ID`,`INTEGER_IDX`) why do we need an extra index on `PART_ID`?
The index I added is `PART_KEY_VAL`, not `PART_ID`.
--
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.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org
[GitHub] [hive] cxzl25 commented on pull request #4474: HIVE-27493: HMS optimizes the SQL query index of getPartitionsByFilter method
Posted by "cxzl25 (via GitHub)" <gi...@apache.org>.
cxzl25 commented on PR #4474:
URL: https://github.com/apache/hive/pull/4474#issuecomment-1697396769
> i was just wondering why we need `PART_ID` if it's already part of PK.
I checked that the index for `PART_ID` exists from very early versions, it is really not necessary.
```sql
PRIMARY KEY (`PART_ID`,`INTEGER_IDX`),
KEY `PARTITION_KEY_VALS_N49` (`PART_ID`),
```
> do we know what is the impact on write operations
If the discussion is about the impact of newly added indexes on writing, it can almost be ignored.
This index has been added in production for several years and it works fine.
--
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.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org
Re: [PR] HIVE-27493: HMS optimizes the SQL query index of getPartitionsByFilter method [hive]
Posted by "zhangbutao (via GitHub)" <gi...@apache.org>.
zhangbutao commented on PR #4474:
URL: https://github.com/apache/hive/pull/4474#issuecomment-6212224665
Reopen this PR. The [blog](https://share.note.youdao.com/ynoteshare/index.html?id=9705b4482b1d8147122d3eb58441fe74&type=note&_time=1792141495933) points out that after adding an index to the PART_KEY_VAL field of the table PARTITION_KEY_VALS , the metadata table query in MySQL went from 3.8 seconds to 0.001 seconds, a extremely significant improvement.
--
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.
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: gitbox-unsubscribe@hive.apache.org
For additional commands, e-mail: gitbox-help@hive.apache.org