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

Global vs. local indexes

Add the GLOBAL or LOCAL keyword to a CREATE INDEX statement to control how the index is partitioned. If you omit both keywords, a local index is created by default.

Limitations

• Applies only to partitioned tables. You cannot create a global index on a non-partitioned table or directly on a child partition.

• No expression-based index keys. A global index maintains one structure across all partitions. Because PolarDB cannot evaluate an expression uniformly across different partition storage layouts, expression-based index keys are not supported.

• Partition key columns cannot be index keys. The partition key already governs data distribution across partitions. A global index on the same columns would not provide additional query routing benefit.

Benefits

• Unique constraints on non-partition key columns. A global index enforces uniqueness across the entire partitioned table, not just within a single partition. This makes it possible to define primary keys or unique indexes on columns that are not part of the partition key.

• Faster queries without a partition key filter. When a query does not include the partition key in its WHERE clause, the planner must otherwise scan all partitions. A global index on the queried column lets the planner do a direct index lookup instead.

• Parallel index creation. Use the cross-node parallel execution feature to speed up building B-tree global indexes. For details, see Accelerate index creation.

Example

The following example uses a range-partitioned table where partitions rotate on a monthly schedule. Each partition covers one month of data based on created_date.

Set up the partitioned table

CREATE TABLE partition_range (
    id integer,
    a int,
    b int,
    created_date timestamp without time zone
)
PARTITION BY RANGE (created_date);

CREATE TABLE partition_range_part01 (
    id integer,
    a int,
    b int,
    created_date timestamp without time zone
);
ALTER TABLE ONLY partition_range ATTACH PARTITION partition_range_part01 FOR VALUES FROM (MINVALUE) TO ('2020-01-01 00:00:00');

CREATE TABLE partition_range_part02 (
    id integer,
    a int,
    b int,
    created_date timestamp without time zone
);
ALTER TABLE ONLY partition_range ATTACH PARTITION partition_range_part02 FOR VALUES FROM ('2020-01-01 00:00:00') TO ('2020-02-01 00:00:00');

CREATE TABLE partition_range_part03 (
    id integer,
    a int,
    b int,
    created_date timestamp without time zone
);
ALTER TABLE ONLY partition_range ATTACH PARTITION partition_range_part03 FOR VALUES FROM ('2020-02-01 00:00:00') TO ('2020-03-01 00:00:00');

CREATE TABLE partition_range_part04 (
    id integer,
    a int,
    b int,
    created_date timestamp without time zone
);
ALTER TABLE ONLY partition_range ATTACH PARTITION partition_range_part04 FOR VALUES FROM ('2020-03-01 00:00:00') TO ('2020-04-01 00:00:00');

CREATE TABLE partition_range_part05 (
    id integer,
    a int,
    b int,
    created_date timestamp without time zone
);
ALTER TABLE ONLY partition_range ATTACH PARTITION partition_range_part05 FOR VALUES FROM ('2020-04-01 00:00:00') TO ('2020-05-01 00:00:00');

Before the global index: full-partition sequential scan

A query on id — a non-partition key column — triggers a sequential scan across every partition:

EXPLAIN (costs off) SELECT * FROM partition_range WHERE ID = 6;

                QUERY PLAN
------------------------------------------
 Append
   ->  Seq Scan on partition_range_part01
         Filter: (id = 6)
   ->  Seq Scan on partition_range_part02
         Filter: (id = 6)
   ->  Seq Scan on partition_range_part03
         Filter: (id = 6)
(7 rows)

Create the global index

CREATE UNIQUE INDEX idx_partition_range_global ON partition_range(id) GLOBAL;

After the global index: direct index lookup

The same query now uses a single global index scan:

EXPLAIN (costs off) SELECT * FROM partition_range WHERE ID = 6;

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

Partition maintenance

Global indexes remain valid when you attach or detach partitions. PolarDB updates the index automatically to reflect the new partition layout.

Attach a new partition

CREATE TABLE partition_range_part06 (
    id integer,
    a int,
    b int,
    created_date timestamp without time zone
);
ALTER TABLE ONLY partition_range ATTACH PARTITION partition_range_part06 FOR VALUES FROM ('2020-05-01 00:00:00') TO ('2020-06-01 00:00:00');

Detach an old partition

ALTER TABLE partition_range DETACH PARTITION partition_range_part01;

What's next

• Accelerate index creation — use cross-node parallel execution to build B-tree global indexes faster