Help Center/ GaussDB/ Developer Guide(Distributed_8.x)/ SQL Reference/ SQL Syntax/ C/ CREATE INDEX
Updated on 2024-08-20 GMT+08:00
View PDF
Share

CREATE INDEX

Description

Defines a new index.

Indexes are primarily used to enhance database performance (though inappropriate use can result in database performance deterioration). You are advised to create indexes on:

  • Columns that are often queried
  • Join conditions. For a query on joined columns, you are advised to create a composite index on the columns. For example, for select * from t1 join t2 on t1.a=t2.a and t1.b=t2.b, you can create a composite index on columns a and b in table t1.
  • Columns having filter criteria (especially scope criteria) of a WHERE clause.
  • Columns that appear after ORDER BY, GROUP BY, and DISTINCT

Partitioned tables do not support partial index creation (when indexes contain the GLOBAL or LOCAL keyword or the created index is a GLOBAL index). If the GLOBAL/LOCAL keyword is declared during index creation, the index of the corresponding type is created. If the partition name is specified during index creation, a LOCAL index is created. If the index is a unique index, the index must contain a partition key. In this case, a local index is created. Otherwise, a global index is created by default.

Precautions

  • If the base table is a hash-distributed table, to create a primary key or unique index that does not contain the distribution key of the base table, use a GSI (using CREATE GLOBAL INDEX); to create a primary key or unique index that contains the distribution key of the base table, use a common index (using CREATE INDEX). In single-DN deployment mode, both the GSI and common index can be successfully created. If the base table is a non-hash-distributed table, you can only create the primary key or unique index as a common index (using CREATE INDEX). That is, the index key must contain the distribution key of the base table.
  • Indexes consume storage and computing resources. Creating too many indexes has negative impact on database performance (especially the performance of data import. Therefore, you are advised to import the data before creating indexes). Therefore, create indexes only when they are necessary.
  • All functions and operators used in an index definition must be immutable, that is, their results must depend only on their parameters and never on any outside influence (such as the contents of another table or the current time). This restriction ensures that the behavior of the index is well-defined. To use a user-defined function in an index or WHERE clause, mark it as an immutable function.
  • Partitioned table 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.
  • A user granted with the CREATE ANY INDEX permission can create indexes in both the public and user schemas.
  • If the base table is distributed in HASH, RANGE, or LIST mode, the distribution key of the base table must be included when a unique index is created, and no expression is allowed.
  • If a user-defined function is called in the expression index, the expression index function is executed based on the permission of the function creator.
  • Data of the XML type cannot be used as common indexes, unique indexes, global indexes, local indexes, or partial indexes.
  • Only B-tree and UB-tree indexes can be created online. Only common indexes of non-partitioned tables, as well as global and local indexes of partitioned tables can be created. PCR UB-tree indexes, level-2 partitions, and GSIs are not supported. Only common indexes, global indexes, and local indexes of Astore and Ustore can be created online in parallel.

Syntax

  • Create an index on a table. 
    1
2
3
4
5
6
7
8
    		 
    CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ IF NOT EXISTS ] [ [schema_name.] index_name ] ON table_name [ USING method ]
    ({ { column_name [ ( length ) ] | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] }[, ...] )
    [ INCLUDE ( column_name [, ...] ) ]
    [ WITH ( {storage_parameter = value} [, ... ] ) ]
    [ TABLESPACE tablespace_name ] 
    { [ COMMENT 'string' ] [ ... ] }
    [ { VISIBLE | INVISIBLE } ]
    [ WHERE predicate ];
  • Create an index on a partitioned table. 
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
    		 
    CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [schema_name.] index_name ] ON table_name [ USING method ]
    ( { { column_name [ ( length ) ] | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS LAST ] } [, ...] )
    [ LOCAL
        [ ( { PARTITION index_partition_name [ FOR { partition_name | ( partition_value [, ...] ) } ] [ TABLESPACE index_partition_tablespace ]
                [ ( [SUBPARTITION index_subpartition_name] [ FOR { partition_name | ( partition_value [, ...] ) } ]  [ TABLESPACE index_partition_tablespace ]
                    [, ...] ) ]
            [, ...] } ) ]
        | GLOBAL ]
    [ INCLUDE ( column_name [, ...] ) ]
    [ WITH ( { storage_parameter = value } [, ...] ) ]
    [ TABLESPACE tablespace_name ] 
    { [ COMMENT 'string' ] [ ... ] }
    [ { VISIBLE | INVISIBLE } ];

