このページは、お客様の言語ではご利用いただけません。Huawei Cloudは、より多くの言語バージョンを追加するために懸命に取り組んでいます。ご協力ありがとうございました。

Compute
Elastic Cloud Server
Huawei Cloud Flexus
Bare Metal Server
Auto Scaling
Image Management Service
Dedicated Host
FunctionGraph
Cloud Phone Host
Huawei Cloud EulerOS
Networking
Virtual Private Cloud
Elastic IP
Elastic Load Balance
NAT Gateway
Direct Connect
Virtual Private Network
VPC Endpoint
Cloud Connect
Enterprise Router
Enterprise Switch
Global Accelerator
Management & Governance
Cloud Eye
Identity and Access Management
Cloud Trace Service
Resource Formation Service
Tag Management Service
Log Tank Service
Config
OneAccess
Resource Access Manager
Simple Message Notification
Application Performance Management
Application Operations Management
Organizations
Optimization Advisor
IAM Identity Center
Cloud Operations Center
Resource Governance Center
Migration
Server Migration Service
Object Storage Migration Service
Cloud Data Migration
Migration Center
Cloud Ecosystem
KooGallery
Partner Center
User Support
My Account
Billing Center
Cost Center
Resource Center
Enterprise Management
Service Tickets
HUAWEI CLOUD (International) FAQs
ICP Filing
Support Plans
My Credentials
Customer Operation Capabilities
Partner Support Plans
Professional Services
Analytics
MapReduce Service
Data Lake Insight
CloudTable Service
Cloud Search Service
Data Lake Visualization
Data Ingestion Service
GaussDB(DWS)
DataArts Studio
Data Lake Factory
DataArts Lake Formation
IoT
IoT Device Access
Others
Product Pricing Details
System Permissions
Console Quick Start
Common FAQs
Instructions for Associating with a HUAWEI CLOUD Partner
Message Center
Security & Compliance
Security Technologies and Applications
Web Application Firewall
Host Security Service
Cloud Firewall
SecMaster
Anti-DDoS Service
Data Encryption Workshop
Database Security Service
Cloud Bastion Host
Data Security Center
Cloud Certificate Manager
Edge Security
Situation Awareness
Managed Threat Detection
Blockchain
Blockchain Service
Web3 Node Engine Service
Media Services
Media Processing Center
Video On Demand
Live
SparkRTC
MetaStudio
Storage
Object Storage Service
Elastic Volume Service
Cloud Backup and Recovery
Storage Disaster Recovery Service
Scalable File Service Turbo
Scalable File Service
Volume Backup Service
Cloud Server Backup Service
Data Express Service
Dedicated Distributed Storage Service
Containers
Cloud Container Engine
Software Repository for Container
Application Service Mesh
Ubiquitous Cloud Native Service
Cloud Container Instance
Databases
Relational Database Service
Document Database Service
Data Admin Service
Data Replication Service
GeminiDB
GaussDB
Distributed Database Middleware
Database and Application Migration UGO
TaurusDB
Middleware
Distributed Cache Service
API Gateway
Distributed Message Service for Kafka
Distributed Message Service for RabbitMQ
Distributed Message Service for RocketMQ
Cloud Service Engine
Multi-Site High Availability Service
EventGrid
Dedicated Cloud
Dedicated Computing Cluster
Business Applications
Workspace
ROMA Connect
Message & SMS
Domain Name Service
Edge Data Center Management
Meeting
AI
Face Recognition Service
Graph Engine Service
Content Moderation
Image Recognition
Optical Character Recognition
ModelArts
ImageSearch
Conversational Bot Service
Speech Interaction Service
Huawei HiLens
Video Intelligent Analysis Service
Developer Tools
SDK Developer Guide
API Request Signing Guide
Terraform
Koo Command Line Interface
Content Delivery & Edge Computing
Content Delivery Network
Intelligent EdgeFabric
CloudPond
Intelligent EdgeCloud
Solutions
SAP Cloud
High Performance Computing
Developer Services
ServiceStage
CodeArts
CodeArts PerfTest
CodeArts Req
CodeArts Pipeline
CodeArts Build
CodeArts Deploy
CodeArts Artifact
CodeArts TestPlan
CodeArts Check
CodeArts Repo
Cloud Application Engine
MacroVerse aPaaS
KooMessage
KooPhone
KooDrive
Updated on 2023-10-23 GMT+08:00

Creating and Managing Indexes

Background

