Re: Proposal: Global Index

> 2021年1月7日 23:04，Robert Haas <robertmhaas(at)gmail(dot)com> 写道：
>
> On Thu, Jan 7, 2021 at 4:44 AM 曾文旌 <wenjing(dot)zwj(at)alibaba-inc(dot)com> wrote:
>> I've been following this topic for a long time. It's been a year since the last response.
>> It was clear that our customers wanted this feature as well, and a large number of them mentioned it.
>>
>> So, I wish the whole feature to mature as soon as possible.
>> I summarized the scheme mentioned in the email and completed the POC patch(base on PG_13).
>
> You need to summarize the basic design choices you've made here. Like,
> what did you do about the fact that TIDs have to be 6 bytes?

These are the basic choices, and most of them come from discussions in previous emails.

1 Definition of global index
Obviously, we need to expand Index address info(CTID) to include child table info in GlobalIndexTuple.

1.1 As mentioned in the previous email, Bruce suggested having the OID
instead of relfilenode as relfilenode can be duplicated across tablespaces.
I agree with that.

1.2 And Heikki pointed me to include heap specific information using the INCLUDE keyword so that heap information
is stored with each index node as data.

So ,In POC stage, I choose use INCLUDE keyword to INCLUDE the tableoid of global index. This will add 4 bytes to each IndexTuple.

Considering that if a single partitioned table does not exceed 65535 child tables, perhaps two bytes for tracking which child table the data belongs to is sufficient.

2. Maintenance of global index by partition table DML.
The DML of each child table of the partitioned table needs to maintain the global index on the partitioned table.

3. Global index scan
Planner:
Processes predicate on the primary partition, generating paths and plans for the global index.
The cost model of the global index may need to be considered. We need to make the global index or the local index selected in their respective advantageous scenarios.

Executer:
The index scan get indextup, get the tableoid from indextup, and verify the visibility of the data in the child table.
If a child table is DETACH, then the index item of this table is ignored during the index scan until VACUUM finishes cleaning up the global index.

4. Vacuum partition table maintains global index.
Old data in the global index also needs to be cleaned up, and vaccum is suitable for it.
Each child table in VACUUM, while vacuuming its own index, also vacuums the global index on the partitioned table.

5. Other
The global index indexes all of the child tables, which makes the global index large and has many levels.
Follow the technical route, The partitioned indexes are a further target.

This is my basic idea for implementing global index.
Looking forward to your feedback.

Thanks!

Wenjing

>
> --
> Robert Haas
> EDB: http://www.enterprisedb.com