Parameters

  • UNIQUE

    Creates a unique index. In this way, the system checks whether new values are unique in the index column. Attempts to insert or update data which would result in duplicate entries will generate an error.

    Currently, only B-tree indexes and UB-tree indexes in row-store tables support unique indexes.

  • CONCURRENTLY

    Creates an index (with ShareUpdateExclusiveLock) in a mode that does not block DML statements. When an index is created, other statements cannot access the table on which the index depends. If this keyword is specified, DML is not blocked during the creation.

    • This option can only specify a name of one index.
    • The CREATE INDEX statement can be run within a transaction, but CREATE INDEX CONCURRENTLY cannot.
    • For temporary tables, you can use CONCURRENTLY to create indexes. However, indexes are created in blocking mode because no other sessions concurrently access the temporary tables and the blocking mode is more cost-effective.
    • This keyword is specified when an index is created. The Astore needs to scan the entire table twice to build it. During the first scan, an index is created and the read and write operations are not blocked. During the second scan, changes that have occurred since the first scan are merged and updated. The Ustore needs to scan the entire table only once. During the scan, the data generated by concurrent DML operations is inserted into the temporary table index_oid_cctmp. After the scan is complete, the data in the temporary table is merged to the primary index and the temporary table is deleted, the index is created. You can use the GUC parameter delete_cctmp_table to specify whether to delete temporary tables after indexes are created online. The default value of this parameter is on, indicating that temporary tables are deleted by default. If this parameter is set to off, temporary tables are retained.
    • For Astore, the table needs to be scanned and built twice, and all existing transactions that may modify the table must be completed. This means that the creation of the index takes a longer time than normal. In addition, the CPU and I/O consumption also affects other services. Although the Ustore only needs to scan the entire table once to create indexes, the preceding consumption also exists.

    • Exceptions may occur during online index creation (for example, manual cancellation, duplicate index key values, insufficient resources, thread startup failure, and lock timeout). As a result, online index creation fails. In this case, indexes in the not valid state may be retained to avoid occupying system resources, the system automatically clears the failed index. Before clearing the failed index, wait until the transaction in the table ends. If a long transaction exists, the system may keep waiting. If you cancel the thread, the failed index remains. If the user cancels the online index creation thread, the process of clearing the failed index is started first. If the user cancels the thread again, the process of clearing the failed index is ended, and the failed index remains. For critical errors, such as FATAL, PANIC, and database faults, you need to manually clear indexes and temporary tables. If both online index creation and automatic residual clearing fail, indexes that are not valid are retained. The residual indexes may be ready or not ready (depending on the phase in which the online index creation fails. For example, the index failed in the first phase is not ready, and the index failed in the third phase is ready). If the residual indexes are ready, the DML statements still maintain the residual indexes. During the maintenance, other errors may occur (for example, the size of the index column exceeds the maximum value or the unique index constraint is violated). To prevent residual indexes from occupying system resources and generating unexpected errors, you need to manually delete them as soon as possible. In the distributed system, failed indexes in DNs are automatically cleared and those in CNs need to be manually cleared. If a critical error occurs, the metadata of the failed indexes cannot be found on some nodes in the distributed system. The DROP INDEX statement cannot be used to delete these indexes. You need to use the DROP INDEX IF EXISTS statement to delete them.

    • For Astore, after the second scan, index creation must wait for any transaction that holds a snapshot earlier than the snapshot taken by the second scan to terminate. In addition, the ShareUpdateExclusiveLock (level 4) added during index creation conflicts with a lock whose level is greater than or equal to 4. Therefore, when such an index is created, the system is prone to hang or deadlock. Example:
      • If two sessions create an index by using CONCURRENTLY for the same table, a deadlock occurs.
      • If a session creates an index by using CONCURRENTLY for a table and another session drops a table, a deadlock occurs.
      • There are three sessions. Session 1 locks table a and does not commit it. Session 2 creates an index by using CONCURRENTLY for table b. Session 3 writes data to table a. Before the transaction of session 1 is committed, session 2 is blocked.
      • When an index is created by using CONCURRENTLY for a table concurrently with the TRUNCATE operation on the same table, a deadlock occurs.
      • The transaction isolation level is set to repeatable read (read committed by default). Two sessions are started. Session 1 writes data to table a and does not commit it. Session 2 creates an index by using CONCURRENTLY for table b. Before the transaction of session 1 is committed, session 2 is blocked.
    • When an index is being created or fails to be created, you need to check the index progress or status. You can query the gs_get_index_status('schema_name', 'index_name') function to check the index status on all nodes. The input parameters schema_name and index_name are used to specify the index schema name and index name, respectively. The return values are node_name, indisready, and indisvalid, indicating the node name, whether the index can be inserted on the node, and whether the index is available on the node. The index is available only when indisready and indisvalid on all nodes are set to true; otherwise, wait until the index creation is complete. If the index fails to be created, delete the index and create it again.
    • When the I/O and CPU resources are not limited, the service performance deterioration caused by online index creation can be controlled within 10%. However, in special scenarios, the service performance deterioration may exceed 10%. This is because online index creation is a long transaction that consumes a large number of I/O and CPU resources. It consumes more resources than offline index creation. The longer the online index creation transaction lasts, the greater the impact on service performance. The time for creating indexes online is positively correlated with the data volume of base tables and the data volume generated by concurrent DML statements. When the I/O and CPU resources are not limited, the time for creating indexes online is about two to six times that for creating indexes offline. However, when the number of concurrent transactions is large (> 10000 TPS) or resource contention occurs, the time may be even longer. If a long transaction exists during online index creation, the long transaction running time must be added. In Astore and Ustore modes, you can create indexes in parallel to shorten the index creation time. The performance of online parallel index creation increases to a certain value and becomes stable as the number of parallel worker threads increases. Compared with the performance of creating indexes in serial mode, the performance of creating indexes in parallel online is improved by about 30%. You are advised to create indexes online during off-peak hours to avoid great impact on services. In addition, do not execute long transactions during online index creation. Although online index creation provides the capability of uninterrupted services to some extent, it still needs to be implemented with caution.
    • When a unique index is created online (by using CREATE UNIQUE INDEX CONCURRENTLY), the table is scanned to check whether the data in the table is unique. If the data in the table is not unique, an error is reported and the online index creation exits. During online index creation, whether data in the table is unique may change. Whether the creation fails depends on whether duplicate data is scanned. Consider the following situations: 1. Tuple A and tuple B are inserted during table scanning, and their index columns are the same. If both tuple A and tuple B are scanned during online index scanning, a uniqueness violation may be reported. However, if tuple B is deleted later, in this case, an error may be reported during online index creation, indicating that the uniqueness is violated. However, after the online index creation fails and exits, the data in the table is found to be unique. 2. During online index creation, if tuple A is inserted, deleted, and then inserted, and SnapshotNow is used to scan the table, tuple A may be scanned twice, which may violate the uniqueness constraint. During online index creation, Astore uses SnapshotMVCC to scan tables, and Ustore uses SnapshotNow to scan tables.
  • IF NOT EXISTS

    When IF NOT EXISTS is specified, the system checks whether a relationship with the same name already exists in the current schema before creating an index. It is not created and a NOTICE is returned if a relationship with the same name already exists. When IF NOT EXISTS is not specified and a relationship with the same name exists in the schema, an ERROR is returned.

  • schema_name

    Specifies the schema name.

    Value range: an existing schema name

  • index_name

    Specifies the name of the index to be created. No schema name can be included here; the index is always created in the same schema as its parent table.

    Value range: a string. It must comply with the naming convention.

  • table_name

    Specifies the name of the table to be indexed (optionally schema-qualified).

    Value range: an existing table name

  • USING method

    Specifies the name of the index method to be used.

    Value range:

    • btree: B-tree indexes store key values of data in a B+ tree structure. This structure helps users to quickly search for indexes. B-tree is applicable to comparison query and range query.
    • ubtree: Multi-version B-tree index used only for Ustore tables. The index page contains transaction information and can be recycled. By default, the INSERTPT function is enabled for UB-tree indexes.

    Row-store tables (Astore) support the following index types: btree (default). Row-store tables (Ustore) support the index type: ubtree.

    B-tree and UB-tree are closely related to the table storage type Astore or Ustore. When an index is created, if the specified index type does not correspond to the main table, the index type is automatically converted.

  • column_name

    Specifies the name of the column on which an index is to be created.

    Multiple columns can be specified if the index method supports multi-column indexes. A global index supports a maximum of 31 columns, and other indexes support a maximum of 32 columns.

  • column_name ( length )

    Creates a prefix key index based on a column in the table. column_name indicates the column name of the prefix key, and length indicates the prefix length.

    The prefix key uses the prefix of the specified column data as the index key value, which reduces the storage space occupied by the index. Indexes can be used for partial filter and join conditions that contain prefix key columns.

    • The prefix key supports the following index methods: B-tree and UB-tree.
    • The data type of the prefix key column must be binary or character (excluding special characters).
    • The prefix length must be a positive integer that does not exceed 2676 and cannot exceed the maximum length of the column. For the binary type, the prefix length is measured in bytes. For non-binary character types, the prefix length is measured in characters. The actual length of the key value is restricted by the internal page. If a column contains multi-byte characters or an index has multiple keys, the length of the index line may exceed the upper limit. As a result, an error is reported. Consider this situation when setting a long prefix length.
    • In the CREATE INDEX syntax, the following keywords cannot be used as prefix keys for column names: COALESCE, EXTRACT, GREATEST, LEAST, LNNVL, NULLIF, NVL, NVL2, OVERLAY, POSITION, REGEXP_LIKE, SUBSTRING, TIMESTAMPDIFF, TREAT, TRIM, XMLCONCAT, XMLELEMENT, XMLEXISTS, XMLFOREST, XMLPARSE, XMLPI, XMLROOT and XMLSERIALIZE.
    • The prefix key is a special expression key. Some constraints and restrictions that are not described are the same as those of the expression key. For details, see the description of the expression index.
    • As an expression index, the prefix index is the same as the expression index and cannot be used as the unique index or primary key in distributed mode.
  • expression

    Specifies an expression based on one or more columns of the table. The expression usually must be written with surrounding parentheses, as shown in the syntax. However, the parentheses can be omitted if the expression has the form of a function call.

    Expression can be used to obtain fast access to data based on some transformation of the basic data. For example, an index computed on upper(col) would allow the WHERE upper(col) = 'JIM' clause to use an index.

    If an expression contains the IS NULL clause, the index for this expression is invalid. In this case, you are advised to create a partial index.

  • COLLATE collation

    Assigns a collation to the column (which must be of a collatable data type). If no collation is specified, the default collation is used. You can run the SELECT * FROM pg_collation command to query collation rules from the pg_collation system catalog. The default collation rule is the row starting with default in the query result.

    In MySQL-compatible mode (that is, sql_compatibility set to 'MYSQL'), when the MySQL-compatible collation is used, ensure that the index collation is the same as the column collation. Otherwise, the index fails to be created and an error is reported.

  • opclass

    Specifies the name of an operator class. An operator class can be specified for each column of an index. The operator class identifies the operators to be used by the index for that column. For example, a B-tree index on the type int4 would use int4_ops; this operator class includes comparison functions for values of type int4. In practice, the default operator class for the column's data type is sufficient. The operator class applies to data with multiple sorts. For example, users might want to sort a complex-number data type either by absolute value or by real part. They could do this by defining two operator classes for the data type and then selecting the proper class when making an index.

  • ASC

    Specifies an ascending (default) sort order.

  • DESC

    Specifies a descending sort order.

  • NULLS FIRST

    Specifies that null values appear before non-null values in the sort ordering. This is the default when DESC is specified.

  • NULLS LAST

    Specifies that null values appear after non-null values in the sort ordering. This is the default when DESC is not specified.

  • LOCAL

    Specifies that the partitioned index to be created is a local index.

  • GLOBAL

    Specifies the partitioned index to be created as a global index. If no keyword is specified, a global index is created by default.

  • INCLUDE ( column_name [, ...] )

    The optional INCLUDE clause specifies that some non-key columns are included in indexes. Non-key columns cannot be used as search criteria for accelerating index scans, and they are omitted when the unique constraints of the indexes are checked.

    An index-only scan can directly return content in the non-key columns without accessing the heap table corresponding to the indexes.

    Exercise caution when adding non-key columns as INCLUDE columns, especially for wide columns. If the size of an index tuple exceeds the maximum size allowed by the index type, data insertion fails. Note that in any case, adding non-key columns to an index increases the space occupied by the index, which may slow down the search speed.

    Currently, only UB-tree indexes access mode supports this feature. Non-key columns are stored in the index leaf tuple corresponding to the heap tuple and are not included in the tuple on the upper-layer index page.

  • PARTITION clause 
    PARTITION index_partition_name [ FOR { partition_name | ( partition_value [, ...] ) } ] [ TABLESPACE index_partition_tablespace ]

    If for partition_name or partition_value is specified in the PARTITION clause, the created partitioned index has the sparsely partitioned index attribute. If for partition_name or partition_value is not specified, the number of PARTITION clauses must be the same as the number of partitions in the target table.

    • The sparsely partitioned index can be created only on a single partition.
    • The sparsely partitioned index supports only B-tree and UB-tree indexes.
    • The sparsely partitioned index does not support the UNIQUE attribute.
    • Currently, the global index is not supported.
    • Currently, level-2 partitions are not supported.
    • The created sparsely partitioned index contains the sparsely_partitioned=true option. Note that the CREATE TABLE or ALTER TABLE statement cannot be used to explicitly specify a non-sparsely partitioned index as a sparsely partitioned index in this option. If the created index is a sparsely partitioned index, sparsely_partitioned=false will be ignored.
    • If a version earlier than 505.0.0 is upgraded to 505.0.0 or later, sparsely partitioned indexes cannot be created during upgrade observation.
    • PARTITION index_partition_name

      Specifies the name of an index partition.

      Value range: a string. It must comply with the naming convention.

    • FOR partition_name

      Specifies the name of the target partition. If the specified partition does not exist, an error is reported.

    • FOR (partition_value [, ...])

      Specifies the value of the specified partition key. The partition key value must be placed in the partition key value FOR list. If there are multiple columns of partition keys, multiple partition_value values must be specified.

    • TABLESPACE index_partition_tablespace

      Specifies the tablespace of an index partition.

      Value range: If this parameter is not specified, the value of index_tablespace is used.

  • WITH ( {storage_parameter = value} [, ... ] )

    Specifies the storage parameter used for an index.

    Value range:

    For indexes other than Psort, you can also set it to FILLFACTOR. Only UB-tree indexes support INDEXSPLIT. Only non-partitioned B-tree indexes support the DEDUPLICATION parameter. Only UB-tree indexes support INDEX_TXNTYPE.
    • STORAGE_TYPE

      Storage engine type of the table where the index is located. If storage_type specified by the index conflicts with the index type, the storage engine type is automatically changed to the correct one. Only B-tree and UB-tree are supported. You cannot modify this parameter after it is set.

      Value range: USTORE, indicating that the table where the index is located uses the in-place update storage engine. ASTORE: indicates that the table where the index is located is an append-only storage engine.

      Default value: USTORE for Ustore tables and ASTORE for Astore tables.

    • FILLFACTOR

      The fill factor of an index is a percentage from 10 to 100. In the scenario where a large number of concurrent insertions are performed and the key value range is dense, the contention of the same index page is high during the insertion. Therefore, a smaller fill factor is more appropriate.

      Value range: 10–100

    • ACTIVE_PAGES

      Specifies the number of index pages, which may be less than the actual number of physical file pages and can be used for optimization. Currently, this parameter is valid only for the local index of the Ustore partitioned table and will be updated by VACUUM and ANALYZE (including AUTOVACUUM). You are advised not to manually set this parameter because it is invalid in distributed mode.

    • DEDUPLICATION

      Specifies whether to deduplicate and compress tuples with duplicate key values. This is an index parameter. When there are a large number of indexes with duplicate key values, enabling this parameter can effectively reduce the space occupied by indexes. This parameter does not take effect for primary key indexes and unique indexes. If non-unique indexes are used and the index key value duplication rate is low or the index key values are unique, enabling this parameter will slightly deteriorate the index insertion performance. Currently, local and global indexes of partitioned tables are not supported.

      Value range: Boolean value. The default value is the value of the enable_default_index_deduplication GUC parameter (the default value is off).

    • INDEXSPLIT

      Specifies the splitting policy of UB-tree indexes. The DEFAULT policy is the same as the splitting policy of B-tree indexes. The INSERTPT policy can significantly reduce the index space usage in some scenarios.

      Value range: INSERTPT and DEFAULT

      Default value: INSERTPT

    • INDEX_TXNTYPE

      Specifies the UB-tree index type (only UB-tree indexes support INDEX_TXNTYPE). When the value is PCR, flashback queries can be performed using UB-tree. PCR UB-tree indexes do not support online index creation, GSIs, ultimate RTO replay, and standby node read. If INDEX_TXNTYPE is not specified, the GUC parameter index_txntype specifies the type of the index to be created. INDEX_TXNTYPE cannot be modified by using ALTER INDEX INDEX_NAME SET (INDEX_TXNTYPE=PCR or RCR).

      Type: character string (case insensitive)

      Value range: RCR and PCR

      Default value: RCR

      For example:

      CREATE UNIQUE INDEX t2_b_pkey ON t(b) WITH(index_txntype='pcr');
    • STAT_STATE

      Determines whether index statistics are locked. If locked, the index statistics cannot be updated. This parameter is invalid in distributed mode.

      Value range: locked and unlock.

      Default value: unlock.

    • enable_tde

      Specifies whether the index is encrypted. If the index is encrypted, the database automatically encrypts the data in the encrypted index before storing it. Before using this parameter, ensure that the TDE function has been enabled using the GUC parameter enable_tde, the information for accessing the key service has been set using the GUC parameter tde_key_info, and the enable_tde attribute has been set for the base table. For details about how to use this parameter, see section "Transparent Data Encryption" in Feature Guide. This parameter supports only B-tree and UB-tree indexes. Other indexes such as hash indexes are not supported.

      Value range: on and off If enable_tde is set to on, the values of key_type, tde_cmk_id, and dek_cipher are automatically generated in the database.

      Default value: off

    • encrypt_algo

      Specifies the encryption algorithm of the encryption index. This parameter must be used together with enable_tde.

      Value range: a string. The value can be AES_128_CTR or SM4_CTR.

      Default value: null if enable_tde and AES_128_CTR if enable_tde is set.

    • dek_cipher

      Specifies the DEK ciphertext. After you set the enable_tde parameter for an index, the index automatically copies the dek_cipher parameter of the base table. This parameter cannot be set or modified.

      Value range: a string

      Default value: null

    • key_type

      Specifies the type of the master key. After you set the enable_tde parameter for a table, the index automatically copies the key_type parameter of the base table. This parameter cannot be set or modified.

      Default value: null

    • cmk_id

      Specifies the ID of the master key. After you set the enable_tde parameter for a table, the index automatically copies the cmk_id parameter of the base table. This parameter cannot be set or modified.

      Value range: a string

      Default value: null

    • LPI_PARALLEL_METHOD

      Index parameter, which is used to set the parallel creation mode of local indexes for partitioned tables.

      Type: String

      Value range: PAGE, PARTITION, and AUTO PAGE creates indexes for pages in parallel. Multiple subthreads are started to scan and sort data. Each subthread processes a data page at a time. After scanning and sorting, the main thread combines the sorting results in serial mode and inserts tuples into the indexes. PARTITION concurrently creates indexes at the partition level and starts multiple subthreads. Each subthread scans, sorts, and inserts indexes into a partition. AUTO estimates the cost of parallel index creation at the page level and partition level based on the partitioned table statistics, and selects the parallel index creation mode with a lower cost. (The statistics may be different from the actual data, resulting in inaccurate calculation results.)

      Default value: PAGE

      Setting suggestion: Set this parameter to PARTITION if the partitioned table data is evenly distributed in each partition. This parameter supports only the B-tree local index of Astore partitioned tables. It does not support the global index of partitioned tables, non-partitioned table indexes, segment-page table indexes, hash bucket table indexes, or online index creation.

      For example:

      CREATE INDEX idx ON tbl(col) WITH (lpi_parallel_method = 'partition');
  • TABLESPACE tablespace_name

    Specifies the tablespace for an index. If no tablespace is specified, the default tablespace is used.

    Value range: an existing table name

  • WHERE predicate

    Creates a partial index. A partial index is an index that contains entries for only a portion of a table, usually a portion that is more useful for indexing than the rest of the table. For example, if you have a table that contains both billed and unbilled orders where the unbilled orders take up a small fraction of the total table and yet that is an often used portion, you can improve performance by creating an index on just that portion. In addition, the WHERE clause with a UNIQUE constraint can be used to enforce uniqueness over a subset of a table.

    Value range: The predicate expression can only refer to columns of the underlying table, but it can use all columns, not just the ones being indexed. Currently, subqueries and aggregate expressions are forbidden in WHERE. You are advised not to use numeric types such as int for predicate, because such types can be implicitly converted to bool values (non-zero values are implicitly converted to true and 0 is implicitly converted to false), which may cause unexpected results.

    For a partitioned table index, if the created index contains the GLOBAL keyword or the created index is a GLOBAL index, the WHERE clause cannot be used to create an index.

  • COMMENT 'string'

    COMMENT 'string' is used to add comments to an index.

    • This parameter is valid only in a MySQL-compatible database (that is, sql_compatibility set to 'MYSQL').
    • An index-level comment can contain a maximum of 1024 characters.
  • VISIBLE

    Sets the index to be visible. This is the default option.

    • VISIBLE can be set only in an ORA-compatible database (that is, sql_compatibility set to 'ORA').
    • When disable_keyword_options is set to "visible", this keyword cannot be used.
    • This keyword is not supported in the upgrade uncommitted phase.
  • INVISIBLE

    Sets the index to be invisible.

    • INVISIBLE can be set only in an ORA-compatible database (that is, sql_compatibility set to 'ORA').
    • When disable_keyword_options is set to "invisible", this keyword cannot be used.
    • This keyword is not supported in the upgrade uncommitted phase.

