PolarDB:Indexes in partitioned tables

Index types

Partitioned tables in PolarDB for PostgreSQL (Compatible with Oracle) support two index types.

Local indexes

In a partitioned table, local indexes correspond to partitions and have the same number and range as partitions. Each indexed partition is associated with a partition in the parent table. Therefore, all keys in an indexed partition only reference rows stored in a single partition. Therefore, indexed partitions are automatically synchronized with their associated partitions and are independent of each other.

You can create a local index by specifying LOCAL attributes. For a local index, partitions or subpartitions that are in the same number as those in the parent table are created. These partitions or subpartitions cover the same range as those in the parent table.

PolarDB for PostgreSQL (Compatible with Oracle) automatically maintains indexed partitions when partitions in the parent table are added, deleted, merged, or split, or when hash partitions or hash subpartitions are added or merged.

If a partition key column is a subset of an index column, you can create a unique local index to ensure that rows with the same index key always are mapped to the same partition.

Global indexes

A global index is a B- tree index that can also be partitioned independently of the parent table.

A globally indexed partition can point to all partitions in the parent table. A global index can also be partitioned. Its partition key must be the prefix of the index key.

Syntax

Create a local index

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON 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] [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]

Create a global index

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON table_name
    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass [ ( opclass_parameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ) GLOBAL 
    [ WITH ( storage_parameter [= value] [, ... ] ) ]
    [ TABLESPACE tablespace_name ]

Examples

Create a local index

Create local index title_idx on the title column of the films table.

CREATE UNIQUE INDEX title_idx ON films (title);

Create a global index

Create global index title_idx on the title column of the films table.

CREATE UNIQUE INDEX title_idx ON films (title) global;