操作步骤

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。

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';
                                                        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。

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;
                                                                         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。

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;
                                                                         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。