Examples

  • Common index 
    -- Create the tbl_test1 table.
gaussdb=# CREATE TABLE tbl_test1(
    id int,           -- User ID
    name varchar(50), -- Username
    postcode char(6)  -- Postal code
);

-- Create the tbs_index1 tablespace.
gaussdb=# CREATE TABLESPACE tbs_index1 RELATIVE LOCATION 'test_tablespace/tbs_index1';

-- Create the idx_test1 index for the tbl_test1 table and specify a tablespace.
gaussdb=# CREATE INDEX idx_test1 ON tbl_test1(name) TABLESPACE tbs_index1;

-- Query information about the idx_test1 index.
gaussdb=# SELECT indexname,tablename,tablespace FROM pg_indexes WHERE indexname = 'idx_test1';
 indexname | tablename | tablespace 
-----------+-----------+------------
 idx_test1 | tbl_test1 | tbs_index1
(1 row)

-- Delete the index.
gaussdb=# DROP INDEX idx_test1;

-- Delete the tablespace.
gaussdb=# DROP TABLESPACE tbs_index1;
  • Unique index 
    -- Create the unique index idx_test2 for the tbl_test1 table.
gaussdb=# CREATE UNIQUE INDEX idx_test2 ON tbl_test1(id);

-- Query index information.
gaussdb=# \d tbl_test1
           Table "public.tbl_test1"
  Column  |         Type          | Modifiers 
