# 操作步骤 #### 无索引和有索引性能对比 1. 使用root用户登录数据库。 2. 查看test_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) ``` 从执行结果来看，执行时间需要382.624ms。 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'; 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。 #### 单列索引和复合索引的性能对比 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; 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。 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; 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。