Help Center/ GaussDB/ Developer Guide(Distributed_V2.0-3.x)/ Development and Design Proposal/ Database Design Specifications/ Index Design Specifications
Updated on 2025-03-13 GMT+08:00
View PDF
Share

Index Design Specifications

  • [Specification] Use the index types in the recommended database index practices.

    Recommended types must be used for index design.

    Table 1 Recommended database index practices

    Index Type

    Description

    Recommended or Not

    Primary key or unique index

    Single-column or multi-column primary key or unique index.

    Recommended

    Global index

    Index organization method.

    In planning

    Expression index

    An index column is a function or scalar expression calculated from one or multiple columns in a table.

    Restricted

    B-tree index

    Index building type.

    Recommended for some data types
  • [Rule] A composite index contains a maximum of 5 columns.
  • [Rule] A composite index contains a maximum of 200 characters.
  • [Rule] Index (including single-column index and composite index) columns should be NOT NULL.
  • [Recommendation] Do not create multiple unique indexes for a single table.

    Maintaining multiple unique indexes at the same time generates more overheads than maintaining a multi-column unique index. If multiple unique indexes are equivalent to a multi-column unique index in service logic, a multi-column unique index shall be preferred.

  • [Recommendation] The efficiency of maintaining indexes created for the same column is different. Columns of the number type are better than those of the character type and other data types. Therefore, it is recommended that columns such as IDs and time for creating indexes be stored as data of the number type.
  • [Recommendation] Create indexes on joined columns.

    HASH JOIN is supported, whereas NESTLOOP JOIN may be used for join operations if the rescan cost is low (for example, the inner table is small). If the NESTLOOP JOIN plan can be viewed by executing EXPLAIN, you can create indexes on joined columns to improve the efficiency of executing NESTLOOP JOIN.

  • [Recommendation] When creating a composite index, you need to create it based on query conditions and the leftmost match principle of the composite index.
    • Leftmost match principle of a composite index: If a query condition contains one or more columns of a composite index, the leftmost consecutive columns of the composite index must match the query condition.
    • When the query is WHERE a = ?, b = ?, c = ?, d = ? or WHERE a = ?, b = ?, c = ?, the optimal index match is used. However, when the query is WHERE b = ?, c = ?, d = ? or WHERE c = ?, d = ?, the idx_test_abcd index might still be used after cost calculation. In such cases, the index scan will involve all pages of the index, resulting in unsatisfactory SQL performance. In similar cases, you are advised to create a composite index suitable for the query condition based on the leftmost match principle. 
      -- Create the test table.
gaussdb=# CREATE TABLE test(a int, b int, c int, d int, e int, f text);
Create a composite index.
gaussdb=# CREATE INDEX idx_test_abcd ON test(a,b,c,d);