----------+-----------------------+-----------
 id       | integer               | 
 name     | character varying(50) | 
 postcode | character(6)          | 
Indexes:
    "idx_test2" UNIQUE, btree (id) TABLESPACE pg_default

-- Delete the index.
gaussdb=# DROP INDEX idx_test2;
  • Expression index 
    -- Create an expression index for the tbl_test1 table.
gaussdb=# CREATE INDEX idx_test3 ON tbl_test1(substr(postcode,2));

-- Query index information.
gaussdb=# \d tbl_test1
           Table "public.tbl_test1"
  Column  |         Type          | Modifiers 
----------+-----------------------+-----------
 id       | integer               | 
 name     | character varying(50) | 
 postcode | character(7)          | 
Indexes:
    "idx_test3" btree (substr(postcode::text, 2)) TABLESPACE pg_default

-- Delete the index.
gaussdb=# DROP INDEX idx_test3;
  • Partial index 
    -- Create an index for data whose ID is not empty in the tbl_test1 table.
gaussdb=# CREATE INDEX idx_test4 ON tbl_test1(id) WHERE id IS NOT NULL;

-- Delete the index.
gaussdb=# DROP INDEX idx_test4;

-- Delete the table.
gaussdb=# DROP TABLE tbl_test1;
  • Partitioned index 
    -- Create a table.
