PostgreSQL--读懂执行计划(一)
这里写自定义目录标题前言执行计划常用命令参数解读常用组合执行计划解读关键字常见扫描方式Seq ScanIndex Only ScanIndex ScanBitmap Index Scan+Bitmap Heap ScanHash JoinNested LoopMerge Join小结前言PostgreSQL为每个收到查询产生一个查询计划。 选择正确的计划来匹配查询结构和数据的属性对于好的性能来说绝
前言
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在后续的文章中继续介绍。
![Logo](https://devpress.csdnimg.cn/79de2bf0b7994defa4242ef90d5513fa.jpg)
开放原子开发者工作坊旨在鼓励更多人参与开源活动,与志同道合的开发者们相互交流开发经验、分享开发心得、获取前沿技术趋势。工作坊有多种形式的开发者活动,如meetup、训练营等,主打技术交流,干货满满,真诚地邀请各位开发者共同参与!
更多推荐
所有评论(0)