Partitioned Indexes

There are three types of indexes on a partitioned table:

Global non-partitioned index
Global partitioned index
Local partitioned index

Currently, GaussDB supports the global non-partitioned index and local partitioned index.

Figure 1 Global non-partitioned index

Figure 2 Global partitioned index

Figure 3 Local partitioned index

Constraints

Partitioned indexes are classified into local indexes and global indexes. A local index binds to a specific partition, and a global index corresponds to the entire partitioned table.
If the constraint key of the unique constraint and primary key constraint contains all partition keys, a local index is created. Otherwise, a global index is created.
When creating a local index, you can use the FOR { partition_name | ( partition_value [, ...] ) } clause to specify that the local index is created on a single partition. This type of index does not take effect on other partitions and will not be automatically created on new partitions. Note that the sparsely partitioned index query path can be generated only for plans that are statically pruned to a single partition.

If the query statement involves multiple target partitions, you are advised to use the global index. Otherwise, you are advised to use the local index. However, note that the global index has extra overhead in the partition maintenance syntax.

Examples

Create a table.

gaussdb=# CREATE TABLE web_returns_p2
(
    ca_address_sk INTEGER NOT NULL ,
    ca_address_id CHARACTER(16) NOT NULL ,
    ca_street_number CHARACTER(10) ,
    ca_street_name CHARACTER VARYING(60) ,
    ca_street_type CHARACTER(15) ,
    ca_suite_number CHARACTER(10) ,
    ca_city CHARACTER VARYING(60) ,
    ca_county CHARACTER VARYING(30) ,
    ca_state CHARACTER(2) ,
    ca_zip CHARACTER(10) ,
    ca_country CHARACTER VARYING(20) ,
    ca_gmt_offset NUMERIC(5,2) ,
    ca_location_type CHARACTER(20)
)
PARTITION BY RANGE (ca_address_sk)
(
    PARTITION P1 VALUES LESS THAN(5000),
    PARTITION P2 VALUES LESS THAN(10000),
    PARTITION P3 VALUES LESS THAN(15000),
    PARTITION P4 VALUES LESS THAN(20000),
    PARTITION P5 VALUES LESS THAN(25000),
    PARTITION P6 VALUES LESS THAN(30000),
    PARTITION P7 VALUES LESS THAN(40000),
    PARTITION P8 VALUES LESS THAN(MAXVALUE)
)
ENABLE ROW MOVEMENT;

Create an index.

Create the local index tpcds_web_returns_p2_index1 without specifying the partition name.
```
gaussdb=# CREATE INDEX tpcds_web_returns_p2_index1 ON web_returns_p2 (ca_address_id) LOCAL;
```
If the following information is displayed, the creation is successful:
```
CREATE INDEX
```

Create the local index tpcds_web_returns_p2_index2 with the specified partition name.

gaussdb=# CREATE TABLESPACE example2 LOCATION '/home/omm/example2';
CREATE TABLESPACE example3 LOCATION '/home/omm/example3';
CREATE TABLESPACE example4 LOCATION '/home/omm/example4';

gaussdb=# CREATE INDEX tpcds_web_returns_p2_index2 ON web_returns_p2 (ca_address_sk) LOCAL
(
    PARTITION web_returns_p2_P1_index,
    PARTITION web_returns_p2_P2_index TABLESPACE example3,
    PARTITION web_returns_p2_P3_index TABLESPACE example4,
    PARTITION web_returns_p2_P4_index,
    PARTITION web_returns_p2_P5_index,
    PARTITION web_returns_p2_P6_index,
    PARTITION web_returns_p2_P7_index,
    PARTITION web_returns_p2_P8_index
) TABLESPACE example2;

If the following information is displayed, the creation is successful:

CREATE INDEX

Create the global index tpcds_web_returns_p2_global_index for a partitioned table.
```
gaussdb=# CREATE INDEX tpcds_web_returns_p2_global_index ON web_returns_p2 (ca_street_number) GLOBAL;
```
If the following information is displayed, the creation is successful:
```
CREATE INDEX
```

Create a sparsely partitioned index for a partition.

Specify the partition name.

gaussdb=# CREATE INDEX tpcds_web_returns_for_p1 ON web_returns_p2 (ca_address_id) LOCAL(partition ind_part for p1);

Specify the value of a partition key.

gaussdb=# CREATE INDEX tpcds_web_returns_for_p2 ON web_returns_p2 (ca_address_id) LOCAL(partition ind_part for (5000));

If the following information is displayed, the creation is successful:

CREATE INDEX

Modify the tablespace of an index partition.
- Change the tablespace of index partition web_returns_p2_P2_index to example1.
```
gaussdb=# ALTER INDEX tpcds_web_returns_p2_index2 MOVE PARTITION web_returns_p2_P2_index TABLESPACE example1;
```
  If the following information is displayed, the modification is successful:
```
ALTER INDEX
```
- Change the tablespace of index partition web_returns_p2_P3_index to example2.
```
gaussdb=# ALTER INDEX tpcds_web_returns_p2_index2 MOVE PARTITION web_returns_p2_P3_index TABLESPACE example2;
```
  If the following information is displayed, the modification is successful:
```
ALTER INDEX
```
Rename an index partition.
- Rename the name of index partition web_returns_p2_P8_index to web_returns_p2_P8_index_new.
```
gaussdb=# ALTER INDEX tpcds_web_returns_p2_index2 RENAME PARTITION web_returns_p2_P8_index TO web_returns_p2_P8_index_new;
```
  If the following information is displayed, the renaming is successful:
```
ALTER INDEX
```
Query indexes.
- Run the following command to query all indexes defined by the system and users:
```
gaussdb=# SELECT RELNAME FROM PG_CLASS WHERE RELKIND='i' or RELKIND='I';
```
- Run the following command to query information about a specified index:
```
gaussdb=# \di+ tpcds_web_returns_p2_index2
```
Drop an index.
```
gaussdb=# DROP INDEX tpcds_web_returns_p2_index1;
```
If the following information is displayed, the deletion is successful:
```
DROP INDEX
```
Cleanup example:
```
gaussdb=# DROP TABLE web_returns_p2;
```