Global indexes

Accelerate non-partition key queries

Step 1: Create a partitioned table.

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');

Step 2: Query without an index.

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

All subpartitions are scanned because partition pruning cannot apply to a non-partition key:

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)

Step 3: Add a local index and query again.

CREATE INDEX partition_range_idx_local ON partition_range(id);

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

All subpartition indexes are still scanned, because local indexes are created on each subpartition individually:

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)

Step 4: Add a global index and query again.

CREATE INDEX partition_range_idx_global ON partition_range(id) GLOBAL;

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

The database uses the global index to locate the target subpartition directly — 7 plan nodes reduced to 2:

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

Enforce a unique constraint on a non-partition key

The partition key for partition_range is created_date, but the column that requires a unique constraint is id.

In native PostgreSQL, creating a UNIQUE index on a non-partition key column fails:

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.

PolarDB for PostgreSQL removes this restriction. By default, a UNIQUE constraint on a non-partition key is automatically enforced as a global constraint backed by a global index. This behavior is controlled by the polar_pk_in_non_partition_column_mode parameter:

To create the unique global index explicitly:

CREATE UNIQUE INDEX partition_range_id_unique_idx ON partition_range(id) GLOBAL;

Point query performance (TPS)

PS = Prepared Statement

TPC-B performance (TPS)

TPC-B includes both point queries and Data Manipulation Language (DML) statements.

Summary

Global indexes improve point query and DML performance on partitioned tables by an order of magnitude compared to local indexes.