dengzhhu653 commented on code in PR #4744:
URL: https://github.com/apache/hive/pull/4744#discussion_r1439310735
##########
standalone-metastore/metastore-server/src/main/sql/postgres/hive-schema-4.0.0-beta-2.postgres.sql:
##########
@@ -1195,7 +1188,7 @@ CREATE INDEX "PART_PRIVS_N49" ON "PART_PRIVS" USING btree
("PART_ID");
-- Name: PCS_STATS_IDX; Type: INDEX; Schema: public; Owner: hiveuser;
Tablespace:
--
-CREATE INDEX "PCS_STATS_IDX" ON "PART_COL_STATS" USING btree
("DB_NAME","TABLE_NAME","COLUMN_NAME","PARTITION_NAME","CAT_NAME");
+CREATE INDEX "PCS_STATS_IDX" ON "PART_COL_STATS" USING btree
("PART_ID","COLUMN_NAME");
Review Comment:
Will this index be cooperated into the multi-join plan? tested it on
Postgres, looks it doesn't use it:
```
explain select * from "PART_COL_STATS" inner join "PARTITIONS" on
"PARTITIONS"."PART_ID"="PART_COL_STATS"."PART_ID" inner join "TBLS" on
"PARTITIONS"."TBL_ID" = "TBLS"."TBL_ID" inner join "DBS" on "DBS"."DB_ID" =
"TBLS"."DB_ID" where "DBS"."CTLG_NAME" = 'hive' and "DBS"."NAME" = 'test_dbl'
and "TBLS"."TBL_NAME" = 'acid_3' and "PART_COL_STATS"."COLUMN_NAME" in ('a');
```
```
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.55..24.75 rows=1 width=6505)
-> Nested Loop (cost=0.42..24.50 rows=1 width=5325)
-> Nested Loop (cost=0.28..16.33 rows=1 width=4761)
Join Filter: ("TBLS"."DB_ID" = "DBS"."DB_ID")
-> Index Scan using "UNIQUETABLE" on "TBLS"
(cost=0.14..8.16 rows=1 width=1465)
Index Cond: (("TBL_NAME")::text = 'acid_3'::text)
-> Index Scan using "UNIQUE_DATABASE" on "DBS"
(cost=0.14..8.16 rows=1 width=3296)
Index Cond: ((("NAME")::text = 'test_dbl'::text) AND
(("CTLG_NAME")::text = 'hive'::text))
-> Index Scan using "PARTITIONS_N49" on "PARTITIONS"
(cost=0.14..8.16 rows=1 width=564)
Index Cond: ("TBL_ID" = "TBLS"."TBL_ID")
-> Index Scan using "PART_COL_STATS_N49" on "PART_COL_STATS"
(cost=0.13..0.24 rows=1 width=1180)
Index Cond: ("PART_ID" = "PARTITIONS"."PART_ID")
Filter: (("COLUMN_NAME")::text = 'a'::text)
```
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
