欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页  >  IT编程

PostgreSQL--读懂执行计划(一)

程序员文章站 2022-06-16 13:36:00
这里写自定义目录标题前言执行计划常用命令参数解读常用组合执行计划解读关键字常见扫描方式Seq ScanIndex Only ScanIndex ScanBitmap Index Scan+Bitmap Heap ScanHash JoinNested LoopMerge Join小结前言PostgreSQL为每个收到查询产生一个查询计划。 选择正确的计划来匹配查询结构和数据的属性对于好的性能来说绝对是最关键的，因此系统包含了一个复杂的规划器来尝试选择好的计划。 你可以使用EXPLAIN命令察看规划器为任...

这里写自定义目录标题

前言

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 hit=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，缓冲命中数
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文件的BIT位判断是否需要回表扫描。

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

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

小结

本文地址：https://blog.csdn.net/qq_37531186/article/details/108995575

相关标签： PostgreSQL 数据库

上一篇： App中如何获取gradle的配置信息

下一篇： Flutter底部导航栏的实现方式

推荐阅读