Global Index for partitioned tables

[mjonss]

Enhancement

Development tasks for: Make GLOBAL INDEX General Available

Stage 1 - full implementation, but Experimental

• [ ] Change syntax, to enable (default) LOCAL index, and (explicit) GLOBAL index, #52994
• [x] #48825
• [ ] Add tests for all index access plans
  • [ ] Create issues for handling the index access plans currently not supporting Global indexes (first disable the specific plan, then add support).
• [ ] Add support for check index/check table
• [ ] Add tests for all write transactions (select lock, commit, rollback, different transaction modes, different transaction levels) * (insert, update, delete, select for update, single/multi statement transaction).

Stage 2 - Stabilise / General Available

• [ ] Fix reported bugs
• [ ] Adjust syntax if needed
• [ ] Add more tests if needed
• [ ] Enable by default
• [ ] Remove experimental flag

[Defined2014]

Maybe support for indexMerge and UnionScan can be added, it seems we are not considering global indexes in those related codes.

Also we need to consider the support of the planner part. Like different cost between global index and local index in some executors.

[311ybb]

please make global index General Available faster, we need it !

[vonwind]

This is an urgently needed feature.

[mjonss]

ADD PARTITION is already supported, it uses the same logic as for TRUNCATE PARTITION, i.e. reading from the Global Index will filter away dropped/added partitions, so it will only show index entries from partitions the transaction/schema version sees. Currently filtered here.

[mjonss]

@Defined2014, for the global index, should there be some way to specify placement rules? I don't think there are any for normal indexes?

[Defined2014]

@Defined2014, for the global index, should there be some way to specify placement rules? I don't think there are any for normal indexes?

I think it will follow table's placement rules and it is in line with my expectations. What do you think @mjonss ? And we could add some test cases for this.

[mjonss]

I think it will follow table's placement rules and it is in line with my expectations. What do you think @mjonss ? And we could add some test cases for this.

@Defined2014 I expect the same, i.e. it will follow the table level's placement rules, so we should probably document it and add a test for it.

[Defined2014]

I think it will follow table's placement rules and it is in line with my expectations. What do you think @mjonss ? And we could add some test cases for this.

@Defined2014 I expect the same, i.e. it will follow the table level's placement rules, so we should probably document it and add a test for it.

I think the exists test cases already covered this situation(by check the key ranges for pd.Rule). And push a doc commit for it.