# 操作步骤
#### 无索引和有索引性能对比
1. 使用root用户登录数据库。
2. 查看test_table表执行计划。 
   ```
   gaussdb=# EXPLAIN ANALYZE SELECT * FROM test_table WHERE email = 'user_500000@example.com';
                       QUERY PLAN                    
   --------------------------------------------------
    Data Node Scan  (cost=0.00..0.00 rows=0 width=0)
      Node/s: All datanodes
   (2 rows)
   Time: 167.579 ms
   ```
   从执行结果来看，执行时间需要167.579ms。
   
   
3. 创建索引。 
   ```
   gaussdb=# CREATE INDEX idx_test_table_email ON test_table(email);
   CREATE INDEX
   ```
   
   
4. 再次查看test_table表执行计划。 
   ```
   gaussdb=# EXPLAIN ANALYZE SELECT * FROM test_table WHERE email = 'user_500000@example.com';
                                                           QUERY PLAN                                                         
   ---------------------------------------------------------------------------------------------------------------------------
    Data Node Scan  (cost=0.00..0.00 rows=0 width=0)
      Node/s: All datanodes
    Remote SQL: SELECT id, name, email, created_at FROM public.test_table WHERE email::text = 'user_500000@example.com'::text
    Datanode Name: dn_6001
      [Bypass]
      Index Scan using idx_test_table_email on test_table  (cost=0.00..2.47 rows=1 width=46)
        Index Cond: ((email)::text = 'user_500000@example.com'::text)
    Datanode Name: dn_6002
      [Bypass]
      Index Scan using idx_test_table_email on test_table  (cost=0.00..2.47 rows=1 width=46)
        Index Cond: ((email)::text = 'user_500000@example.com'::text)
   (14 rows)
   Time: 18.467 ms
   ```
   添加索引后，通过与无索引时执行计划的对比，查询时间从原来的167.579ms缩短到18.467ms。
   
   
 
#### 单列索引和复合索引的性能对比
1. 使用root用户登录数据库。
2. 创建单列索引。 
   ```
   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. 查看执行计划。 
   ```
   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)
      Node/s: All datanodes
    Remote SQL: SELECT record_id, region_id, store_id, product_id, sale_date, amount, is_refund FROM public.sales_records WHERE region_id = 5 AND store_id = 42
    Datanode Name: dn_6001
      Bitmap Heap Scan on sales_records  (cost=1354.75..2501.82 rows=1160 width=31)
        Recheck Cond: ((store_id = 42) AND (region_id = 5))
        ->  BitmapAnd  (cost=1354.75..1354.75 rows=1160 width=0)
              ->  Bitmap Index Scan on idx_store  (cost=0.00..118.79 rows=10526 width=0)
                    Index Cond: (store_id = 42)
              ->  Bitmap Index Scan on idx_region  (cost=0.00..1235.13 rows=110237 width=0)
                    Index Cond: (region_id = 5)
    Datanode Name: dn_6002
      Bitmap Heap Scan on sales_records  (cost=1325.15..2406.59 rows=1087 width=31)
        Recheck Cond: ((store_id = 42) AND (region_id = 5))
        ->  BitmapAnd  (cost=1325.15..1325.15 rows=1087 width=0)
              ->  Bitmap Index Scan on idx_store  (cost=0.00..113.05 rows=10053 width=0)
                    Index Cond: (store_id = 42)
              ->  Bitmap Index Scan on idx_region  (cost=0.00..1211.31 rows=108088 width=0)
                    Index Cond: (region_id = 5)
   (22 rows)
   Time: 28.455 ms
   ```
   从执行结果来看，执行时间需要28.455ms。
   
   
4. 创建复合索引。 
   ```
   gaussdb=# CREATE INDEX idx_region_store ON sales_records(region_id, store_id);
   CREATE INDEX
   ```
   
   
5. 再次查看执行计划。 
   ```
   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)
      Node/s: All datanodes
    Remote SQL: SELECT record_id, region_id, store_id, product_id, sale_date, amount, is_refund FROM public.sales_records WHERE region_id = 5 AND store_id = 42
    Datanode Name: dn_6001
      Bitmap Heap Scan on sales_records  (cost=16.54..1163.61 rows=1160 width=31)
        Recheck Cond: ((region_id = 5) AND (store_id = 42))
        ->  Bitmap Index Scan on idx_region_store  (cost=0.00..16.25 rows=1160 width=0)
              Index Cond: ((region_id = 5) AND (store_id = 42))
    Datanode Name: dn_6002
      Bitmap Heap Scan on sales_records  (cost=15.79..1097.23 rows=1087 width=31)
        Recheck Cond: ((region_id = 5) AND (store_id = 42))
        ->  Bitmap Index Scan on idx_region_store  (cost=0.00..15.52 rows=1087 width=0)
              Index Cond: ((region_id = 5) AND (store_id = 42))
   (16 rows)
   Time: 6.856 ms
   ```
   通过对单列索引和复合索引执行计划的对比，查询时间从原来的28.455ms缩短到6.856ms。