PolarDB:Global indexes

How it works

A local index is built on each partition separately. Because each partition index only covers its own partition, it can enforce uniqueness only when the index key includes the partition key — which means unique constraints and primary keys must include the partition key.

A global index is a B-tree index built on the root table and mapped across all partitions. When the query planner uses a global index, it accesses matching rows across partitions in one operation rather than scanning each partition separately. This removes the partition-key requirement for unique constraints, primary keys, and foreign keys.

A global partitioned index is a global index that is itself partitioned — but using its own partitioning logic, independent of the data table's partitions. Use global partitioned indexes on large partitioned tables where you want to control the index partition size.

Choose an index type

When to use a global index:

• You need a unique constraint or primary key on a non-partition-key column.

• Your workload is point queries and single-row DML. Global indexes minimize the number of partition probes, which is the main overhead for partitioned-table point queries.

When to use a local index:

• You prioritize index manageability during partition maintenance operations.

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 | 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 UNIQUE INDEX m_city_id_idx_global ON measurement(city_id) GLOBAL;

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

Primary keys and foreign keys

Because global indexes support unique constraints on any column, any column of a partitioned table can serve as a primary key or foreign key.

When you define a primary key, PolarDB for PostgreSQL (Compatible with Oracle) automatically chooses the index type:

• If the primary key includes the partition key, a local index is created.

• If the primary key does not include the partition key, a global index is created.

The same logic applies to foreign keys: the referenced column must have a unique index, and if it is not the partition key, a global index is used.

To specify the index type explicitly, use:

ALTER TABLE <table_name> ADD PRIMARY KEY USING INDEX <index_name>;

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

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

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

Limitations

• Global indexes can only be created on parent tables of partitioned tables, not on non-partitioned tables or child tables. A global index is built on the root table and maps to partitions — neither non-partitioned tables nor individual child tables have that structure.

• Global indexes cannot be based on expressions. Only column references are supported.

• Global partitioned indexes can be range- or hash-partitioned only. List partitioning is not supported.

• Global partitioned indexes are partitioned on a left prefix of the index columns.

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

• Without the GLOBAL keyword, CREATE INDEX creates a local index by default.

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

Index scan types

Global indexes support eight scan types. All examples use the EXPLAIN output from queries against the measurement table.

Global Index Scan — point query using a global index:

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 — point query using a global partitioned index:

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 — query satisfied entirely from the index:

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 — range query returning multiple rows:

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)

The remaining scan types — Global Index Parallel Scan and Global Only Index Parallel Scan — follow the same pattern with parallel execution plans.

Performance considerations

The following benchmarks compare global indexes against local indexes and non-partitioned tables. Tests use pgbench with 80,000 rows. Transactions per second (TPS) varies by hardware environment; use these numbers for relative comparison only.