CREATE INDEX

Description

Creates an index in a specified table.

Indexes are primarily used to enhance database performance (though inappropriate use can deteriorate database performance). 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

Different from the syntax used for creating an index in an ordinary table, the one used in a partitioned table follows this rule: If the GLOBAL or LOCAL keyword is specified during index creation, the system creates an index of the corresponding type. If no keyword is specified, a global index is created by default with one exception that a local index can be created for a unique index which contains all partition keys.

Precautions

• 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 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 the CREATE ANY INDEX permission can create indexes in both the public and user schemas.
• Other users are not allowed to create expression indexes that contain user-defined functions on tables of the initial user.
• 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 ordinary indexes, unique indexes, global indexes, or local indexes.
• Only B-tree and UB-tree indexes, instead of hash indexes, can be created online. Only ordinary indexes of non-partitioned tables, as well as global and local indexes of partitioned tables can be created. Online index column addition, deletion, and modification, PCR UB-tree indexes, level-2 partitions, and GSIs are not supported. Only ordinary indexes, global indexes, and local indexes of Astore and Ustore can be created online in parallel.

Syntax

• Create an index on a table.

  CREATE  [ UNIQUE ] INDEX [ [schema_name.] index_name ] [index_type] 
  ON table_name  ( key_part,... )   
      [ WITH ({ storage_parameter = value } [, ... ] ) ]
  [ TABLESPACE tablespace_name ]
      [index_option]
      [algorithm_option | lock_option] ...
  
  key_part:
      { { column_name [ ( length ) ] | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] }[, ...]
  
  index_option: {
   index_type
    | COMMENT 'string'
  }
  
  index_type:
      USING {BTREE | UBTREE}
  
  algorithm_option:
      ALGORITHM [=] {DEFAULT | INPLACE | COPY}
  
  lock_option:
      LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE};

• Create an index on a partitioned table.

  CREATE  [ UNIQUE ] INDEX [ [schema_name.] index_name ][index_type] 
   ON table_name
      [ LOCAL
          [ ( { PARTITION index_partition_name [ TABLESPACE index_partition_tablespace ]
                  
              [, ...] } ) ]
          | GLOBAL ]
      [ WITH ( { storage_parameter = value } [, ...] ) ]
      [ TABLESPACE tablespace_name ]
      [index_option]
      [algorithm_option | lock_option] ...
  
  index_option: {
   index_type
    | COMMENT 'string'
  }
  
  index_type:
      USING {BTREE | UBTREE}
  
  algorithm_option:
      ALGORITHM [=] {DEFAULT | INPLACE | COPY}
  
  lock_option:
      LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE};
  

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 support unique indexes.

• schema_name

  Specifies the schema name.

  Value range: an existing schema name

• index_name

  Specifies the name of the index to be created. The schema of the index is the same as that of the table.

  Value range: character strings complying with Identifier Description.

• table_name

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

  Value range: an existing table name

• USING method

  Specifies the method of creating an index.

  Value range:

  • BTREE: The key values of data are stored in a B+ tree structure, enabling users to quickly search for indexes. B-tree indexes support comparison queries with ranges specified.
  • 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 following index type: BTREE. (The UBTREE index is created.)

  

  B-tree is closely related to the table storage type Astore or Ustore. If the specified index type does not correspond to the main table, the index type is automatically converted during index creation. An index created by Astore is B-tree, and the one created by Ustore is UB-tree.

• 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 composite 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 row may exceed the upper limit. As a result, an error is reported. Consider this situation when setting a long prefix length.
  • 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.
• expression

  Specifies an expression index based on one or more columns of the table. It is usually written in parentheses.

  The expression index 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 clause WHERE upper(col) = 'JIM' to use an index.

  If an expression contains IS NULL, the index for this expression is invalid.

• 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.

• 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. In addition, if the value of COLLATE of an index of the string type (such as varchar, varchar2, and text) is not C or POSIX, but you want the index to support prefix matching, you need to specify the varchar_pattern_ops option.

• 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.

• 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.