gaussdb=# CREATE TABLE student(id int, name varchar(20)) PARTITION BY RANGE (id) (
    PARTITION p1 VALUES LESS THAN (200),
    PARTITION pmax VALUES LESS THAN (MAXVALUE)
);

-- Create a local partitioned index without specifying the index partition name.
gaussdb=# CREATE INDEX idx_student1 ON student(id) LOCAL;

-- Check the index partition information. It is found that the number of local index partitions is the same as the number of table partitions.
gaussdb=# SELECT relname FROM pg_partition WHERE parentid = 'idx_student1'::regclass;
   relname   
-------------
 p1_id_idx
 pmax_id_idx
(2 rows)

-- Delete the local partitioned index.
gaussdb=# DROP INDEX idx_student1;

-- Create a global index.
gaussdb=# CREATE INDEX idx_student2 ON student(name) GLOBAL;

-- Check the index partition information. It is found that the number of global index partitions is different from the number of table partitions.
gaussdb=# SELECT relname FROM pg_partition WHERE parentid = 'idx_student2'::regclass;
 relname 
---------
(0 rows)

-- Delete the global partitioned index.
gaussdb=# DROP INDEX idx_student2;
 
-- Create a local expression index without specifying the index partition name.
gaussdb=# CREATE INDEX idx_student3 ON student(lower(name)) LOCAL;

