进阶数据库系列（九）：PostgreSQL 执行计划
了解 PostgreSQL 执行计划对于程序员来说是一项关键技能，执行计划是我们优化查询，验证我们的优化查询是否确实按照我们期望的方式运行的重要方式。
PostgreSQL 数据库中的查询生命周期
每个查询都会经历不同的阶段，了解每个阶段对数据库的意义很重要。
- 第一阶段是通过Postgres 的客户端连接到数据库。
- 第二阶段是将查询转换为称为解析树的中间格式。
- 第三阶段就是我们所说的重写系统/规则系统。它采用从第二阶段生成的解析树，并以规划器/优化器可以开始在其中工作的方式重新编写它。
- 第四阶段是最重要的阶段。如果没有规划器，执行器将在如何执行查询、使用什么索引、是否扫描较小的表以消除更多不必要的行等问题上一头雾水。
- 第五个也是最后一个阶段是执行器，它实际执行并返回结果。
PostgreSQL 为每个收到查询产生一个查询计划。 选择正确的计划来匹配查询结构和数据的属性对于好的性能来说绝对是最关键的，因此系统包含了一个复杂的规划器来尝试选择好的计划。 你可以使用EXPLAIN命令察看规划器为任何查询生成的查询计划。
执行计划常用命令
EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement
这里 option可以是：
ANALYZE [ boolean ]
VERBOSE [ boolean ]
COSTS [ boolean ]
BUFFERS [ boolean ]
TIMING [ boolean ]
SUMMARY [ boolean ]
FORMAT { TEXT | XML | JSON | YAML }
参数解读
- ANALYZE，执行命令并且显示实际的运行时间和其他统计信息。这个参数默认被设置为FALSE。
- VERBOSE，显示关于计划的额外信息。特别是：计划树中每个结点的输出列列表、模式限定的表和函数名、总是把表达式中的变量标上它们的范围表别名，以及总是打印统计信息被显示的每个触发器的名称。这个参数默认被设置为FALSE。
- COSTS，包括每一个计划结点的估计启动和总代价，以及估计的行数和每行的宽度。这个参数默认被设置为TRUE。
- BUFFERS，包括缓冲区使用的信息。特别是：共享块命中、读取、标记为脏和写入的次数、本地块命中、读取、标记为脏和写入的次数、以及临时块读取和写入的次数。只有当ANALYZE也被启用时，这个参数才能使用。它的默认被设置为FALSE。
- TIMING，在输出中包括实际启动时间以及在每个结点中花掉的时间。只有当ANALYZE也被启用时，这个参数才能使用。它的默认被设置为TRUE。
- SUMMARY，在查询计划之后包含摘要信息（例如，总计的时间信息）。当使用ANALYZE 时默认包含摘要信息，但默认情况下不包含摘要信息，但可以使用此选项启用摘要信息。 使用EXPLAIN EXECUTE中的计划时间包括从缓存中获取计划所需的时间 以及重新计划所需的时间（如有必要）。
- FORMAT，指定输出格式，可以是 TEXT、XML、JSON 或者 YAML。非文本输出包含和文本输出格式相同的信息，但是更容易被程序解析。这个参数默认被设置为TEXT。
- statement，你想查看其执行计划的任何SELECT、INSERT、UPDATE、DELETE、VALUES、EXECUTE、DECLARE、CREATE TABLE AS或者CREATE MATERIALIZED VIEW AS语句。
常用组合
- 一般查询
--在不需要真正执行sql时，需把analyze去掉
explain analyze select … ;
- 查询缓存及详细信息
--在不需要真正执行sql时，需把analyze去掉
explain (analyze,verbose,buffers) select … ;
执行计划解读
关键字
首先我们看下执行计划常见的关键字
db_test=# explain (analyze,verbose,buffers) select * from db_test.t_test;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Seq Scan on db_test.t_test(cost=0.00..22.32 rows=1032 width=56) (actual time=0.060..1.167 rows=1032 loops=1)
Output: c_bh, n_dm, c_ah
Buffers: shared read=12
Planning Time: 0.283 ms
Execution Time: 1.730 ms
关键字解读
cost=0.00..22.32，#0.00代表启动成本，22.32代表返回所有数据的成本。
rows=1032：#表示返回多少行。
width=56，#表示每行平均宽度。
actual time=0.060..1.167，#实际花费的时间。
loops=1，#循环的次数
Output，#输出的字段名
Buffers，#缓冲命中数
shared read，#代表数据来自disk(磁盘)而并非cache(缓存)，当再次执行sql，会发现变成shared hit，说明数据已经在cache中
Planning Time，#生成执行计划的时间
Execution Time，#执行执行计划的时间
常见扫描方式
PostgreSQL中数据扫描方式很多，常见有如下几种
Seq Scan
全表顺序扫描
db_test=# explain (analyze,verbose,buffers) select * from db_test.t_ms_aj;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Seq Scan on db_test.t_ms_aj (cost=0.00..22.32 rows=1032 width=56) (actual time=0.060..1.167 rows=1032 loops=1)
Output: c_bh, n_dm, c_ah
Buffers: shared hit=12
Planning Time: 0.283 ms
Execution Time: 1.730 ms
Index Only Scan
按索引顺序扫描，通过VM减少回表，绝大数情况下不需要回表。
db_test=# explain (analyze,verbose,buffers) select c_bh from db_test.t_ms_aj where c_bh='db22f5a4f828d0f4eaa0b70679a4d637';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using t_ms_aj_pkey on db_test.t_ms_aj (cost=0.28..8.29 rows=1 width=33) (actual time=0.079..0.081 rows=1 loops=1)
Output: c_bh
Index Cond: (t_ms_aj.c_bh = 'db22f5a4f828d0f4eaa0b70679a4d637'::bpchar)
Heap Fetches: 1
Buffers: shared hit=3
Planning Time: 0.139 ms
Execution Time: 0.166 ms
Index Scan
按索引顺序扫描，并回表。
db_test=# explain (analyze,buffers) select * from db_test.t_ms_aj where c_bh='db22f5a4f828d0f4eaa0b70679a4d637';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Index Scan using t_ms_aj_pkey on t_ms_aj (cost=0.28..8.29 rows=1 width=56) (actual time=0.890..0.894 rows=1 loops=1)
Index Cond: (c_bh = 'db22f5a4f828d0f4eaa0b70679a4d637'::bpchar)
Buffers: shared hit=3
Planning Time: 0.376 ms
Execution Time: 1.136 ms
Bitmap Index Scan+Bitmap Heap Scan
按索引取得的BLOCKID排序，然后根据BLOCKID顺序回表扫描，然后再根据条件过滤掉不符合条件的记录。
这种扫描方法，主要解决了离散数据(索引字段的逻辑顺序与记录的实际存储顺序非常离散的情况)，需要大量离散回表扫描的情况。
db_test=# explain (analyze,verbose,buffers) select * from db_test.t_ms_aj_bak where n_dm=12;
QUERY PLAN
------------------------------------------------------------------
Bitmap Heap Scan on db_test.t_ms_aj_bak (cost=100.85..1303.26 rows=5233 width=56) (actual time=1.477..107.896 rows=5204 loops=1)
Output: c_bh, n_dm, c_ah
Recheck Cond: (t_ms_aj_bak.n_dm = 12)
Heap Blocks: exact=1125
Buffers: shared hit=1126 read=15
-> Bitmap Index Scan on i_ms_aj_bak_n_dm (cost=0.00..99.54 rows=5233 width=0) (actual time=1.260..1.260 rows=5204 loops=1)
Index Cond: (t_ms_aj_bak.n_dm = 12)
Buffers: shared hit=1 read=15
Planning Time: 0.114 ms
Execution Time: 109.361 ms
Hash Join
哈希JOIN，较小的数据集来构建HASH表，然后用较大的数据集去做探测。
db_test=# explain (analyze,verbose,buffers) select aj.c_bh from db_test.t_ms_aj aj join db_test.t_ms_dsr dsr on dsr.c_bh_aj=aj.c_bh;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=35.22..5378.59 rows=2307 width=33) (actual time=3.121..1254.234 rows=2074 loops=1)
Output: aj.c_bh
Inner Unique: true
Hash Cond: (dsr.c_bh_aj = aj.c_bh)
Buffers: shared hit=2828
-> Seq Scan on db_test.t_ms_dsr dsr (cost=0.00..4817.86 rows=200186 width=33) (actual time=0.013..598.660 rows=200186 loops=1)
Output: dsr.c_bh, dsr.c_bh_aj, dsr.c_name
Buffers: shared hit=2816
-> Hash (cost=22.32..22.32 rows=1032 width=33) (actual time=3.089..3.089 rows=1032 loops=1)
Output: aj.c_bh
Buckets: 2048 Batches: 1 Memory Usage: 82kB
Buffers: shared hit=12
-> Seq Scan on db_test.t_ms_aj aj (cost=0.00..22.32 rows=1032 width=33) (actual time=0.010..1.860 rows=1032 loops=1)
Output: aj.c_bh
Buffers: shared hit=12
Planning Time: 0.396 ms
Execution Time: 1257.348 ms
Nested Loop
嵌套循环。其中一个表扫描一次，另一个表则循环多次。
db_test=# explain analyze select aj.c_bh from db_test.t_ms_aj aj join db_test.t_ms_dsr dsr on dsr.c_bh_aj=aj.c_bh where aj.n_dm=20;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=8.87..263.50 rows=45 width=33) (actual time=0.058..0.405 rows=37 loops=1)
-> Bitmap Heap Scan on t_ms_aj aj (cost=4.43..17.09 rows=20 width=33) (actual time=0.028..0.067 rows=20 loops=1)
Recheck Cond: (n_dm = 20)
Heap Blocks: exact=10
-> Bitmap Index Scan on i_ms_aj_n_dm (cost=0.00..4.43 rows=20 width=0) (actual time=0.018..0.019 rows=20 loops=1)
Index Cond: (n_dm = 20)
-> Bitmap Heap Scan on t_ms_dsr dsr (cost=4.44..12.30 rows=2 width=33) (actual time=0.014..0.015 rows=2 loops=20)
Recheck Cond: (c_bh_aj = aj.c_bh)
Heap Blocks: exact=20
-> Bitmap Index Scan on i_ms_dsr_c_bh (cost=0.00..4.43 rows=2 width=0) (actual time=0.011..0.011 rows=2 loops=20)
Index Cond: (c_bh_aj = aj.c_bh)
Planning Time: 0.409 ms
Execution Time: 0.555 ms
Merge Join
Merge Join，需要两个JOIN的表的KEY都是先排好顺序的，如果有索引没有排序过程。Merge Join两个表都只扫描一次。
db_test=# explain analyze select aj.c_bh from db_test.t_ms_aj aj join db_test.t_ms_dsr dsr on dsr.c_bh=aj.c_ah;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=14985.80..15689.01 rows=1032 width=33) (actual time=944.856..951.963 rows=0 loops=1)
Workers Planned: 1
Workers Launched: 1
-> Merge Join (cost=13985.80..14585.81 rows=607 width=33) (actual time=851.573..851.573 rows=0 loops=2)
Merge Cond: (dsr.c_bh = (aj.c_ah)::bpchar)
-> Sort (cost=13911.82..14206.21 rows=117756 width=33) (actual time=747.508..792.472 rows=100093 loops=2)
Sort Key: dsr.c_bh
Sort Method: quicksort Memory: 11282kB
Worker 0: Sort Method: quicksort Memory: 10503kB
-> Parallel Seq Scan on t_ms_dsr dsr (cost=0.00..3993.56 rows=117756 width=33) (actual time=0.035..115.401 rows=100093 loops=2)
-> Sort (cost=73.98..76.56 rows=1032 width=52) (actual time=2.963..3.154 rows=338 loops=2)
Sort Key: aj.c_ah USING <
Sort Method: quicksort Memory: 194kB
Worker 0: Sort Method: quicksort Memory: 194kB
-> Seq Scan on t_ms_aj aj (cost=0.00..22.32 rows=1032 width=52) (actual time=0.082..0.545 rows=1032 loops=2)
Planning Time: 0.481 ms
Execution Time: 952.152 ms
实例
我们先看sql，根据现场反馈修改其他c_dbbh后，sql执行较快，唯独这一个c_dbbh需要一分钟才会出结果。
SELECT COUNT(1)
FROM db_test.t_zh_axx aj
LEFT JOIN (SELECT n_ccsl,c_ajbh,c_zblx,c_laay FROM db_test.t_zh_zjxx A
WHERE
c_bh = (SELECT c_bh FROM db_test.t_zh_zjxx b WHERE b.c_ajbh = A.c_ajbh AND b.c_zblx = '0050002' ORDER BY dt_cjsj DESC NULLS LAST LIMIT 1)
) zb ON zb.c_ajbh = aj.c_ajbh
WHERE
c_dbbh = '0191H4325678D8172F58EE383720D0A9'
AND zb.c_zblx = '0050002'
AND aj.c_zy = '1628'
--涉及表的数据量
--db_test.t_zh_axx 1200万+
--db_test.t_zh_zjxx 1900万+
有经验的同学第一眼看到sql是可能就会发现sql编写有些问题，这里暂时先不管。先看执行计划为什么慢，在进行sql优化。
Aggregate (cost=3214.11..3214.12 rows=1 width=8) (actual time=61097.734..61097.734 rows=1 loops=1)
-> Nested Loop (cost=743.92..3214.11 rows=1 width=0) (actual time=8.702..61097.110 rows=1461 loops=1)
-> Bitmap Heap Scan on t_zh_axx aj (cost=743.36..763.39 rows=5 width=17) (actual time=8.585..11.327 rows=1461 loops=1)
Recheck Cond:((c_dbbh = '0191H4325678D8172F58EE383720D0A9'::bpchar) AND ((c_zy)::text = '1801'::text))
Heap Blocks:exact=720
-> BitmapAnd (cost=743.36..743.36 rows=5 width=0)(actual time=8.479..8.479 rows=0 loops=1)
-> Bitmap Index Scan on i_t_zh_axx_c_dbbh_c_cbfy (cost=0.00..70.63 rows=1343 width=0)(actual time=0.766..0.766 rows=0 loops=1)
Index Cond:(c_dbbh = '0191H4325678D8172F58EE383720D0A9'::bpchar)
-> Bitmap Index Scan on i_t_zh_axx_zblx_c_zy (cost=0.00..672.47 rows=36272 width=0)(actual time=7.644..7.644 rows=0 loops=1)
Index Cond:((c_zy)::text = '1628'::text)
-> Index Scan using i_t_zh_zjxx_c_ajbh on t_zh_zjxx a (cost=0.56..190.13 rows=1 width=16) (actual time=20.069..41.822 rows=1 loops=1461)
Index Cond: ((c_ajbh)::text = (aj.c_ajbh)::text)
Filter: (((c_zblx)::text = '0020002'::text) AND (c_bh = (SubPlan 1)))
Rows Removed by Filter: 167
SubPlan 1
-> Limit (cost=44.55..44.56 rows=1 width=41) (actual time=0.247..0.248 rows=1 loops=245603)
-> Sort (cost=44.55..44.56 rows=1 width=41) (actual time=0.247..0.247 rows=1 loops=245603)
Sort Key: b.dt_cjsj DESC NULLS LAST
Sort Method: top-N heapsort Memory: 25kB
-> Index Scan using i_t_zh_zjxx_c_ajbh on t_zh_zjxx b (cost=0.56..54.82 rows=1 width=41) (actual time=0.017..0.071 rows=38 loops=245603)
Index Cond: ((c_ajbh)::text = (a.c_ajbh)::text)
Filter: ((c_zblx)::text = '0020002'::text)
Rows Removed by Filter: 114
Planning Time: 1.267 ms
Execution Time: 61097.876 ms
仔细观察执行计划后发现，占用时间最多的在第2行Nested Loop（actual time=8.702…61097.110），嵌套循环占用了一分钟，然后在16 -> 20行看到（loops=245603），循环了24.5万次。
嵌套循环：其中一个表扫描一次，另一个表则循环多次。这里基本可以确定问题了，找开发确认发现是确实是数据问题，原因是重复上报导致。
改写1
处理掉垃圾数据后，再来看下原sql是否有优化空间，进行sql改写（以下测试数据采用公司测试环境）。
--在测试环境原sql执行需要2s，勉强符合公司标准
db_zxzh_jy=# SELECT count(1) from db_test.t_zh_axx aj left join (select n_ccsl,c_ajbh,c_zblx,c_laay from db_test.t_zh_zjxx a where c_bh = (SELECT c_bh
from db_test.t_zh_zjxx b where b.c_ajbh=a.c_ajbh and b.c_zblx = '0020002' order by dt_cjsj desc NULLS LAST LIMIT 1) ) zb on zb.c_ajbh = aj.c_ajbh where c_dbbh = '8B7D8C93864E0D0C3E3259C49ED65471' and zb.c_zblx = '0020002' and aj.c_zy = '1801';
count
-------
1356
(1 行记录)
Time: 2227.680 ms (00:02.228)
原sql中left join是一个自关联，取相同c_ajbh中dt_cjsj时间最大的一条与t_zh_axx进行关联。
--在第一次修改过程中陷入原sql的固定模式，想把自连接改掉，直接取相同c_ajbh中时间最大的一条
explain analyze
select count(*)
from db_test.t_zh_axx aj
join (select c_ajbh,c_zblx,max(dt_cjsj) from db_test.t_zh_zjxx group by c_ajbh,c_zblx) zbajxx on zbajxx.c_ajbh = aj.c_ajbh
where aj.c_dbbh = '8B7D8C93864E0D0C3E3259C49ED65471'
and aj.c_zy = '1801'
and zbajxx.c_zblx = '0020002'
--改完发现更慢，根据执行计划进行解读
--执行计划选择的是Merge Join，这种join方式是需要两个表关联字段事先排序
--可以看到两个表的Sort Key是c_ajbh，t_zh_zjxx表没用到索引，先进性全表扫描，然后进行排序，然后是Merge Join
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1046565.51..1046565.52 rows=1 width=8) (actual time=24074.583..24074.583 rows=1 loops=1)
-> Merge Join (cost=1012316.07..1046565.50 rows=6 width=0) (actual time=24057.923..24074.497 rows=1356 loops=1)
Merge Cond: ((t_zh_zjxx.c_ajbh)::text = (aj.c_ajbh)::text)
-> GroupAggregate (cost=1007105.01..1028294.00 rows=1044828 width=30) (actual time=23062.977..24014.438 rows=176529 loops=1)
Group Key: t_zh_zjxx.c_ajbh, t_zh_zjxx.c_zblx
-> Sort (cost=1007105.01..1010685.25 rows=1432095 width=22) (actual time=23062.958..23895.947 rows=347291 loops=1)
Sort Key: t_zh_zjxx.c_ajbh
Sort Method: external merge Disk: 45504kB
-> Seq Scan on t_zh_zjxx (cost=0.00..831303.47 rows=1432095 width=22) (actual time=0.031..9310.987 rows=1366950 loops=1)
Filter: ((c_zblx)::text = '0020002'::text)
Rows Removed by Filter: 18389119
-> Sort (cost=5211.06..5211.07 rows=6 width=17) (actual time=4.402..4.524 rows=1461 loops=1)
Sort Key: aj.c_ajbh
Sort Method: quicksort Memory: 117kB
-> Index Scan using t_zh_axx_c_dbbh_idx on t_zh_axx aj (cost=0.56..5210.98 rows=6 width=17) (actual time=0.099..1.896 rows=1461 loops=1)
Index Cond: (c_dbbh = '8B7D8C93864E0D0C3E3259C49ED65471'::bpchar)
Filter: ((c_zy)::text = '1801'::text)
Planning Time: 1.547 ms
Execution Time: 24082.631 ms
改写2
我们改变下思路，再看下原sql想要得到什么结果。SQL中只有c_zblx = '0020002’这一个条件，随机选一条即可，并不必须是dt_cjsj创建时间最大的一条。
--用exists改写
explain analyze
select count(*) from db_test.t_zh_axx aj
where exists (select 1 from db_test.t_zh_zjxx zbajxx where zbajxx.c_ajbh=aj.c_ajbh and c_zblx = '0020002')
and aj.c_dbbh = '8B7D8C93864E0D0C3E3259C49ED65471'
AND aj.c_zy = '1801'
--执行计划都走索引
--值得关注的是join的方式是Nested Loop Semi Join，多了个Semi。Semi Join支持支持hash, merge, nestloop几种JOIN方法
--semi Join的操作在EXISTS中有一个返回TRUE的操作即可，所以在有索引的情况下很大概率下并不需要全表扫描
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=5560.83..5560.84 rows=1 width=8) (actual time=38.158..38.158 rows=1 loops=1)
-> Nested Loop Semi Join， (cost=1.12..5560.82 rows=6 width=0) (actual time=0.072..37.912 rows=1356 loops=1)
-> Index Scan using t_zh_axx_c_dbbh_idx on t_zh_axx aj (cost=0.56..5210.98 rows=6 width=17) (actual time=0.042..1.315 rows=1461 loops=1)
Index Cond: (c_dbbh = '8B7D8C93864E0D0C3E3259C49ED65471'::bpchar)
Filter: ((c_zy)::text = '1801'::text)
-> Index Scan using i_t_zh_zjxx_c_ajbh on t_zh_zjxx zbajxx (cost=0.56..58.50 rows=1 width=16) (actual time=0.024..0.024 rows=1 loops=1461)
Index Cond: ((c_ajbh)::text = (aj.c_ajbh)::text)
Filter: ((c_zblx)::text = '0020002'::text)
Planning Time: 0.852 ms
Execution Time: 38.255 ms
优化后速度大大提升，验证多个编号结果也无问题。
小结
参考文章：https://blog.csdn.net/qq_37531186/article/ details/108995575 https://blog.csdn.net/qq_37531186 /article/details/109033222
本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2023-07-07，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
数据库
云数据库为企业提供了完善的关系型数据库、非关系型数据库、分析型数据库和数据库生态工具。您可以通过产品选择和组合搭建，轻松实现高可靠、高可用性、高性能等数据库需求。云数据库服务也可大幅减少您的运维工作量，更专注于业务发展，让企业一站式享受数据上云及分布式架构的技术红利！
腾讯云开发者
