Help Center/ GaussDB/ Feature Guide(Centralized_3.x)/ Partitioned Table/ Partitioned Table Query Optimization/ Partitioned Indexes
Updated on 2024-05-20 GMT+08:00
View PDF
Share

Partitioned Indexes

There are three types of indexes on a partitioned table:
  1. Global non-partitioned index
  2. Global partitioned index
  3. Local partitioned index

Currently, GaussDB Kernel 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 for the constraints. Otherwise, a global index is created.

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';
gaussdb=# CREATE TABLESPACE example3 LOCATION '/home/omm/example3';
gaussdb=# 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
  • 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.
    • Query all indexes defined by the system and users. 
      gaussdb=# SELECT RELNAME FROM PG_CLASS WHERE RELKIND='i' or RELKIND='I';
    • 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

    Perform the cleanup operation.

    -- Cleanup example
gaussdb=# DROP TABLE web_returns_p2;