Managing Indexes

Context

Indexes accelerate data access but increase the processing time of insertion, update, and deletion operations. Therefore, you need to evaluate the necessity of creating an index and decide on the selection of the index column. You can determine whether to create an index for a table by analyzing the service processing and data use of applications, as well as columns that are frequently used as search criteria or need to be collated.

Indexes are created based on columns in database tables. Therefore, when creating an index, you need to evaluate the selection criteria of the target column. You can evaluate the necessity of creating an index based on the following conditions and select the column for which the index is to be created:

• Column that is often searched and queried. This speeds up searches.
• Column that is used as the primary key. The primary key must be unique and sorted in order.
• Column that is often joined. This increases the join efficiency.
• Columns that are often searched by range. The index helps collate data, and therefore the specified ranges are contiguous.
• Columns that often need to be collated. The index helps collate data, reducing the time for a collation query.
• Column where the WHERE clause is executed frequently. This speeds up condition judgment.
• Column that often appears after the keywords ORDER BY, GROUP BY, and DISTINCT.
  

  • After an index is created, the system automatically determines when to reference it. If the system determines that indexing is faster than sequential scan, the index will be used.
  • After an index is successfully created, it must be synchronized with the table to ensure new data can be accurately located, which increases the data operation load. Therefore, delete unnecessary indexes periodically.
  • Partitioned table indexes are classified into local indexes and global indexes. A local index corresponds to a specific partition, and a global index corresponds to the entire partitioned table.

Procedure

Create a partitioned table before performing operations. For details, see Managing Partitioned Tables.

• Create an index. For details, see CREATE INDEX.
  • Create the local index tpcds_web_returns_p2_index1 without specifying the partition name.

    1	gaussdb=# CREATE INDEX tpcds_web_returns_p2_index1 ON tpcds.web_returns_p2 (ca_address_id) LOCAL;

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

    1	CREATE INDEX

  • Create the local index tpcds_web_returns_p2_index2 with the partition name specified.

    1 2 3 4 5 6 7 8 9 10 11

    gaussdb=# CREATE INDEX tpcds_web_returns_p2_index2 ON tpcds.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:

    1	CREATE INDEX

  • Create the global index tpcds_web_returns_p2_global_index for a partitioned table.

    CREATE INDEX tpcds_web_returns_p2_global_index ON tpcds.web_returns_p2 (ca_street_number) GLOBAL;

• Change the tablespace of an index partition.
  • Change the tablespace of index partition web_returns_p2_P2_index to example1.

    1	gaussdb=# ALTER INDEX tpcds.tpcds_web_returns_p2_index2 MOVE PARTITION web_returns_p2_P2_index TABLESPACE example1;

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

    1	ALTER INDEX

  • Change the tablespace of index partition web_returns_p2_P3_index to example2.

    1	gaussdb=# ALTER INDEX tpcds.tpcds_web_returns_p2_index2 MOVE PARTITION web_returns_p2_P3_index TABLESPACE example2;

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

    1	ALTER INDEX

• Rename an index partition.
  Rename the index partition web_returns_p2_P8_index to web_returns_p2_P8_index_new.

  1	gaussdb=# ALTER INDEX tpcds.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 rename operation is successful:

  1	ALTER INDEX

• Query indexes.
  • Query all indexes defined by the system and users.

    1	gaussdb=# SELECT RELNAME FROM PG_CLASS WHERE RELKIND='i' or RELKIND='I';

  • Query information about a specified index.

    1	gaussdb=# \di+ tpcds.tpcds_web_returns_p2_index2

• Drop indexes.

  1 2	gaussdb=# DROP INDEX tpcds.tpcds_web_returns_p2_index1; gaussdb=# DROP INDEX tpcds.tpcds_web_returns_p2_index2;

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

  1	DROP INDEX

Introduction to Indexes

GaussDB supports four methods for creating indexes. For details, see Table 1.

• Create an ordinary table.

  1 2	gaussdb=# CREATE TABLE tpcds.customer_address_bak AS TABLE tpcds.customer_address; INSERT 0 0

• Create an ordinary index.
  For the tpcds.customer_address_bak table, you need to perform the following operations frequently:

  1	gaussdb=# SELECT ca_address_sk FROM tpcds.customer_address_bak WHERE ca_address_sk=14888;

  When the database system queries the tpcds.customer_address_bak table, if the WHERE condition is matched row by row in full table scan mode, the operation will cause a significant performance bottleneck in the scenario where the table contains a large amount of data but only a few rows (even zero) are matched. If the database system maintains an index on the ca_address_sk attribute to quickly locate matched tuples, the database system only needs to traverse a small number of index nodes to obtain matched tuples, greatly improving the query efficiency. Furthermore, indexes can improve the update and deletion operation performance in the database.

  Create an index.

  1 2	gaussdb=# CREATE INDEX index_wr_returned_date_sk ON tpcds.customer_address_bak (ca_address_sk); CREATE INDEX

• Create a unique index.

  Create a unique index on the SM_SHIP_MODE_SK column in the tpcds.ship_mode_t1 table.

  gaussdb=# CREATE UNIQUE INDEX ds_ship_mode_t1_index1 ON tpcds.ship_mode_t1(SM_SHIP_MODE_SK);

• Create a composite index.
  To frequently query records with ca_address_sk being 5050 and ca_street_number smaller than 1000 in the tpcds.customer_address_bak table, run the following command:

  1	gaussdb=# SELECT ca_address_sk,ca_address_id FROM tpcds.customer_address_bak WHERE ca_address_sk = 5050 AND ca_street_number < 1000;

  Define a composite index on the ca_address_sk and ca_street_number columns.

  1 2	gaussdb=# CREATE INDEX more_column_index ON tpcds.customer_address_bak(ca_address_sk ,ca_street_number ); CREATE INDEX

• Create a partial index.

  If you only want to find records with ca_address_sk being 5050, you can create a partial index to facilitate your query.

  1 2	gaussdb=# CREATE INDEX part_index ON tpcds.customer_address_bak(ca_address_sk) WHERE ca_address_sk = 5050; CREATE INDEX

• Create an expression index.
  Frequently query records with ca_street_number smaller than 1000.

  1	gaussdb=# SELECT * FROM tpcds.customer_address_bak WHERE trunc(ca_street_number) < 1000;

  The following expression index can be created for this query task:

  1 2	gaussdb=# CREATE INDEX para_index ON tpcds.customer_address_bak (trunc(ca_street_number)); CREATE INDEX

• Drop the tpcds.customer_address_bak table.

  1 2	gaussdb=# DROP TABLE tpcds.customer_address_bak; DROP TABLE