-- Check the index partition information. It is found that the number of local index partitions is the same as the number of table partitions.
gaussdb=# SELECT relname FROM pg_partition WHERE parentid = 'idx_student3'::regclass;
   relname   
-------------
 p1_id_idx
 pmax_id_idx
(2 rows)

-- Delete the expression index of the LOCAL partition.
gaussdb=# DROP INDEX idx_student3;

-- Create a global expression index.
gaussdb=# CREATE INDEX idx_student4 ON student(lower(name)) GLOBAL;

-- Check the index partition information. It is found that the number of global expression index partitions is different from the number of table partitions.
gaussdb=# SELECT relname FROM pg_partition WHERE parentid = 'idx_student4'::regclass;
 relname 
---------
(0 rows)

-- Delete the expression index of the global partition.
gaussdb=# DROP INDEX idx_student4;

-- Delete the table.
gaussdb=# DROP TABLE student;

Helpful Links

ALTER INDEX and DROP INDEX

Suggestions

  • create index
    Constraints:
    • An index of an ordinary table supports a maximum of 32 columns. A GLOBAL index of a partitioned table supports a maximum of 31 columns.
    • The size of a single index cannot exceed the size of the index page (8 KB). The size of a B-tree or UB-tree index cannot exceed one-third of the page size.
    • Partial indexes cannot be created in a partitioned table.
    • When a GLOBAL index is created on a partitioned table, the following constraints apply:
      • Expression indexes and partial indexes are not supported.
      • Only B-tree indexes are supported.
    • In the same attribute column, the local index and global index of a partition cannot coexist.
    • If the ALTER statement does not contain UPDATE GLOBAL INDEX, the original global index is invalid. In this case, other indexes are used for query. If the ALTER statement contains UPDATE GLOBAL INDEX, the original global index is still valid and the index function is correct.
Parent topic: C