Help Center/ GaussDB/ Best Practices/ Best Practices for Index Design/ Best Practices for Index Design (Distributed Instances)/ Procedure
Updated on 2025-09-04 GMT+08:00
View PDF
Share

Procedure

Performance Comparison Between Tables with and Without Indexes

  1. Log in to the database as the root user.
  2. Check the execution plan of the test_table table. 

    gaussdb=# EXPLAIN ANALYZE SELECT * FROM test_table WHERE email = 'user_500000@example.com';
 id |         operation          | A-time  | A-rows | E-rows | Peak Memory | A-width | E-width |     E-cost
s      
----+----------------------------+---------+--------+--------+-------------+---------+---------+-----------
-------
  1 | ->  Seq Scan on test_table | 382.457 |      0 |   1989 | 19KB        |         |     148 | 0.000..136
44.650
(1 row)

           Predicate Information (identified by plan id)           
-------------------------------------------------------------------
   1 --Seq Scan on test_table
         Filter: ((email)::text = 'user_500000@example.com'::text)
         Rows Removed by Filter: 1000000
(3 rows)

       ====== Query Summary =====       
----------------------------------------
 Datanode executor start time: 0.037 ms
 Datanode executor run time: 382.544 ms
 Datanode executor end time: 0.017 ms
 Planner runtime: 0.391 ms
 Query Id: 1945836514001883020
 Total runtime: 382.624 ms
(6 rows)

    The execution plan indicates that the query needs 382.624 ms (full table scan).

  3. Create an index. 

    gaussdb=# CREATE INDEX idx_test_table_email ON test_table(email);
CREATE INDEX

  4. Check the execution plan of the test_table table again. 

    gaussdb=# EXPLAIN ANALYZE SELECT * FROM test_table WHERE email = 'user_500000@example.com';
 id |                        operation                        | A-time | A-rows | E-rows | Peak Memory | A-
width | E-width |   E-costs    
----+---------------------------------------------------------+--------+--------+--------+-------------+---
------+---------+--------------
  1 | ->  Index Scan using idx_test_table_email on test_table | 0.163  |      0 |      1 | 75KB        |   
      |      46 | 0.000..8.268
(1 row)

             Predicate Information (identified by plan id)             
-----------------------------------------------------------------------
   1 --Index Scan using idx_test_table_email on test_table
         Index Cond: ((email)::text = 'user_500000@example.com'::text)
(2 rows)

       ====== Query Summary =====       
----------------------------------------
 Datanode executor start time: 0.063 ms
 Datanode executor run time: 0.190 ms
 Datanode executor end time: 0.013 ms
 Planner runtime: 0.936 ms
 Query Id: 1945836514001885197
 Total runtime: 0.293 ms
(6 rows)

 ====== Query Others ===== 
---------------------------
 Bypass: Yes
(1 row)

    The query time decreases from 382.624 ms (without indexes) to 0.293 ms (with an index).

Performance Comparison Between Single-Column Indexes and Composite Indexes

  1. Log in to the database as the root user.
  2. Create single-column indexes. 

    gaussdb=# CREATE INDEX idx_region ON sales_records(region_id);
CREATE INDEX
gaussdb=# CREATE INDEX idx_store ON sales_records(store_id);
CREATE INDEX

  3. Check the execution plan. 

    gaussdb=# EXPLAIN ANALYZE SELECT * FROM sales_records WHERE region_id = 5 AND store_id = 42;
                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 Data Node Scan  (cost=0.00..0.00 rows=0 width=0) (actual time=23.482..37.694 rows=2221 loops=1)
   Node/s: All datanodes
 Total runtime: 37.945 ms
(3 rows)

    The execution plan indicates that the query needs 37.945 ms.

  4. Create a composite index. 

    gaussdb=# CREATE INDEX idx_region_store ON sales_records(region_id, store_id);
CREATE INDEX

  5. Check the execution plan again. 

    gaussdb=# EXPLAIN ANALYZE SELECT * FROM sales_records WHERE region_id = 5 AND store_id = 42;
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Data Node Scan  (cost=0.00..0.00 rows=0 width=0) (actual time=4.266..6.390 rows=2221 loops=1)
   Node/s: All datanodes
 Total runtime: 6.616 ms
(3 rows)

    The query time decreases from 37.945 ms (with single-column indexes) to 6.616 ms (with a composite index).