Details
-
New Feature
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
Description
In other RDBMS, there is the concept of GLOBAL or LOCAL indexes on partitioned tables.
MariaDB currently has LOCAL indexes, which are local to each partition in a table.
A GLOBAL index on the other hand is a one-to-many relationship, allowing one index partition to map to many table partitions and avoid the scanning behavior when partition keys are not in a query.
Implementations on other databases:
http://www.dba-oracle.com/t_global_local_partitioned_index.htm
Good justification on why postgres (and MariaDB) should have it: https://www.postgresql.org/message-id/CALtqXTcurqy1PKXzP9XO%3DofLLA5wBSo77BnUnYVEZpmcA3V0ag%40mail.gmail.com
Attachments
Issue Links
- is part of
-
-
- Stalled
-
Activity
My primary concerns:
1) Global indexes allow for fast partition pruning. This means we no longer have the UNION ALL type of behavior for queries without partition keys and can rely on indexed columns to choose the correct partitions and provide a significant boost for read performance.
2) This makes implementing foreign keys on partitioned tables better
Sergei, I thought UNIQUE was not possible unless the "partition key" was included in the UNIQUEness constraint.
I agree that a global index is helpful for pruning. For example, a "range" over a HASH partitioning must now check all partitions; a global index would make pruning easy and efficient. I believe that HASH never provides performance benefits. I don't think that adding global indexing will change my mind. Can anyone provide a performance benefit relative to removing PARTITION BY HASH from the table (and adjusting the indexes if necessary)?
Could you please add some justification here? Note that we already have UNIQUE constraint over all partitions.