Creating and Managing Indexes

Background

Indexes accelerate the data access speed but also add the processing time of the insert, update, and delete operations. Therefore, before creating an index, consider whether it is necessary and determine the columns where indexes will be created. You can determine whether to add 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 sorted.

Indexes are created based on columns in database tables. When creating indexes, you need to determine the columns, which can be:

• Columns that are frequently searched: The search efficiency can be improved.
• The uniqueness of the columns and the data sequence structures is ensured.
• Columns that usually function as foreign keys and are used for connections. Then the connection efficiency is improved.
• Columns that are usually searched for by a specified scope. These indexes have already been arranged in a sequence, and the specified scope is contiguous.
• Columns that need to be arranged in a sequence. These indexes have already been arranged in a sequence, so the sequence query time is accelerated.
• Columns that usually use the WHERE clause. Then the condition decision efficiency is increased.
• Fields that are frequently used after keywords, such as 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 sequenced scanning, the index will be used.
  • After an index is successfully created, it must be synchronized with the associated table to ensure new data can be accurately located. Therefore, data operations increase. Therefore, delete unnecessary indexes periodically.
  • After an index is created, it takes effect on the existing data in the table.

Procedure

For details about the procedure for creating a partitioned table, see Creating and Managing Partitioned Tables.

• Creating an Index
  • Create the partitioned table index tpcds_web_returns_p2_index1 without specifying the partition name.

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

    If the following information is displayed, the index has been created.

    1	CREATE INDEX

  • Create the partitioned table index tpcds_web_returns_p2_index2 and specify index names for all partitions. Currently, specifying index names for partial partitions is not allowed.

    1 2 3 4 5 6 7 8 9 10 11

    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 index has been created.

    1	CREATE INDEX

• Renaming an index partition
  Rename the name of index partition web_returns_p2_P8_index to web_returns_p2_P8_index_new.

  1	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 index has been renamed.

  1	ALTER INDEX

• Querying indexes
  • Run the following command to query all indexes defined by the system and users:

    1	SELECT RELNAME FROM PG_CLASS WHERE RELKIND='i';

  • Run the following command to query information about a specified index:

    1	\di+ tpcds.tpcds_web_returns_p2_index2

• Deleting an index

  1 2	DROP INDEX tpcds.tpcds_web_returns_p2_index1; DROP INDEX tpcds.tpcds_web_returns_p2_index2;

  If the following output is displayed, the index has been deleted.

  1	DROP INDEX

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

• After an index is created, the system automatically determines when to reference it. If the system determines that indexing is faster than sequenced scanning, the index will be used.

• After an index is successfully created, it must be synchronized with the associated table to ensure new data can be accurately located. Therefore, data operations increase. Therefore, delete unnecessary indexes periodically.

• Run the following command to create an ordinary table:

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

• Create a common index.
  You need to query the following information in the tpcds.customer_address_bak table:

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

  Generally, the database system needs to scan the tpcds.customer_address_bak table row by row to find all matched tuples. If the size of the tpcds.customer_address_bak table is large but only a few (possibly zero or one) of the WHERE conditions are met, the performance of this sequential scan is low. If the database system uses an index to maintain the ca_address_sk attribute, the database system only needs to search a few tree layers for the matched tuples. This greatly improves data query performance. Furthermore, indexes can improve the update and delete operation performance in the database.

  Run the following command to create an index:

  1	CREATE INDEX index_wr_returned_date_sk ON tpcds.customer_address_bak (ca_address_sk);

• Create a multi-column index.
  Assume you need 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	SELECT ca_address_sk,ca_address_id FROM tpcds.customer_address_bak WHERE ca_address_sk = 5050 AND ca_street_number < 1000;

  Run the following command to define a multiple-column index on ca_address_sk and ca_street_number columns:

  1	CREATE INDEX more_column_index ON tpcds.customer_address_bak(ca_address_sk ,ca_street_number );

• Create a partition index.

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

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

• Create an expression index.
  Assume you need to frequently query records with ca_street_number smaller than 1000, run the following command:

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

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

  1	CREATE INDEX para_index ON tpcds.customer_address_bak (trunc(ca_street_number));

• Delete the tpcds.customer_address_bak table.

  1	DROP TABLE tpcds.customer_address_bak;