在分析数据库单条慢SQL时，执行计划是最基础、成本最低的手段，本文对PG数据库执行计划解读做简单说明。执行计划中通常包含计划节点信息以及对应的代价信息。

如何查看执行计划

• 执行计划的结构是一颗计划节点的树，通常有0-2个输入，特殊节点会有多个输入，大部分情况呈现出二叉树结构。
  -> 每一个计划节点对应于树中的一个节点，下层节点的输出作为上层节点输入。数据（元组）从底层节点向上层节点流动，直至根节点。

执行计划查看工具

• explain/对象管理工具：可查看指定SQL语句在当前session下的执行计划
• auto_explain：记录所有后台SQL语句的执行计划：psql、JDBC等。相关配置参数：auto_explain.log_min_duration=1000，log_analyze=on，log_buffers=on
• 可视化工具：
  depesz： depesz
  大力宝：dalibao

---

一、基本语法

explain [option] statement

option为可选参数，常见选项如下

选项	说明
analyze	执行SQL并且显示实际的运行时间和其他统计信息，会实际执行SQL语句
verbose	显示附加信息
costs	包括每个计划节点的启动成本预估和总成本的消耗，也包括行数和行宽度的预估，默认TRUE
timing	计划节点实际消耗，包括行数，循环次数，默认TRUE
buffers	包括共享块命中、读、脏和写的次数，本地块命中、读、脏和写，临时块读和写的次数。
format	格式：TEXT、XML、JSON 或 YAML，默认 TEXT

• 仅explain
  展示计划节点信息（Seq Scan）和预估代价信息（cost）。将cost关闭仅展示计划节点信息，在自动化回归用例时关闭，仅关注计划信息是否变更。

test=# create table tab1(a int);
CREATE TABLE
test=# insert into tab1 values(generate_series(1,1000));
INSERT 0 1000
test=# explain select * from t1;
                          QUERY PLAN
---------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..287791.79 rows=14095679 width=49)
(1 row)
test=# explain (costs off) select * from t1;
   QUERY PLAN
----------------
 Seq Scan on t1
(1 row)

• explain + analyze
  除计划节点信息和执行代价cost信息外，SQL语句会实际执行（Execution Time）

test=# explain analyze select * from t1;
                                                   QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Seq Scan on t1  (cost=0.00..287791.79 rows=14095679 width=49) (actual time=0.012..8043.851 rows=14095230 loops=1)
Planning Time: 0.059 ms
Execution Time: 8659.065 ms
(3 rows)

• explain + analyze + buffers
  buffers附件语句在共享块和本地块明早信息，在分析IO场景较多使用

test=# explain (analyze on, buffers on) select * from t1;
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..287791.79 rows=14095679 width=49) (actual time=0.013..1494.984 rows=14095230 loops=1)
   Buffers: shared hit=96 read=146739
 Planning Time: 0.060 ms
 Execution Time: 2074.946 ms
(4 rows)

• explain + analyze + buffers + verbose
  verbose会额外打印详细信息

test=# explain (analyze on, buffers on,verbose on) select * from t1;
                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 Seq Scan on public.t1  (cost=0.00..287791.79 rows=14095679 width=49) (actual time=0.012..1542.221 rows=14095230 loops=1)
   Output: id, no, info, crt_time
   Buffers: shared hit=128 read=146707
 Planning Time: 0.063 ms
 Execution Time: 2140.104 ms
(5 rows)

二、执行计划解读

1.解读原则

• 从上往下，由里到外
• 每一步cost包括上一步cost

2.内容解读

test=# select relname,relnamespace from pg_class join pg_namespace on (pg_class.relnamespace=pg_namespace.oid) where relname='tab1';
 relname | relnamespace
---------+--------------
 tab1    |         2200
