PG数据库中的执行计划

原创唐祖亮 2020-05-17

8472

1、PGSQL执行计划的查看方式

PGSQL中查看执行计划主要使用EXPLAIN命令


EXPLAIN [(option [,…])]statement
EXPLAIN[ANALYEZ][VERBOSE]statement
命令的可选项“options”为:

ANALYZE  [boolean]
VERBOSE  [boolean]
COST     [boolean]
BUFFERS  [boolean]
TIMING   [boolean]
FORMAT { TEXT | XML | JOSN | YAML }
复制

ANALYZE：选项通过实际执行的SQL来获得相应的执行计划。因为它真正被执行，所以可以看到执行计划每一步花掉了多少时间，以及它实际返回的行数目。
注意1：加上analyze选项后，会真正执行实际的SQL，如果SQL语句是一个插入、删除、更新或create table as语句，这些语句会修改数据库数据。为了不影响实际的数据，可以把EXPLAIN ANALYZE放到一个事务中，执行完后回滚事务，如下：
begin;
explain analyze …;
rollback;
VERBOSE（默认false）：选项用于显示计划的附加信息。这些附加信息有：计划树中每个节点输出的各个列，如果触发器被触发，还会输出触发器的名称。
COSTS（默认true）：选项显示每个计划节点的启动成本和总成本，以及估计行数和每行宽度。
TIMING(默认true）：analyze出现时可选。显示每个节点的启动时间和总时间花费。
BUFFERS（默认false）：选项显示关于缓冲区使用的信息。该参数只能与anlyze参数一起使用。显示的缓冲区信息包括共享块、本地块、和临时块读和写的块数。共享块、本地块、和临时块分别包含表和索引、临时表和临时索引，以及在排序和物化计划中使用的磁盘块。上层节点显示出来的块数包括其所有子节点使用的块数。

下面为执行计划示例：


explain  (analyze,buffers,verbose) select * from t1 where id=2;

testdb=> explain  (analyze,buffers,verbose) select * from t1 where id=2;
                                                     QUERY PLAN                                                      
---------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_t1_id on test.t1  (cost=0.14..8.16 rows=1 width=524) (actual time=0.020..0.021 rows=1 loops=1)
   Output: id, t, name
   Index Cond: (t1.id = 2)
   Buffers: shared hit=2
 Planning Time: 0.031 ms
 Execution Time: 0.031 ms
(6 rows)
复制

结果中“Index Scan using”表示索引扫描表“idx_t1_id”；
后面的内容“(cost=0.14…8.16 rows=1 width=524)”可以分为三部分：
1）“cost=0.14…8.16”：“cost=”后面两个数字，中间是由“…”分割，第一个数字“0.14”表示启动的成本（注意这里不是时间），也就是说返回第一行pgsql需要消耗多少cost值；第二个数字表示返回所有的数据的成本。注意：每个节点中的COST都包含该节点之前节点的COST并且都为预估值。

2）rows=1：表示会返回1行。

3）width=524：表示每行平均宽度为524byte（int=4byte，character varying=2byte）。

actual部分为真实的消耗，也可以分为三个部分：

1)“time=0.020…0.021”：“0.020”为返回第一条数据花费的真实时间，“0.021”为返回所有数据花费的时间。
2)“rows=1” ：真实的返回行数
3)“loops=1”：该步骤循环的次数

buffers：缓冲命中数
output：输出的字段名
planning time: 生成执行计划时间
execution time：执行执行计划时间

2、PGSQL执行计划的阅读

阅读顺序：

嵌套层次最深的，最先执行同样嵌套深度的，从上到下，先予执行每一步的cost包括上一步。

以下面的执行计划为例：


testdb=> explain  (analyze,buffers,verbose) select * from t1,t2 where t1.id=t2.id;
                                                 QUERY PLAN                                                  
-------------------------------------------------------------------------------------------------------------
1 Hash Join  (cost=1.07..13.02 rows=3 width=532) (actual time=0.050..0.052 rows=3 loops=1)
    Output: t1.id, t1.t, t1.name, t2.id, t2.salary
    Hash Cond: (t1.id = t2.id)
    Buffers: shared hit=5
2   ->  Seq Scan on test.t1  (cost=0.00..11.40 rows=140 width=524) (actual time=0.007..0.007 rows=3 loops=1)
          Output: t1.id, t1.t, t1.name
          Buffers: shared hit=1
3   ->  Hash  (cost=1.03..1.03 rows=3 width=8) (actual time=0.019..0.020 rows=3 loops=1)
          Output: t2.id, t2.salary
          Buckets: 1024  Batches: 1  Memory Usage: 9kB
         Buffers: shared hit=1
4         ->  Seq Scan on test.t2  (cost=0.00..1.03 rows=3 width=8) (actual time=0.010..0.011 rows=3 loops=1)
               Output: t2.id, t2.salary
               Buffers: shared hit=1
 Planning Time: 0.362 ms
 Execution Time: 0.078 ms
(16 rows)
复制

PG数据库的执行计划没有步骤编号，为了方便讲解这里我为每个步骤从上到下依次加上了编号。
我们现在来解读一下这个执行计划，先从上往下看，可以看到编号2和编号3这两个步骤嵌套深度是相同的。按照前面的阅读顺序规则，这里应该编号2
步骤先执行，然后再执行编号3步骤，再往下到了嵌套最深的编号4步骤，该步骤执行后最后执行编号1步骤。
最终该执行计划的执行顺序就是2->3->4->1。

知道了执行顺序还不够还需要知道每个步骤具体是在干什么，按照执行顺序来看，先看编号2步骤，'Seq Scan on test.t1’该句的意思是对test用户下的t1表进行全表扫描，后面的COST和ACTUAL部分前面已经解释过这里就不重复解释了。'Output: t1.id, t1.t, t1.name, t2.id, t2.salary’部分显示的是返回的表中哪些列。然后执行了编号3步骤，这里进行了HAHS运算把编号2步骤返回的结果生成了HASH表。然后执行编号4步骤，对t2表进行全表扫描。最后执行编号1步骤以t1表作为驱动表与T2表做HASH连接最终返回3行记录。


Explaining → 执行计划运算类型
     Seq Scan：   扫描表。
     Index Scan： 索引扫描。
     Bitmap Index Scan：索引扫描。
     Bitmap Heap Scan： 索引扫描。
     Subquery Scan： 子查询。
     Tid Scan：ctid = 以CTID为查询条件。
     Function Scan： 函数扫描。
     Nested Loop： 循环连接。
     Merge Join： 排序合并连接。
     Hash Join： 哈希连接。
     Sort：  排序，ORDER BY操作。
     Hash：  哈希运算。
     Result： 函数扫描，和具体的表无关。
     Unique： DISTINCT，UNION操作。
     Limit： LIMIT，OFFSET操作。有
     Aggregate： count, sum,avg, stddev集约函数。
     Group： GROUP BY分组操作。
     Append： UNION操作。
     Materialize： 子查询。
     SetOp： INTERCECT，EXCEPT
复制

postgresql