PolarDB:Global indexes

Background information

A local index is an index on each partition of a partitioned table. The partitioning of local indexes is consistent with that of partitions. Thus, local indexes can serve as unique indexes only if they contain the partition key of the table.

A global index is an index on the root table and mapped to the table partitions. For a partitioned table, the system has to retrieve data from the physical partitions of the table, which are distributed across the storage. With a global index, however, the system can access data from multiple partitions in a collective manner based on the global index, instead of accessing the partitions separately.

A global index is a B-tree index built on the root table. They are not on the partitions. Global indexes can be created on fields other than the partition key of a partitioned table, and they support unique constraints.

Global partitioned indexes

Global partitioned indexes are often used on partitioned tables that contain large data volumes, and thus are more complicated to build. Global partitioned indexes are different from global indexes in that global partitioned indexes are not partitioned in the same logic as their data tables.

Create a global index

Syntax

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ]
    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass [ ( opclass_parameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ INCLUDE ( column_name [, ...] ) ]
    [ WITH ( storage_parameter [= value] [, ... ] ) ]
[ GLOBAL/LOCAL/global_partitioned_index ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]
global_partitioned_index:
    GLOBAL PARTITION BY {RANGE (column_list) (index_partitioning_clause) |
                            HASH (column_list) (hash_partitions_by_quantity)}

Create a global index for a partitioned table.

CREATE unique index m_city_id_idx_global ON measurement(city_id) global;

Create a global partitioned index for a partitioned table.

CREATE UNIQUE INDEX m_peaktemp_idx_global ON measurement(peaktemp) global
PARTITION BY range (peaktemp)
(
PARTITION m_peaktemp_idx_global_p1 values less than(1000),
PARTITION m_peaktemp_idx_global_p2 values less than(5000),
PARTITION m_peaktemp_idx_global_p3 values less than(MAXVALUE)
);

Notes

• The system creates a global index when GLOBAL is specified.

• If you do not specify the GLOBAL keyword, a local index is created by default.

• You can specify CONCURRENTLY in a CREATE INDEX statement that creates a global index.

• You cannot create global indexes for non-partitioned tables or the child tables of partitioned tables.

• You cannot create global indexes based on expressions.

• Global partitioned indexes can be range- or hash-partitioned. The syntax is similar to that for partitioning a data table.

• Global partitioned indexes cannot be list-partitioned.

• Global partitioned indexes can be created only for the parent tables of partitioned tables. They can be created based on the partition key columns of partitioned tables.

• Global partitioned indexes support globally unique constraints.

• A global partitioned index is partitioned on a left prefix of the index columns.

• Range-partitioned global indexes support ordered scanning.

• Hash-partitioned global indexes do not support ordered scanning.

• Global partitioned indexes can be used to implement partition pruning based on optimizers and executors.

Global Index Scan

Similar to a B-tree index, when the system performs a query on the index key, a global index will make for better performance due to index scans.

Global indexes support the following types of index scans:

• Global Index Scan

• Global Partitioned Index Scan

• Global Index Only Scan

• Global Partitioned Index Only Scan

• Global Index Bitmap Scan

• Global Partitioned Index Bitmap Scan

• Global Index Parallel Scan

• Global Only Index Parallel Scan

Examples

• Global Index Scan

  explain select * from measurement where city_id = 5;
                                             QUERY PLAN                                           
  ------------------------------------------------------------------------------------------------
   Global Index Scan using m_city_id_idx_global on measurement  (cost=0.12..8.14 rows=1 width=20)
     Index Cond: (city_id = 5)
  (2 rows)

• Global Partitioned Index Scan

  explain select * from measurement where peaktemp = 5;
                                                      QUERY PLAN                                                    
  ------------------------------------------------------------------------------------------------------------------
   Global Partitioned Index Scan(1/3) using m_peaktemp_idx_global on measurement  (cost=0.50..8.52 rows=1 width=20)
     Index Cond: (peaktemp = 5)
  (2 rows)

• Global Index Only Scan

  explain select city_id from measurement where city_id = 5;
                                               QUERY PLAN                                             
  ----------------------------------------------------------------------------------------------------
   Global Index Only Scan using m_city_id_idx_global on measurement  (cost=0.12..8.14 rows=1 width=4)
     Index Cond: (city_id = 5)
  (2 rows)

• Global Partitioned Index Only Scan

  explain select peaktemp from measurement where peaktemp = 5;
                                                        QUERY PLAN                                                      
  ----------------------------------------------------------------------------------------------------------------------
   Global Partitioned Index Only Scan(1/3) using m_peaktemp_idx_global on measurement  (cost=0.12..8.14 rows=1 width=4)
     Index Cond: (peaktemp = 5)
  (2 rows)

• Global Index Bitmap Scan

  explain select * from measurement where city_id > 5 and city_id < 10000;
                                             QUERY PLAN                                           
  ------------------------------------------------------------------------------------------------
   Append  (cost=1.03..14.47 rows=32 width=20)
     ->  Bitmap Heap Scan on measurement_y2023q1  (cost=1.03..3.58 rows=8 width=20)
           Recheck Cond: ((city_id > 5) AND (city_id < 10000))
           Filter: ((city_id > 5) AND (city_id < 10000))
           ->  Bitmap Global Index Scan on m_city_id_idx_global  (cost=0.00..4.13 rows=1 width=0)
                 Index Cond: ((city_id > 5) AND (city_id < 10000))
     ->  Bitmap Heap Scan on measurement_y2023q2  (cost=1.03..3.58 rows=8 width=20)
           Recheck Cond: ((city_id > 5) AND (city_id < 10000))
           Filter: ((city_id > 5) AND (city_id < 10000))
           ->  Bitmap Global Index Scan on m_city_id_idx_global  (cost=0.00..4.13 rows=1 width=0)
                 Index Cond: ((city_id > 5) AND (city_id < 10000))
     ->  Bitmap Heap Scan on measurement_y2023q3  (cost=1.03..3.58 rows=8 width=20)
           Recheck Cond: ((city_id > 5) AND (city_id < 10000))
           Filter: ((city_id > 5) AND (city_id < 10000))
           ->  Bitmap Global Index Scan on m_city_id_idx_global  (cost=0.00..4.13 rows=1 width=0)
                 Index Cond: ((city_id > 5) AND (city_id < 10000))
     ->  Bitmap Heap Scan on measurement_y2023q4  (cost=1.03..3.58 rows=8 width=20)
           Recheck Cond: ((city_id > 5) AND (city_id < 10000))
           Filter: ((city_id > 5) AND (city_id < 10000))
           ->  Bitmap Global Index Scan on m_city_id_idx_global  (cost=0.00..4.13 rows=1 width=0)
                 Index Cond: ((city_id > 5) AND (city_id < 10000))
  (21 rows)

• Global Partitioned Index Bitmap Scan

  explain select peaktemp from measurement where peaktemp > 5 and peaktemp< 1000;
                                                      QUERY PLAN                                                    
  ------------------------------------------------------------------------------------------------------------------
   Append  (cost=1.03..14.47 rows=32 width=4)
     ->  Bitmap Heap Scan on measurement_y2023q1  (cost=1.03..3.58 rows=8 width=4)
           Recheck Cond: ((peaktemp > 5) AND (peaktemp < 1000))
           Filter: ((peaktemp > 5) AND (peaktemp < 1000))
           ->  Bitmap Global Partitioned Index Scan(1/3) on m_peaktemp_idx_global  (cost=0.00..4.13 rows=1 width=0)
                 Index Cond: ((peaktemp > 5) AND (peaktemp < 1000))
     ->  Bitmap Heap Scan on measurement_y2023q2  (cost=1.03..3.58 rows=8 width=4)
           Recheck Cond: ((peaktemp > 5) AND (peaktemp < 1000))
           Filter: ((peaktemp > 5) AND (peaktemp < 1000))
           ->  Bitmap Global Partitioned Index Scan(1/3) on m_peaktemp_idx_global  (cost=0.00..4.13 rows=1 width=0)
                 Index Cond: ((peaktemp > 5) AND (peaktemp < 1000))
     ->  Bitmap Heap Scan on measurement_y2023q3  (cost=1.03..3.58 rows=8 width=4)
           Recheck Cond: ((peaktemp > 5) AND (peaktemp < 1000))
           Filter: ((peaktemp > 5) AND (peaktemp < 1000))
           ->  Bitmap Global Partitioned Index Scan(1/3) on m_peaktemp_idx_global  (cost=0.00..4.13 rows=1 width=0)
                 Index Cond: ((peaktemp > 5) AND (peaktemp < 1000))
     ->  Bitmap Heap Scan on measurement_y2023q4  (cost=1.03..3.58 rows=8 width=4)
           Recheck Cond: ((peaktemp > 5) AND (peaktemp < 1000))
           Filter: ((peaktemp > 5) AND (peaktemp < 1000))
           ->  Bitmap Global Partitioned Index Scan(1/3) on m_peaktemp_idx_global  (cost=0.00..4.13 rows=1 width=0)
                 Index Cond: ((peaktemp > 5) AND (peaktemp < 1000))
  (21 rows)

Global index as the primary key or foreign key

In PolarDB for PostgreSQL (Compatible with Oracle), any column of a partitioned table can be the primary key or a foreign key, thanks to the support of global indexes. This is because to be the primary key or a foreign key, a column must have a unique index, but a local index can serve as a unique index if the index key contains the partition key. If only local indexes are supported, the primary key must contain the partition key, and the foreign key must contain the partition key. Because global indexes are not subject to this limitation, any column can be the primary key or a foreign key.

You can specify the primary key when you are creating a partitioned table, and the system will decide whether to create local indexes or a global index based on the specified primary key. If the specified primary key contains the partition key, the system creates local indexes as the unique index. Otherwise, a global index is created to serve the purpose.

Examples

• Create a partitioned table with the primary key specified.

  -- local index
  CREATE TABLE pk_rel(a int primary key, b int, c int, d int) PARTITION BY RANGE (a);
  
  postgres=# \d pk_rel
                 Table "public.pk_rel"
   Column |  Type   | Collation | Nullable | Default 
  --------+---------+-----------+----------+---------
   a      | integer |           | not null | 
   b      | integer |           |          | 
   c      | integer |           |          | 
   d      | integer |           |          | 
  Partition key: RANGE (a)
  Indexes:
      "pk_rel_pkey" PRIMARY KEY, btree (a)
  Number of partitions: 0
  
  --- global index 
  CREATE TABLE pk_rel(a int, b int primary key, c int, d int) PARTITION BY RANGE (a);
  
  postgres=# \d pk_rel
                 Table "public.pk_rel"
   Column |  Type   | Collation | Nullable | Default 
  --------+---------+-----------+----------+---------
   a      | integer |           |          | 
   b      | integer |           | not null | 
   c      | integer |           |          | 
   d      | integer |           |          | 
  Partition key: RANGE (a)
  Indexes:
      "pk_rel_pkey" PRIMARY KEY, btree (b) GLOBAL
  Number of partitions: 0
  

  Note

  • You can also use the ALTER TABLE xxx ADD PRIMARY KEY USING INDEX xxx statement to specify the type of index that will be the primary key.

  • PolarDB for PostgreSQL (Compatible with Oracle) allows any column of a partitioned table to be a foreign key, but only if the column is a primary key or a unique index has been created for the column. If the column is the partition key, local indexes are created. If it isn't, a global index is created.

• Create a partitioned table with a foreign key specified

  CREATE TABLE fk_rel(a int, b int REFERENCES pk_rel(b), c int, d int) PARTITION BY RANGE (a);
  
  postgres=# \d fk_rel
                 Table "public.fk_rel"
   Column |  Type   | Collation | Nullable | Default 
  --------+---------+-----------+----------+---------
   a      | integer |           |          | 
   b      | integer |           |          | 
   c      | integer |           |          | 
   d      | integer |           |          | 
  Partition key: RANGE (a)
  Foreign-key constraints:
      "fk_rel_b_fkey" FOREIGN KEY (b) REFERENCES pk_rel(b)
  Number of partitions: 0

Performance tests

pgbench is used to generate 80,000 rows of data, and create a partitioned table and a non-partitioned table.

Point query performance on non-partition keys

TPC-B performance on non-partition keys

Conclusion

Global indexes can exponentially improve performance for point queries and DML statements.