(1 row)
test=# explain (analyze on,buffers on) select relname,relnamespace from pg_class join pg_namespace on (pg_class.relnamespace=pg_namespace.oid) where relname='tab1';
                                                                      QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=4.31..5.52 rows=1 width=68) (actual time=0.144..0.148 rows=1 loops=1)
   Hash Cond: (pg_namespace.oid = pg_class.relnamespace)
   Buffers: shared hit=5 dirtied=1
   ->  Seq Scan on pg_namespace  (cost=0.00..1.16 rows=16 width=4) (actual time=0.007..0.008 rows=15 loops=1)
         Buffers: shared hit=1
   ->  Hash  (cost=4.29..4.29 rows=1 width=68) (actual time=0.047..0.048 rows=1 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         Buffers: shared hit=4 dirtied=1
         ->  Index Only Scan using pg_class_relname_nsp_index on pg_class  (cost=0.28..4.29 rows=1 width=68) (actual time=0.017..0.022 rows=1 loops=1)
               Index Cond: (relname = 'tab1'::name)
               Heap Fetches: 2
               Buffers: shared hit=4 dirtied=1
 Planning Time: 0.253 ms
 Execution Time: 0.176 ms
(14 rows)

• cost信息

(cost=4.31…5.52 rows=1 width=68) (actual time=0.144…0.148 rows=1 loops=1)

cost=4.31..5.52 rows=1 width=68 #cost=4.31 计划启动成本，返回第一行cost值；5.52返回所有数据成本；rows=1返回行数；width=68每行平均宽度（字节），代价越低表示查询执行越高效。

actual time=0.144..0.148 rows=1 loops=1 #实际花费时间，loops循环次数

• 执行和计划耗时

 Buffers: shared hit=1
 Planning Time: 0.253 ms
 Execution Time: 0.176 ms

Planning Time：生成执行计划的时间 Execution Time：执行执行计划的时间 Buffers：数据在缓存中命中块数

• 计划节点信息

test=# explain (costs off) select relname,relnamespace from pg_class join pg_namespace on (pg_class.relnamespace=pg_namespace.oid) where relname='tab1';
                                QUERY PLAN
--------------------------------------------------------------------------
 Hash Join
   Hash Cond: (pg_namespace.oid = pg_class.relnamespace)
   ->  Seq Scan on pg_namespace
   ->  Hash
         ->  Index Only Scan using pg_class_relname_nsp_index on pg_class
               Index Cond: (relname = 'tab1'::name)
(6 rows)

第一步：通过pg_class_relname_nsp_index索引扫描pg_class，Index Only Scan不会回表相较于Index Scan（可能回表）减少IO。
第二部：Hash第一步结果集
第三步：对pg_namespace表进行Seq Scan（全表扫描）
第四步：对步骤二和步骤三进行Hash Join

三 常见的执行计划节点

节点类型	说明
Seq Scan	全表扫描，当数据表中没有索引，或者满足条件的数据集较大，索引扫描的成本高于全表扫描，优化器会选择使用全表扫描。
Index Scan	索引扫描，查询列有索引，则直接扫描索引，不再进行全表扫描，耗费时间小于全表扫描
Index Only Scan	全索引扫描，当查询的条件都在索引中，也会走该扫描方式，不会读取表文件
Bitmap Index Scan	位图索引扫描，也是一种走索引的方式，方法是扫描索引，把满足条件的行或者块在内存中建一个位图，扫描完索引后，再跟进位图中记录的指针到表的数据文件读取相应的数据。在or、and、in子句和有多个条件都可以同时走不同的索引时，都可能走Bitmap Index Scan。
Nestloop Join	嵌套循环连接，是在两个表做连接时，从一张表中读取数据(驱动表outer table)，然后访问另一张表（被查找表 inner table,通常有索引）。驱动表中的每一行与inner表中的相应记录JOIN。
Hash Join	散列连接，是优化器做大数据集连接时的常用方式，优化器使用两个表中较小的表（通常是小一点的那个表或数据源）利用连接键（JOIN KEY）在内存中建立散列表，将列数据存储到hash列表中，然后扫描较大的表，同样对JOIN KEY进行HASH后探测散列表，找出与散列表匹配的行。需要注意的是：如果HASH表太大，无法一次构造在内存中，则分成若干个部分，写入磁盘的临时文件，会多一个写的代价，降低效率。
Merge Join	排序合并连接，是先将关联表的关联列各自做排序，然后从各自的排序表中抽取数据，到另一个排序表中做匹配