怎样分析PostgreSQL的执行计划

经常有人问我如何看懂执行计划，如何才能抓住计划中的瓶颈点，要完全搞懂一个执行计划还是需要丰富的背景知识，但是对于测试或开发并没有完备的数据库知识，那么怎样看懂PostgreSQL的执行计划呢，这里介绍一个网站，以图形和数据分析的方式解释每个执行计划，小白也能轻松掌握，抓住瓶颈优化SQL。

构造测试数据

postgres=# SELECT version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 13.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)

--创建100个班级
postgres=# CREATE TABLE t_class(classid INT,classname VARCHAR(30),createtime TIMESTAMP(0) NOT NULL DEFAULT NOW());
CREATE TABLE
postgres=# INSERT INTO t_class(classid,classname) SELECT t.id,'一年级' || t.id || '班' FROM (SELECT generate_series(1,100) AS id) t;
INSERT 0 100

--每个班级50个学生
postgres=# CREATE TABLE t_student(studnetid INT,studentname VARCHAR(30),classid INT,createtime TIMESTAMP(0) NOT NULL DEFAULT NOW());
CREATE TABLE
postgres=# INSERT INTO t_student(studnetid,studentname,classid) 
postgres-# SELECT row_number() over(),substr(md5(random()::text),1,5),c.classid FROM t_class c,(SELECT generate_series(1,50) AS id) t;
INSERT 0 5000

postgres=# TABLE t_class LIMIT 10;
 classid | classname  |     createtime      
---------+------------+---------------------
       1 | 一年级1班  | 2021-07-21 10:15:44
       2 | 一年级2班  | 2021-07-21 10:15:44
       3 | 一年级3班  | 2021-07-21 10:15:44
       4 | 一年级4班  | 2021-07-21 10:15:44
       5 | 一年级5班  | 2021-07-21 10:15:44
       6 | 一年级6班  | 2021-07-21 10:15:44
       7 | 一年级7班  | 2021-07-21 10:15:44
       8 | 一年级8班  | 2021-07-21 10:15:44
       9 | 一年级9班  | 2021-07-21 10:15:44
      10 | 一年级10班 | 2021-07-21 10:15:44
(10 rows)

postgres=# TABLE t_student LIMIT 10;
 studnetid | studentname | classid |     createtime      
-----------+-------------+---------+---------------------
         1 | 9a443       |       1 | 2021-07-21 10:15:44
         2 | 1fee5       |       1 | 2021-07-21 10:15:44
         3 | 244dd       |       1 | 2021-07-21 10:15:44
         4 | 99fee       |       1 | 2021-07-21 10:15:44
         5 | aa69b       |       1 | 2021-07-21 10:15:44
         6 | a6b51       |       1 | 2021-07-21 10:15:44
         7 | bec83       |       1 | 2021-07-21 10:15:44
         8 | 084ef       |       1 | 2021-07-21 10:15:44
         9 | 8ceae       |       1 | 2021-07-21 10:15:44
        10 | e6827       |       1 | 2021-07-21 10:15:44
(10 rows)

postgres=# VACUUM ANALYZE t_class,t_student;
VACUUM

--生成JSON格式的计划
postgres=# EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
postgres-# SELECT * FROM t_class c,t_student s WHERE c.classid = s.classid AND s.studentname = '8ceae';
                                                         QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
 [                                                                                                                         +
   {                                                                                                                       +
     "Plan": {                                                                                                             +
       "Node Type": "Nested Loop",                                                                                         +
       "Parallel Aware": false,                                                                                            +
       "Join Type": "Inner",                                                                                               +
       "Startup Cost": 0.00,                                                                                               +
       "Total Cost": 97.75,                                                                                                +
       "Plan Rows": 1,                                                                                                     +
       "Plan Width": 48,   ...计划太长，此处省略一万字
复制

分析执行计划

访问 https://explain.dalibo.com/ 网站，把以上生成的执行计划贴到此网站，提交即可

这里就可以看到图形化的执行计划了，页面信息非常丰富，应有尽有

可以点开各个节点展开详细信息（节点中的选项卡可以切换General\IO&Buffers\Output\Workers\Misc等）

这里是执行计划的文本信息

这里是查询语句

这个页面查看执行计划的统计信息，包括每个表的统计信息，节点统计信息，索引统计信息等，这里可以清晰的分析出性能的瓶颈，这里很明显顺序扫描t_student表时间占比达到了92%（1.28ms），在查询条件上面应该建一个索引

postgres=# CREATE INDEX idx_studentname_t_student ON t_student(studentname);
CREATE INDEX
postgres=# VACUUM ANALYZE t_student;
VACUUM
复制

查看建索引后的执行计划，执行时间从1.38毫秒下降到了0.168毫秒，对t_student表的扫描占比下降到了21%（0.036ms）

总结

以上是个很简单的例子，不借助上面的网站也容易分析，但是如果对于那种成百上千行的SQL语句，即使是老司机文本分析也不是那么简单，有这么好的工具我们应该多加利用来提高生产力。