postgresql explain的初步分析

os: centos 7.4
postgresql: 9.6.8

explain 是 postgresql 查看执行计划最直接的方式。

explain 语法

EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

这里 option可以是：

    ANALYZE [ boolean ]
    VERBOSE [ boolean ]
    COSTS [ boolean ]
    BUFFERS [ boolean ]
    TIMING [ boolean ]
    FORMAT { TEXT | XML | JSON | YAML }

explain 查看

现在有 user，role，user_role 三个表的关联，来获取登录用户的角色权限。
如下

mondb=# explain
SELECT tu.user_id,
       tu.user_first_name::text || tu.user_last_name::text AS user_name,
       tr.role_id,
       tr.role_name
  FROM t_base_user tu,
       t_base_role tr,
       t_base_user_role tur
 WHERE 1 = 1 
   AND tu.user_id = tur.user_id 
   AND tr.role_id = tur.role_id 
   AND tu.user_status = '1' 
   AND tr.role_status = '1'
 ORDER BY tr.role_id, 
          tu.user_id;

                                      QUERY PLAN                                       
---------------------------------------------------------------------------------------
 Sort  (cost=3.94..3.97 rows=9 width=57)
   Sort Key: tr.role_id, tu.user_id
   ->  Hash Join  (cost=2.41..3.80 rows=9 width=57)
         Hash Cond: ((tur.role_id)::text = (tr.role_id)::text)
         ->  Hash Join  (cost=1.24..2.48 rows=9 width=19)
               Hash Cond: ((tur.user_id)::text = (tu.user_id)::text)
               ->  Seq Scan on t_base_user_role tur  (cost=0.00..1.11 rows=11 width=9)
               ->  Hash  (cost=1.12..1.12 rows=9 width=16)
                     ->  Seq Scan on t_base_user tu  (cost=0.00..1.12 rows=9 width=16)
                           Filter: ((user_status)::text = '1'::text)
         ->  Hash  (cost=1.09..1.09 rows=7 width=19)
               ->  Seq Scan on t_base_role tr  (cost=0.00..1.09 rows=7 width=19)
                     Filter: ((role_status)::text = '1'::text)
(13 rows)

mondb=# 

以这一行为例：-> Seq Scan on t_base_user_role tur (cost=0.00..1.11 rows=11 width=9) ，说下每行后面类似 (cost=0.00..1.11 rows=11 width=9) 所代表的含义。
有这个输出是因为 option 的 COSTS 默认为 true，即 explain 等价于 explain (COSTS true)

那这个 COSTS 的具体含义是：包括每一个计划结点的估计启动和总代价，以及估计的行数和每行的宽度。这个参数默认被设置为TRUE。

显示中最重要的部分是估计出的语句执行代价，它是计划器对于该语句要运行多久的猜测（以任意的代价单位度量，但是习惯上表示取磁盘页面的次数）。
事实上会显示两个数字：在第一行能被返回前的启动代价，以及返回所有行的总代价。
对于大部分查询来说总代价是最重要的，但是在一些情景中（如EXISTS中的子查询），计划器将选择更小的启动代价来代替最小的总代价（因为因为执行器将在得到一行后停止）。此外，如果你用一个LIMIT子句限制返回行的数量，计划器会在终端代价之间做出适当的插值来估计到底哪个计划是真正代价最低的。

cost

总时间(单位：毫秒)
cost=0.00..1.11 是怎么计算出来的？估计很多人都想搞清楚。
其中：
0.00代表估计的启动开销。在输出阶段可以开始之前消耗的时间，例如在一个排序结点里执行排序的时间。
1.11代表估计的总开销。这个估计值基于的假设是计划结点会被运行到完成，即所有可用的行都被检索。实际上一个结点的父结点可能很快停止读所有可用的行

这里涉及了postgresql 几个参数：

seq_page_cost：       连续块扫描操作的单个块的cost. 例如全表扫描
random_page_cost：    随机块扫描操作的单个块的cost. 例如索引扫描
cpu_tuple_cost：      处理每条记录的CPU开销（tuple：关系中的一行记录）
cpu_index_tuple_cost：扫描每个索引条目带来的CPU开销
cpu_operator_cost：   操作符或函数带来的CPU开销.(需要注意函数以及操作符对应的函数的三态, 执行计划会根据三态做优化, 关系到多条记录时三态对应的调用次数是需要关心的)

查看当前设置

mondb=# select name,setting from pg_settings ps
  where ps.name in ('seq_page_cost','random_page_cost','cpu_tuple_cost','cpu_index_tuple_cost','cpu_operator_cost');

         name         | setting 
----------------------+---------
 cpu_index_tuple_cost | 0.005
 cpu_operator_cost    | 0.0025
 cpu_tuple_cost       | 0.01
 random_page_cost     | 4
 seq_page_cost        | 1
(5 rows)

总成本=seq_page_cost*relpages+cpu_tuple_cost*reltuples

mondb=# select relpages,reltuples from pg_class where relname='t_base_user_role';
 relpages | reltuples 
----------+-----------
        1 |        11
(1 row)

带入公式:

1(seq_page_cost)*1(relpages)+0.01(cpu_tuple_cost)*11(reltuples)=1.11

有过滤条件的公式，比如 >=，多了一个 cpu_operation_cost 的成本。
1(seq_page_cost)*1(relpages)+0.01(cpu_tuple_cost)*11(reltuples)+0.0025(cpu_operation_cost)*11(reltuples)

rows

计划结点输出行数的估计值。同样，也假定该结点能运行到完成。
rows=11

mondb=# select reltuples from pg_class where relname='t_base_user_role';
 reltuples 
