操作步骤

无索引和有索引性能对比

使用root用户登录数据库。

查看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（全表扫描）。

创建索引。

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

再次查看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.293 ms。

单列索引和复合索引的性能对比

使用root用户登录数据库。

创建单列索引。

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) (actual time=23.482..37.694 rows=2221 loops=1)
   Node/s: All datanodes
 Total runtime: 37.945 ms
(3 rows)

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

创建复合索引。

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) (actual time=4.266..6.390 rows=2221 loops=1)
   Node/s: All datanodes
 Total runtime: 6.616 ms
(3 rows)

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

父主题： 索引设计最佳实践（分布式）

上一篇：前置准备

下一篇：索引设计最佳实践（集中式）