Indexes accelerate data access but increase the processing time of insertion, update, and deletion operations. Therefore, before creating an index, consider whether it is necessary and select the columns where indexes are to be created. 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, you must correctly identify which columns require indexes. You are advised to create indexes for any of the following columns:

  • Columns that are often searched and queried. This speeds up searches.
  • Columns that function as primary keys. This enforces the uniqueness of the columns and the data collation structures in organized tables.
  • 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.
  • Columns where the WHERE clause is executed frequently. This speeds up condition judgment.
  • Columns that often appear 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 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, 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.
  • When logical replication is enabled, if you need to create a primary key index that contains system columns, you must set the REPLICA IDENTITY attribute of the table to FULL or use USING INDEX to specify a unique, non-local, non-deferrable index that does not contain system columns and contains only columns marked NOT NULL.

Procedure

For details about how to create a partitioned table, see Creating and Managing Partitioned Tables.

  • Create an index.
    • Create the local index tpcds_web_returns_p2_index1 without specifying the partition name.
      1
      openGauss=# 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 local index tpcds_web_returns_p2_index2 with the partition name specified.
       1
       2
       3
       4
       5
       6
       7
       8
       9
      10
      11
      openGauss=# 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
      
    • 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;
  • Modify the tablespace of an index partition.
    • Change the tablespace of index partition web_returns_p2_P2_index to example1.
      1
      openGauss=# ALTER INDEX tpcds.tpcds_web_returns_p2_index2 MOVE PARTITION web_returns_p2_P2_index TABLESPACE example1;
      

      If the following information is displayed, the tablespace of the index partition has been modified:

      1
      ALTER INDEX
      
    • Change the tablespace of index partition web_returns_p2_P3_index to example2.
      1
      openGauss=# ALTER INDEX tpcds.tpcds_web_returns_p2_index2 MOVE PARTITION web_returns_p2_P3_index TABLESPACE example2;
      

      If the following information is displayed, the tablespace of the index partition has been modified:

      1
      ALTER INDEX
      
  • Rename an index partition.
    Rename the name of index partition web_returns_p2_P8_index to web_returns_p2_P8_index_new.
    1
    openGauss=# 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 partition has been renamed:

    1
    ALTER INDEX
    
  • Query indexes.
    • Run the following command to query all indexes defined by the system and users:
      1
      openGauss=# SELECT RELNAME FROM PG_CLASS WHERE RELKIND='i' or RELKIND='I';
      
    • Run the following command to query information about a specified index:
      1
      openGauss=# \di+ tpcds.tpcds_web_returns_p2_index2 
      
  • Delete indexes.
    1
    2
    openGauss=# DROP INDEX tpcds.tpcds_web_returns_p2_index1;
    openGauss=# DROP INDEX tpcds.tpcds_web_returns_p2_index2;
    

    If the following information is displayed, the indexes have been deleted:

    1
    DROP INDEX
    

GaussDB 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, which increases the data operation load. Therefore, delete unnecessary indexes periodically.
Table 1 Indexing method

Indexing Method

Description

Unique index

An index that requires the uniqueness of an index attribute or an attribute group. If a table declares unique constraints or primary keys, GaussDB automatically creates unique indexes (or composite indexes) for columns that form the primary keys or unique constraints. Currently, unique indexes can be created only for the B-tree and UB-tree in GaussDB.

Composite index

An index that can be defined for multiple attributes of a table. Currently, the B-tree in GaussDB supports multi-column indexes.

Partial index

An index that can be created for subsets of a table. This indexing method contains only tuples that meet condition expressions.

Expression index

An index that is built on a function or expression calculated based on one or more attributes of a table. An expression index works only when the queried expression is the same as the created expression.

  • Create an ordinary table.
    1
    2
    openGauss=# 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
    openGauss=# 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 poor. 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 deletion operation performance in the database.

    Run the following command to create an index:

    1
    2
    openGauss=# 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.

    openGauss=# CREATE UNIQUE INDEX ds_ship_mode_t1_index1 ON tpcds.ship_mode_t1(SM_SHIP_MODE_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 commands:
    1
    openGauss=# 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 composite index on ca_address_sk and ca_street_number columns:
    1
    2
    openGauss=# 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 whose ca_address_sk is 5050, you can create a partial index to facilitate your query.

    1
    2
    openGauss=# CREATE INDEX part_index ON tpcds.customer_address_bak(ca_address_sk) WHERE ca_address_sk = 5050;
    CREATE INDEX
    
  • Create an expression index.
    Assume that you need to frequently query records with ca_street_number smaller than 1000, run the following command:
    1
    openGauss=# 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
    openGauss=# CREATE INDEX para_index ON tpcds.customer_address_bak (trunc(ca_street_number));
    CREATE INDEX
    
  • Delete the tpcds.customer_address_bak table.
    1
    2
    openGauss=# DROP TABLE tpcds.customer_address_bak;
    DROP TABLE
    

We use cookies to improve our site and your experience. By continuing to browse our site you accept our cookie policy. Find out more