Indexes

Overview

An index is a data structure used in a database to accelerate data retrieval. By creating pointers to data in a table, the database can quickly locate and access specific rows without scanning the entire table. Indexes function similarly to a book catalog, which helps users quickly locate the desired parts and significantly improves query performance. Using indexes helps accelerate data access but can also prolong the time needed to insert, update, or delete data. Therefore, it is crucial to carefully evaluate whether to create indexes for a table and which specific columns to index.

Index Type

• B-tree: a structure similar to B+ tree. The leaf nodes store data in the sequence of key values. It is applicable to equality query, range query, sorting, and grouping.
• UB-tree: multi-version B-tree index used only for Ustore tables. The index page contains transaction information and can be automatically recycled. By default, the INSERTPT feature is enabled for UB-tree indexes.

Examples

• Single-column index
  An index created based on a single column, which is applicable to simple query conditions.

  -- Create a table.
  gaussdb=# CREATE TABLE tb_t1(c1 int, c2 int);
  
  -- Create an index.
  gaussdb=# CREATE INDEX idx_t1_c1 ON tb_t1(c1);
  
  -- Drop the table.
  gaussdb=# DROP TABLE tb_t1;

• Composite index
  A composite index created based on multiple columns, which is applicable to multi-condition queries.

  -- Create a table.
  gaussdb=# CREATE TABLE tb_t2(c1 int, c2 int, c3 int);
  
  -- Create an index.
  gaussdb=# CREATE INDEX idx_t2_c1_c2 ON tb_t2(c1,c2);
  
  -- Drop the table.
  gaussdb=# DROP TABLE tb_t2;

• Unique index
  An index created based on a unique column that allows null values. When a unique constraint is added to a table, the database automatically adds a unique index to the table.

  -- Create a table.
  gaussdb=# CREATE TABLE tb_userinfo(uid char(5), name varchar(20));
  
  -- Create a unique index.
  gaussdb=# CREATE UNIQUE INDEX idx_userinfo_uid ON tb_userinfo(uid);
  
  -- Drop the table.
  gaussdb=# DROP TABLE tb_userinfo;

• Expression index
  An index created based on the result of a calculated expression or function instead of the original columns in a table. It allows for fast retrieval of some calculated or converted values and is applicable when the query condition contains functions or expressions.

  -- Create a table and insert data into the table.
  gaussdb=# CREATE TABLE tb_test(c1 varchar,c2 varchar);
  gaussdb=# INSERT INTO tb_test VALUES ('AAa','AAA12');
  gaussdb=# INSERT INTO tb_test VALUES ('ABa','AAA13');
  gaussdb=# INSERT INTO tb_test VALUES ('AAc','AAA14');
  
  -- Create an index.
  gaussdb=# CREATE INDEX idx_test_c1_lower ON tb_test(lower(c1));
  
  -- Query.
  gaussdb=# SELECT * FROM tb_test WHERE lower(c1) = 'aac';
  
  -- Drop the table.
  gaussdb=# DROP TABLE tb_test;

• Partial index
  An index created only for data that meets specific conditions in a table. Using such indexes can reduce the index size and improve write performance.

  -- Create a table and insert data into the table.
  gaussdb=# CREATE TABLE tb_users(uid varchar, name varchar, is_active boolean);
  gaussdb=# INSERT INTO tb_users VALUES('bc'||generate_series(1,100),'uname'||generate_series(1,100),true);
  gaussdb=# INSERT INTO tb_users VALUES('ac'||generate_series(1,50),'uname'||generate_series(1,50),false);
  
  -- Create an index.
  gaussdb=# CREATE INDEX idx_users_uid_act ON tb_users(uid) WHERE is_active = true;
  
  -- Partial index is automatically used during queries.
  gaussdb=# SELECT * FROM tb_users WHERE uid = 'bc001' AND is_active = true;
  
  -- Drop the table.
  gaussdb=# DROP TABLE tb_users;

• 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)
  
  -- Drop 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)
  
  -- Drop the global 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)
  
  -- Drop the local expression index.
  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)
  
  -- Drop the global expression index.
  gaussdb=# DROP INDEX idx_student4;
  
  -- Drop the table.
  gaussdb=# DROP TABLE student;