• PARTITION clause

  The syntax for specifying level-1 partition information is as follows:

  PARTITION index_partition_name  [ TABLESPACE index_partition_tablespace ]

  The syntax for specifying level-2 partition information is as follows:

  SUBPARTITION index_subpartition_name  [ TABLESPACE index_partition_tablespace ]

  • PARTITION index_partition_name

    Specifies the name of an index partition.

    Value range: character strings complying with Identifier Description.

  • 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.

    Note: You need to create or delete tablespaces in a non-M-compatible database.

    

    Level-2 partitions are not supported.

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

  Specifies the storage parameter used for an index.

  Value range:

  Only UB-tree indexes support INDEXSPLIT. Only non-partitioned B-tree indexes support the DEDUPLICATION parameter. Only UB-tree indexes support the INDEX_TXNTYPE parameter.

  • STORAGE_TYPE

    Specifies the storage engine type of the table where the index is located. The storage engine type will be automatically corrected if it conflicts with the index type. Once set, this parameter cannot be modified.

    Value range:

    • USTORE: The table where the index is located uses an in-place update storage engine.
    • ASTORE: The table where the index is located uses an append-only storage engine.

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

  • FILLFACTOR

    Specifies a fill factor for the index. It is a percentage between 10 and 100. When a large number of insertions occur concurrently with a dense key value range, there will be significant contention for the same index page. In such cases, a smaller fill factor is more appropriate.

    Value range: 10–100

  • 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 read on standby. 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  INDEX t2_b_pkey ON t(b) WITH(index_txntype='pcr');

  • 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 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.

  • 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).

  • stat_state

    Determines whether index statistics are locked. If locked, the index statistics cannot be updated.

    Value range: locked and unlock.

    Default value: unlock.

  • LPI_PARALLEL_METHOD

    Specifies the parallel creation mode of local indexes for partitioned tables. PAGE creates indexes in parallel at the page level. Multiple sub-threads are started to scan and sort data. Each sub-thread 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 sub-threads. Each sub-thread scans, sorts, and inserts indexes into a partition. AUTO estimates the cost of parallel index creation at the page level or partition level based on the partitioned table statistics, and selects the parallel index creation mode with a lower cost. (The possible difference between the statistics and the actual data may result in inaccurate calculation.) Set this parameter to PARTITION if data in the partitioned table is evenly distributed. 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, or online index creation.

    Type: character string (case-insensitive)

    Value range: PAGE, PARTITION, and AUTO

    Default value: PAGE

    For example:

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

    

    Currently, the distributed 505.2 environment does not support compression indexes, COMPRESSTYPE, COMPRESS_LEVEL, COMPRESS_CHUNK_SIZE, COMPRESS_PREALLOC_CHUNKS, COMPRESS_BYTE_CONVERT, and COMPRESS_DIFF_CONVERT options.

• TABLESPACE tablespace_name

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

  Value range: an existing tablespace name

  Note: You need to create or delete tablespaces in a non-M-compatible database.

Examples

• Ordinary index

  -- Create the tbl_test1 table.
  m_db=# CREATE TABLE tbl_test1(
      id int,           -- User ID
      name varchar(50), -- Username
      postcode char(6)  -- Postal code
  );
  
  -- Create the idx_test1 index for the tbl_test1 table and specify a tablespace.
  m_db=# CREATE INDEX idx_test1 ON tbl_test1(name) TABLESPACE pg_default;
  
  -- Query information about the idx_test1 index.
  m_db=# SELECT indexname,tablename,tablespace FROM pg_indexes WHERE indexname = 'idx_test1';
   indexname | tablename | tablespace 
  -----------+-----------+------------
   idx_test1 | tbl_test1 |
  (1 row)
  
  -- Drop the index.
  m_db=# DROP INDEX idx_test1;
  

• Unique index

  -- Create the unique index idx_test2 for the tbl_test1 table.
  m_db=# CREATE UNIQUE INDEX idx_test2 ON tbl_test1(id);
  
  -- Query index information.
  m_db=# \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
  
  -- Drop the index.
  m_db=# DROP INDEX idx_test2;

• Partitioned index

  -- Create a table.
  m_db=# 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.
  m_db=# 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 that of table partitions.
  m_db=# SELECT relname FROM pg_partition WHERE parentid = 'idx_student1'::regclass;
     relname   
  -------------
   p1_id_idx
   pmax_id_idx
  (2 rows)
  
  -- Drop the local partitioned index.
  m_db=# DROP INDEX idx_student1;
  
  -- Create a global index.
  m_db=# 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.
  m_db=# SELECT relname FROM pg_partition WHERE parentid = 'idx_student2'::regclass;
   relname 
  ---------
  (0 rows)
  
  -- Drop the global partitioned index.
  m_db=# DROP INDEX idx_student2;
  
  -- Drop the table.
  m_db=# DROP TABLE student;

Helpful Links

ALTER INDEX and DROP INDEX

Suggestions

• create index

  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
  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.

  • When a global index is created on a partitioned table, the following constraints apply:
    • Expression indexes are not supported.
    • Only B-tree indexes (Astore) are supported. UB-tree indexes are supported by Ustore.
  • In the same attribute column, the local index and global index of a partition cannot be specified at the same time.
  • 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.