PG中，使用EXPLAIN命令来显示SQL命令执行计划。查看规划器为任何查询生成的查询计划。
EXPLAIN命令语法
PG中，EXPLAIN命令语法如下：
postgres=# \h explain
Command: EXPLAIN
Description: show the execution plan of a statement
Syntax:
EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement
where option can be one of:
ANALYZE [ boolean ]
VERBOSE [ boolean ]
COSTS [ boolean ]
SETTINGS [ boolean ]
BUFFERS [ boolean ]
TIMING [ boolean ]
SUMMARY [ boolean ]
FORMAT { TEXT | XML | JSON | YAML }
URL: https://www.postgresql.org/docs/12/sql-explain.html
其中，ANALYZE选项通过实际执行SQL来获得SQL命令的实际执行计划。ANALYZE选项查看到的执行计划因为真正被执行过，所以可以看到执行计划每一步耗费了多场时间，以及它实际返回的行数。另外，为了不影响实际数据，可以吧EXPLAIN ANALYZE放到一个事务中，执行完即回滚事务，例如：
BEGIN；
EXLAIN ANALYZE ...;
ROLLBACK;
postgres=# begin;
BEGIN
postgres=# explain analyze insert into t1 values(1,now());
QUERY PLAN
-------------------------------------------------------------------------------------------
Insert on t1 (cost=0.00..0.02 rows=1 width=36) (actual time=0.208..0.209 rows=0 loops=1)
-> Result (cost=0.00..0.02 rows=1 width=36) (actual time=0.008..0.011 rows=1 loops=1)
Planning Time: 0.037 ms
Execution Time: 0.279 ms
(4 rows)
postgres=# select * from t1;
a | b
---+-------------------------------
1 | 2022-02-27 17:19:52.885978+08
(1 row)
postgres=# rollback;
ROLLBACK
VERBOSE选项显示计划的附加信息，如计划树中每个节点输出的各个列，如果触发器被触发，还会输出触发器的名称。
COSTS选项显示每个计划节点的启动成本和总成本，以及估计行数和每行宽度。该选项默认为“TRUE”。
BUFFERS选项显示缓冲区使用的信息。该参数只能与ANALYZE参数一起使用。显示的缓冲区信息包括共享块读和写的块数、本地块读和写的块数，以及临时块读和写的块数。共享块、本地块和临时块分别包含表和索引、临时表和临时索引，以及在排序和物化计划中使用的磁盘块。上层节点显示出来的块数包含所有其子节点使用的块数。
FORMAT选项指定输出格式，输出格式可以使TEXT、XML、JSON或者YAML。非文本输出包含与文本输出格式相同的信息，默认为“TEXT”。
TIMING：在输出中包含实际启动时间和每个节点花费的时间，重复读系统块在某些系统上会显著的减缓查询的速度，只在ANALYZE也启用的时候使用。
SETTINGS：包括有关配置参数的信息。具体来说，包括影响查询计划的选项，其值不同于内置默认值。此参数默认为FALSE。
SUMMARY:在查询计划之后包含摘要信息（例如，总计的时间信息）。当使用ANALYZE时默认包含摘要信息，但默认情况下不包含摘要信息，但可以使用此选项启用摘要信息。使用EXPLAIN EXECUTE中的计划时间包括从缓存中获取计划所需的时间 以及重新计划所需的时间（如有必要）。
EXPLAIN输出结果解释
postgres=# explain select * from t1;
QUERY PLAN
-------------------------------------------------------
Seq Scan on t1 (cost=0.00..23.10 rows=1310 width=34)
(1 row)
- "Seq Scan on t1"：表示顺序扫描表t1，也就是全表扫描，从头到尾扫描。
- "cost=0.00..23.10"："cost="后面跟了两个数字，由".."分隔，其中"0.00"表示启动的成本，即返回第一行需要的cost值；第二个数字"23.10"表示所有数据的成本。
- "rows=1310"：表示会返回1310行
- "width=34"：表示每行平均宽度为34字节。
成本cost值确定方法：
- 顺序扫描一个数据块，cost值定为"1"
- 随机扫描一个数据块，cost值定为"4"
- 处理一个数据行的CPU代价，cost值定为"0.01"
- 处理一个索引行的CPU代价，cost值定为"0.005"
- 每个操作符的CPU代价为"0.0025"
EXPLAIN使用示例
1、改变输出格式
默认情况执行计划输出为文本格式，可通过format将输出格式进行改变
--输出为JSON格式
postgres=# explain (format json) select * from t1;
QUERY PLAN
--------------------------------
[ +
{ +
"Plan": { +
"Node Type": "Seq Scan",+
"Parallel Aware": false,+
"Relation Name": "t1", +
"Alias": "t1", +
"Startup Cost": 0.00, +
"Total Cost": 23.10, +
"Plan Rows": 1310, +
"Plan Width": 34 +
} +
} +
]
(1 row)
--输出为xml格式
postgres=# explain (format xml) select * from t1;
QUERY PLAN
----------------------------------------------------------
<explain xmlns="http://www.postgresql.org/2009/explain">+
<Query> +
<Plan> +
<Node-Type>Seq Scan</Node-Type> +
<Parallel-Aware>false</Parallel-Aware> +
<Relation-Name>t1</Relation-Name> +
<Alias>t1</Alias> +
<Startup-Cost>0.00</Startup-Cost> +
<Total-Cost>23.10</Total-Cost> +
<Plan-Rows>1310</Plan-Rows> +
<Plan-Width>34</Plan-Width> +
</Plan> +
</Query> +
</explain>
(1 row)
2、使用analyze参数
使用analyze参数，可以实际执行sql语句获取更精确的执行计划
postgres=# explain analyze select * from t2;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Seq Scan on t2 (cost=0.00..1443.00 rows=100000 width=4) (actual time=0.008..49.992 rows=100000 loops=1)
Planning Time: 0.112 ms
Execution Time: 95.298 ms
(3 rows)
加了analyze参数后，输出结果多了"(actual time=0.008..49.992 rows=100000 loops=1)"，表示实际的启动时间、执行时间、扫描行数。
使用analyze和buffers选项，可以实际执行并查看实际代价和缓冲区命中的情况
postgres=# explain (analyze true,buffers true) select * from t2;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Seq Scan on t2 (cost=0.00..1443.00 rows=100000 width=4) (actual time=0.019..52.520 rows=100000 loops=1)
Buffers: shared hit=443
Planning Time: 0.271 ms
Execution Time: 98.838 ms
(4 rows)
"shared hit"表示从共享内存中读到了443个块。
扫描方式
1、全表扫描
即顺序扫描(Seq Scan)，把表中的所有数据块从头到尾读一遍，从中找到符合条件的数据块。上述的示例均为全表扫描
2、索引扫描
在索引中找出需要的数据行的物理位置，然后到表的数据块中把相应的数据读出来的过程。在EXPLAIN命令的结果中用"Index Scan"表示。
postgres=# explain analyze select * from t1 where a=1000;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Index Scan using t1_pkey on t1 (cost=0.29..8.31 rows=1 width=36) (actual time=0.012..0.014 rows=1 loops=1)
Index Cond: (a = 1000)
Planning Time: 0.090 ms
Execution Time: 0.052 ms
(4 rows)
3、位图扫描
位图扫描也是走索引的一种方式，扫描索引，将满足条件的行或块在内存中建一个位图（相当于一个bit存一个，4字节存一个int数据，如果用位图来标记就可以存32个），扫描完索引后，再根据位图到表的数据文件中把响应的数据读出来。如果走了两个索引，可以把两个索引形成的位图通过AND或OR计算合并成一个，再到表的数据文件中把数据读出来。
当执行计划的结果行数很多时会走这种扫描，如非等值查询、IN子句或有很多条件都可以走不同的索引时。在EXPALIN命令的结果中用"Bitmap Heap Scan"表示。
参考《PostgreSQL修炼之道 从小工到专家 第2版》
