PolarDB:Global indexes

Applicability

This feature is supported on PolarDB for PostgreSQL clusters that run PostgreSQL 14 with minor engine version 2.0.14.9.15.0 or later.

Limits

• You can still ATTACH or DETACH subpartitions from a partitioned table that has a global index.

• Specify the GLOBAL keyword in the `CREATE INDEX` statement to create a global index. If you do not specify this keyword, a local index is created by default.

• You can create global indexes concurrently by specifying the CONCURRENTLY keyword.

• You cannot create global indexes on non-partitioned tables or on child tables that contain subpartitions.

• Global indexes do not support expression indexes.

• You cannot create global indexes on the partition key columns of a partitioned table.

Syntax

You can create a global index.

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 ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]

Examples

Accelerate non-partition key queries

1. You can create a partitioned table with a time column as the partition key.

   CREATE TABLE partition_range (
       id INT,
       a INT,
       b INT,
       created_date TIMESTAMP WITHOUT TIME ZONE
   ) PARTITION BY RANGE (created_date);
   
   CREATE TABLE partition_range_part01 PARTITION OF partition_range FOR VALUES FROM (MINVALUE) TO ('2020-01-01 00:00:00');
   CREATE TABLE partition_range_part02 PARTITION OF partition_range FOR VALUES FROM ('2020-01-01 00:00:00') TO ('2020-02-01 00:00:00');
   CREATE TABLE partition_range_part03 PARTITION OF partition_range FOR VALUES FROM ('2020-02-01 00:00:00') TO ('2020-03-01 00:00:00');

2. You can query the partitioned table based on a non-partition key condition.

   EXPLAIN (COSTS OFF) SELECT * FROM partition_range WHERE id = 1;

   The result shows that all subpartitions are scanned. The partition pruning feature cannot be used.

                           QUERY PLAN
   ------------------------------------------------------------
    Append
      ->  Seq Scan on partition_range_part01 partition_range_1
            Filter: (id = 1)
      ->  Seq Scan on partition_range_part02 partition_range_2
            Filter: (id = 1)
      ->  Seq Scan on partition_range_part03 partition_range_3
            Filter: (id = 1)
   (7 rows)

3. You can create a local index on the partitioned table and run the query again.

   CREATE INDEX partition_range_idx_local ON partition_range(id);
   
   EXPLAIN (COSTS OFF) SELECT * FROM partition_range WHERE id = 1;

   The result shows that the local indexes of all subpartitions are still scanned because local indexes are created on each subpartition.

                                               QUERY PLAN
   --------------------------------------------------------------------------------------------------
    Append
      ->  Index Scan using partition_range_part01_id_idx on partition_range_part01 partition_range_1
            Index Cond: (id = 1)
      ->  Index Scan using partition_range_part02_id_idx on partition_range_part02 partition_range_2
            Index Cond: (id = 1)
      ->  Index Scan using partition_range_part03_id_idx on partition_range_part03 partition_range_3
            Index Cond: (id = 1)
   (7 rows)

4. You can use the GLOBAL keyword to create a global index on the partitioned table and run the query again.

   CREATE INDEX partition_range_idx_global ON partition_range(id) GLOBAL;
   
   EXPLAIN (COSTS OFF) SELECT * FROM partition_range WHERE id = 1;

   The result shows that the database uses the global index to directly find the subpartition that contains the data.

                                 QUERY PLAN
   -----------------------------------------------------------------------
    Global Index Scan using partition_range_idx_global on partition_range
      Index Cond: (id = 1)
   (2 rows)

Unique constraint for non-partition keys

Use the same partitioned table from the preceding example. The partition key is created_date, but the column that requires a unique constraint is id.

CREATE UNIQUE INDEX partition_range_id_unique_idx ON partition_range(id);
ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  UNIQUE constraint on table "partition_range" lacks column "created_date" which is part of the partition key.

However, this restriction does not apply when you add a UNIQUE constraint to a global index, as shown below.

CREATE UNIQUE INDEX partition_range_id_unique_idx ON partition_range(id) GLOBAL;

Performance test

You can use pgbench to generate data with a scale factor of 80000 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 improve the performance of point queries and DML statements on partitioned tables by an order of magnitude.