-----------
        11
(1 row)

width

计划结点输出的行平均宽度（以字节计算）
width=9

mondb=# select sum(length(user_id)),
       sum(length(role_id)),
       (sum(length(user_id)) + sum(length(role_id))) /count(1)
  from t_base_user_role;

 sum | sum | ?column? 
-----+-----+----------
  59 |  30 |        8
(1 row)

explain analyze

analyze 代表语句真实执行了。所以会有类似 (actual time=0.263..0.265 rows=7 loops=1)的输出

mondb=# explain analyze
SELECT tu.user_id,
       tu.user_first_name::text || tu.user_last_name::text AS user_name,
       tr.role_id,
       tr.role_name
  FROM t_base_user tu,
       t_base_role tr,
       t_base_user_role tur
 WHERE 1 = 1 
   AND tu.user_id = tur.user_id 
   AND tr.role_id = tur.role_id 
   AND tu.user_status = '1' 
   AND tr.role_status = '1'
 ORDER BY tr.role_id, 
          tu.user_id;

                                                            QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=3.94..3.97 rows=9 width=57) (actual time=0.263..0.265 rows=7 loops=1)
   Sort Key: tr.role_id, tu.user_id
   Sort Method: quicksort  Memory: 25kB
   ->  Hash Join  (cost=2.41..3.80 rows=9 width=57) (actual time=0.194..0.214 rows=7 loops=1)
         Hash Cond: ((tur.role_id)::text = (tr.role_id)::text)
         ->  Hash Join  (cost=1.24..2.48 rows=9 width=19) (actual time=0.063..0.076 rows=7 loops=1)
               Hash Cond: ((tur.user_id)::text = (tu.user_id)::text)
               ->  Seq Scan on t_base_user_role tur  (cost=0.00..1.11 rows=11 width=9) (actual time=0.010..0.011 rows=11 loops=1)
               ->  Hash  (cost=1.12..1.12 rows=9 width=16) (actual time=0.029..0.029 rows=7 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 9kB
                     ->  Seq Scan on t_base_user tu  (cost=0.00..1.12 rows=9 width=16) (actual time=0.013..0.020 rows=7 loops=1)
                           Filter: ((user_status)::text = '1'::text)
         ->  Hash  (cost=1.09..1.09 rows=7 width=19) (actual time=0.063..0.063 rows=7 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 9kB
               ->  Seq Scan on t_base_role tr  (cost=0.00..1.09 rows=7 width=19) (actual time=0.034..0.041 rows=7 loops=1)
                     Filter: ((role_status)::text = '1'::text)
 Planning time: 0.607 ms
 Execution time: 0.351 ms
(18 rows)

explain (analyze true)

mondb=# explain (analyze true)
SELECT tu.user_id,
       tu.user_first_name::text || tu.user_last_name::text AS user_name,
       tr.role_id,
       tr.role_name
  FROM t_base_user tu,
       t_base_role tr,
       t_base_user_role tur
 WHERE 1 = 1 
   AND tu.user_id = tur.user_id 
   AND tr.role_id = tur.role_id 
   AND tu.user_status = '1' 
   AND tr.role_status = '1'
 ORDER BY tr.role_id, 
          tu.user_id;

                                                            QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=3.94..3.97 rows=9 width=57) (actual time=0.120..0.120 rows=7 loops=1)
   Sort Key: tr.role_id, tu.user_id
   Sort Method: quicksort  Memory: 25kB
   ->  Hash Join  (cost=2.41..3.80 rows=9 width=57) (actual time=0.077..0.091 rows=7 loops=1)
         Hash Cond: ((tur.role_id)::text = (tr.role_id)::text)
         ->  Hash Join  (cost=1.24..2.48 rows=9 width=19) (actual time=0.031..0.040 rows=7 loops=1)
               Hash Cond: ((tur.user_id)::text = (tu.user_id)::text)
               ->  Seq Scan on t_base_user_role tur  (cost=0.00..1.11 rows=11 width=9) (actual time=0.004..0.005 rows=11 loops=1)
               ->  Hash  (cost=1.12..1.12 rows=9 width=16) (actual time=0.016..0.016 rows=7 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 9kB
                     ->  Seq Scan on t_base_user tu  (cost=0.00..1.12 rows=9 width=16) (actual time=0.007..0.011 rows=7 loops=1)
                           Filter: ((user_status)::text = '1'::text)
         ->  Hash  (cost=1.09..1.09 rows=7 width=19) (actual time=0.030..0.030 rows=7 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 9kB
               ->  Seq Scan on t_base_role tr  (cost=0.00..1.09 rows=7 width=19) (actual time=0.015..0.021 rows=7 loops=1)
                     Filter: ((role_status)::text = '1'::text)
 Planning time: 0.443 ms
 Execution time: 0.187 ms
(18 rows)

从输出上来看 explain analyze 等价于 explain (analyze true)，那为什么会有两种表现形式？很容易让人困惑。

对于 EXPLAIN [ ANALYZE ] [ VERBOSE ] statement ，只有 ANALYZE 和 VERBOSE 选项能被指定，并且必须按照上述的顺序，不要把选项列表放在圆括号内。在PostgreSQL 9.0 之前，只支持没有圆括号的语法。
我们期望所有新的选项将只在圆括号语法中支持。

个人感觉没有放到圆括号的 analyze 是为了兼容之前的老版本。

官方文档要好好读！！！
官方文档要好好读！！！
官方文档要好好读！！！

参考
http://www.postgres.cn/docs/9.6/sql-explain.html
http://www.postgres.cn/docs/9.6/using-explain.html