操作步骤

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)

从执行结果来看，执行时间需要382.624ms。

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

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)

添加索引后，通过与无索引时执行计划的对比，查询时间从原来的382.624ms缩短到0.293ms。

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

gaussdb=# EXPLAIN ANALYZE SELECT * FROM sales_records WHERE region_id = 5 AND store_id = 42;
 id |                  operation                   | A-time | A-rows | E-rows | Peak Memory | A-width | E-width |     E-costs      
----+----------------------------------------------+--------+--------+--------+-------------+---------+---------+------------------
  1 | ->  Bitmap Heap Scan on sales_records        | 50.225 |   2217 |     50 | 20KB        |         |      45 | 232.175..287.628
  2 |    ->  BitmapAnd                             | 44.209 |      0 |     50 | 640BYTE     |         |       0 | 232.175..232.175
  3 |       ->  Bitmap Index Scan using idx_store  | 4.905  |  20108 |  10000 | 1410KB      |         |       0 | 0.000..115.950
  4 |       ->  Bitmap Index Scan using idx_region | 38.234 | 221854 |  10000 | 5706KB      |         |       0 | 0.000..115.950
(4 rows)


                        Predicate Information (identified by plan id)                        
---------------------------------------------------------------------------------------------
   1 --Bitmap Heap Scan on sales_records
         Recheck Cond: ((store_id = 42) AND (region_id = 5)), (Expression Flatten Optimized)
   3 --Bitmap Index Scan using idx_store
         Index Cond: (store_id = 42)
   4 --Bitmap Index Scan using idx_region
         Index Cond: (region_id = 5)
(6 rows)


       ====== Query Summary =====       
----------------------------------------
 Datanode executor start time: 0.061 ms
 Datanode executor run time: 50.617 ms
 Datanode executor end time: 0.023 ms
 Planner runtime: 0.574 ms
 Query Id: 1946117988981419190
 Total runtime: 50.717 ms
(6 rows)

从执行结果来看，执行时间需要50.717ms。

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

gaussdb=# EXPLAIN ANALYZE SELECT * FROM sales_records WHERE region_id = 5 AND store_id = 42;
 id |                    operation                    | A-time | A-rows | E-rows | Peak Memory | A-width | E-width |     E-costs      
----+-------------------------------------------------+--------+--------+--------+-------------+---------+---------+------------------
  1 | ->  Bitmap Heap Scan on sales_records           | 6.029  |   2217 |   2293 | 20KB        |         |      35 | 33.653..2320.157
  2 |    ->  Bitmap Index Scan using idx_region_store | 1.018  |   2217 |   2293 | 355KB       |         |       0 | 0.000..33.080
(2 rows)


                        Predicate Information (identified by plan id)                        
---------------------------------------------------------------------------------------------
   1 --Bitmap Heap Scan on sales_records
         Recheck Cond: ((region_id = 5) AND (store_id = 42)), (Expression Flatten Optimized)
   2 --Bitmap Index Scan using idx_region_store
         Index Cond: ((region_id = 5) AND (store_id = 42))
(4 rows)


       ====== Query Summary =====       
----------------------------------------
 Datanode executor start time: 0.070 ms
 Datanode executor run time: 6.418 ms
 Datanode executor end time: 0.057 ms
 Planner runtime: 0.971 ms
 Query Id: 1946117988981419725
 Total runtime: 6.561 ms
(6 rows)

通过对单列索引和复合索引执行计划的对比，查询时间从原来的50.717ms缩短到6.561ms。