Updated on 2025-09-04 GMT+08:00

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