侧边栏壁纸
博主头像
极客日记 博主等级

行动起来，活在当下

  • 累计撰写 93 篇文章
  • 累计创建 17 个标签
  • 累计收到 1 条评论

目 录CONTENT

文章目录
PostgreSQL

PostgreSQL 学习笔记 - 执行计划说明

Jack.Jia
Jack.Jia
2022-12-14 / 0 评论 / 0 点赞 / 7 阅读 / 0 字

执行计划说明

执行计划命令

PG 中使用执行计划来查看 SQL 的执行效率。 查看执行计划的命令如下：

EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

where option can be one of:

    ANALYZE [ boolean ]
    VERBOSE [ boolean ]
    COSTS [ boolean ]
    SETTINGS [ boolean ]
    BUFFERS [ boolean ]
    WAL [ boolean ]
    TIMING [ boolean ]
    SUMMARY [ boolean ]
    FORMAT { TEXT | XML | JSON | YAML }

https://www.postgresql.org/docs/15/sql-explain.html

这里注意 ANALYZE 选项，加上后会真正执行实际的 SQL，如果 SQL 语句是一个插入、删除、更新或 CREATETABLE AS 语句，这些语句会修改数据库。为了不想影响实际的数据，可以把 EXPLAINANALYZE 放到一个事务中，执行完后回滚事务，如下：

BEGIN;
EXPLAIN ANALYZE …… 
ROLLBACK;

其他选项说明如下：

  1. VERBOSE 选项用于显示计划的附加信息。这些附加信息有：计划树中每个节点输出的各个列，如果触发器被触发，还会输出触发器的名称。该选项值默认为 FALSE。
  2. COSTS 选项显示每个计划节点的启动成本和总成本，以及估计行数和每行宽度。该选项值默认为 TRUE。
  3. BUFFERS 选项显示关于缓冲区使用的信息。该参数只能与 ANALYZE 参数一起使用。显示的缓冲区信息包括共享块、本地块和临时块读和写的块数。共享块、本地块和临时块分别包含表和索引、临时表和临时索引，以及在排序和物化计划中使用的磁盘块。上层节点显示出来的块数包括其所有子节点使用的块数。该选项值默认为 FALSE。
  4. FORMAT 选项指定输出格式，输出格式可以是 TEXT、XML、JSON 或 YAML。非文本输出包含与文本输出格式相同的信息，但其他程序更容易解析。该参数默认为 TEXT。

执行计划相关参数

PG 参数 postgresql.conf 中有关执行计划得参数如下：

#------------------------------------------------------------------------------
# QUERY TUNING
#------------------------------------------------------------------------------

# - Planner Method Configuration -

#enable_async_append = on
#enable_bitmapscan = on
#enable_gathermerge = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_incremental_sort = on
#enable_indexscan = on
#enable_indexonlyscan = on
#enable_material = on
#enable_memoize = on
#enable_mergejoin = on
#enable_nestloop = on
#enable_parallel_append = on
#enable_parallel_hash = on
#enable_partition_pruning = on
#enable_partitionwise_join = off
#enable_partitionwise_aggregate = off
#enable_seqscan = on
#enable_sort = on
#enable_tidscan = on

# - Planner Cost Constants -

#seq_page_cost = 1.0                    # measured on an arbitrary scale
#random_page_cost = 4.0                 # same scale as above
#cpu_tuple_cost = 0.01                  # same scale as above
#cpu_index_tuple_cost = 0.005           # same scale as above
#cpu_operator_cost = 0.0025             # same scale as above
#parallel_setup_cost = 1000.0   # same scale as above
#parallel_tuple_cost = 0.1              # same scale as above
#min_parallel_table_scan_size = 8MB
#min_parallel_index_scan_size = 512kB
#effective_cache_size = 4GB

#jit_above_cost = 100000                # perform JIT compilation if available
                                        # and query more expensive than this;
                                        # -1 disables
#jit_inline_above_cost = 500000         # inline small functions if query is
                                        # more expensive than this; -1 disables
#jit_optimize_above_cost = 500000       # use expensive JIT optimizations if
                                        # query is more expensive than this;
                                        # -1 disables

# - Genetic Query Optimizer -
#geqo = on
#geqo_threshold = 12
#geqo_effort = 5                        # range 1-10
#geqo_pool_size = 0                     # selects default based on effort
#geqo_generations = 0                   # selects default based on effort
#geqo_selection_bias = 2.0              # range 1.5-2.0
#geqo_seed = 0.0                        # range 0.0-1.0

# - Other Planner Options -

#default_statistics_target = 100        # range 1-10000
#constraint_exclusion = partition       # on, off, or partition
#cursor_tuple_fraction = 0.1            # range 0.0-1.0
#from_collapse_limit = 8
#jit = on                               # allow JIT compilation
#join_collapse_limit = 8                # 1 disables collapsing of explicit

这部分参数得解释官网有说明，直接查看：

https://www.postgresql.org/docs/current/runtime-config-query.html

我们这里简单得查看几个参数：

  1. enable_seqscan：是否选择全表顺序扫描。实际上并不能完全禁止全表扫描，但是把这个变量关闭会让优化器在存在其他方法时优先选择其他方法
  2. enable_indexscan：是否选择索引扫描
  3. enable_bitmapscan：是否选择位图扫描
  4. enable_tidscan：是否选择位图扫描
  5. enable_nestloop：多表连接时，是否选择嵌套循环连接。如果设置为”off”，执行计划只有走嵌套循环连接一条路时，优化器也只能选择这条路，但如果有其他连接方法可以走，优化器会优先选择其他方法
  6. enable_hashjoin：多表连接时，是否选择 hash 连接
  7. enable_mergejoin：多表连接时，是否选择 merge 连接
  8. enable_hashagg：是否使用 hash 聚合
  9. enable_sort：是否使用明确的排序，如果设置为“off”，执行计划只有排序一条路时，优化器也只能选择这条路，但如果有其他方法可以走，优化器会优先选择其了方法

COST 基准值参数

PG 参数中也有一些 COST 基准参数，如下：

# - Planner Cost Constants -

#seq_page_cost = 1.0                    # measured on an arbitrary scale
#random_page_cost = 4.0                 # same scale as above
#cpu_tuple_cost = 0.01                  # same scale as above
#cpu_index_tuple_cost = 0.005           # same scale as above
#cpu_operator_cost = 0.0025             # same scale as above
#parallel_setup_cost = 1000.0   # same scale as above
#parallel_tuple_cost = 0.1              # same scale as above

执行计划在选择最优路径时，不同路径的 cost 值只有相对意义，同时缩放它们将不会对不同路径的选择产生任何影响。默认情况下，PG 以顺序扫描一个数据块的开销作为基准单位，也就是说将顺序扫描的基准参数 seq_page_cost 默认设为 1.0，其他开销的基准参数都对照它来设置。

在上面的配置项中，”seq_page_cost”一般作为基准，不用改变。可能需要改变的是”random_page_cost”，如果在读数据时，数据基本都命中在内存中，这时随机读和顺序读的差异不大，可能需要把”random_page_cost”的值调得小一些。如果想让优化器偏向走索引，而不走全表扫描，可以把”random_page_cost”的值调得高一些。

查看执行计划

Explain 使用示例

cndba=# select * from dave;
 id |         url
----+----------------------
  1 | https://www.cndba.cn
  2 | https://www.cndba.cn
  3 | https://www.cndba.cn
(3 rows)


cndba=# explain (format json) select * from dave;
           QUERY PLAN
--------------------------------
 [                             +
   {                           +
     "Plan": {                 +
       "Node Type": "Seq Scan",+
       "Parallel Aware": false,+
       "Async Capable": false, +
       "Relation Name": "dave",+
       "Alias": "dave",        +
       "Startup Cost": 0.00,   +
       "Total Cost": 1.03,     +
       "Plan Rows": 3,         +
       "Plan Width": 102       +
     }                         +
   }                           +
 ]
(1 row)

cndba=# explain (format xml) select * from dave;
                        QUERY PLAN
----------------------------------------------------------
 <explain xmlns="http://www.postgresql.org/2009/explain">+
   <Query>                                               +
     <Plan>                                              +
       <Node-Type>Seq Scan</Node-Type>                   +
       <Parallel-Aware>false</Parallel-Aware>            +
       <Async-Capable>false</Async-Capable>              +
       <Relation-Name>dave</Relation-Name>               +
       <Alias>dave</Alias>                               +
       <Startup-Cost>0.00</Startup-Cost>                 +
       <Total-Cost>1.03</Total-Cost>                     +
       <Plan-Rows>3</Plan-Rows>                          +
       <Plan-Width>102</Plan-Width>                      +
     </Plan>                                             +
   </Query>                                              +
 </explain>
(1 row)

cndba=# explain (format YAML) select * from dave;
        QUERY PLAN
---------------------------
 - Plan:                  +
     Node Type: "Seq Scan"+
     Parallel Aware: false+
     Async Capable: false +
     Relation Name: "dave"+
     Alias: "dave"        +
     Startup Cost: 0.00   +
     Total Cost: 1.03     +
     Plan Rows: 3         +
     Plan Width: 102
(1 row)

cndba=# explain select * from dave;
                      QUERY PLAN
------------------------------------------------------
 Seq Scan on dave  (cost=0.00..1.03 rows=3 width=102)
(1 row)

cndba=# explain analyze select * from dave;
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Seq Scan on dave  (cost=0.00..1.03 rows=3 width=102) (actual time=0.006..0.006 rows=3 loops=1)
 Planning Time: 0.033 ms
 Execution Time: 0.016 ms
(3 rows)

cndba=#

这里加了 analyze 之后会执行执行，执行计划中也会多一部分内容 (actual time=0.006..0.006 rows=3 loops=1)。

查看 buffer 命中率：

cndba=# explain (analyze true,buffers true) select * from film;
                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Seq Scan on film  (cost=0.00..64.00 rows=1000 width=384) (actual time=0.024..0.584 rows=1000 loops=1)
   Buffers: shared hit=32 read=22
 Planning:
   Buffers: shared hit=58 read=3 dirtied=6
 Planning Time: 4.640 ms
 Execution Time: 0.749 ms
(6 rows)

cndba=#

加了 (analyze true,buffers true) 后，这里多了一条记录：

Buffers: shared hit=58 read=3 dirtied=6

表示从内存中读取了 58 个块，从磁盘中读取 3 个，写磁盘 6 个块。 因为共享内存中有脏块，所以从磁盘中读块之前必须把内存中得脏块写到磁盘。

全表扫描

全表扫描在 PostgreSQL 也称为顺序扫描（seq scan），全表扫描就是把表的所有数据块从头到尾读一遍，然后从数据块中找到符合条件的数据块。

全表扫描在 EXPLAIN 命令输出的结果中用”Seq Scan”表示，如下：

cndba=# explain select * from dave;
                      QUERY PLAN
------------------------------------------------------
 Seq Scan on dave  (cost=0.00..1.03 rows=3 width=102)
(1 row)

索引扫描

索引通常是为了加快查询数据的速度而增加的。索引扫描，就是在索引中找出需要的数据行的物理位置，然后再到表的数据块中把相应的数据读出来的过程。

索引扫描在 EXPLAIN 命令输出的结果中用”Index Scan”表示，如下：

cndba=# explain select * from film where film_id<100;
                                QUERY PLAN
--------------------------------------------------------------------------
 Index Scan using film_pkey on film  (cost=0.28..15.79 rows=98 width=384)
   Index Cond: (film_id < 100)
(2 rows)

位图扫描

位图扫描也是走索引的一种方式。方法是扫描索引，把满足条件的行或块在内存中建一个位图，扫描完索引后，再根据位图到表的数据文件中把相应的数据读出来。如果走了两个索引，可以把两个索引形成的位图进行”and”或”or”计算，合并成一个位图，再到表的数据文件中把数据读出来。

PostgreSQL 不支持创建位图索引。但会为每个查询动态创建页面的位图。它不会被缓存或重复使用，并在位图索引扫描结束时被丢弃。
假设要搜索 x = 1 和 y = 2 。在 x 和 y 上有 b 树索引。 PostgreSQL 不会将 x 和 y 合并为位图，然后搜索位图。它将扫描索引 x 以查找所有 x = 1 的页面地址，并在其中包含 x = 1 为真。然后它扫描 y 来查找 y 可能等于 2 ，由此生成一个位图。然后与它们进行 AND 操作，以找到 x = 1 和 y = 2 都可能为真的页面。最后，它扫描表自身，仅读取可能包含候选值的页面，读取每个页面并仅保留其中 x = 1 和 y = 2 的行。

当执行计划的结果行数很多时会进行这种扫描，如非等值查询、IN 子句或有多个条件都可以走不同的索引时。

下面是非等值的一个示例：

cndba=# explain select * from ustc where id2 >10000;
QUERY PLAN
Bitmap Heap Scan on ustc (cost=18708.13..36596.06 rows=998155 width=16)
Recheck Cond:(id2 > 10000)
-> Bitmap Index Scan on idx_ustc_id2 (cost=0.00..18458.59 rows=998155
width=0)
Index Cond:(id2>10000)
(4 rows)

条件过滤

条件过滤，一般就是在 where 条件上加的过滤条件，当扫描数据行时，会找出满足过滤条件的行。条件过滤在执行计划中显示为”Filter”，示例如下：

cndba=# explain select * from cndba.public.film f where film_id<300 and title like 'Ace%';
                                QUERY PLAN
---------------------------------------------------------------------------
 Index Scan using film_pkey on film f  (cost=0.28..35.03 rows=1 width=384)
   Index Cond: (film_id < 300)
   Filter: ((title)::text ~~ 'Ace%'::text)
(3 rows)

如果条件的列上有索引，可能会走索引，不走过滤，如下：osdba=# EXPLAIN cndba=# explain select * from cndba.public.film f where film_id<300;
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Index Scan using film_pkey on film f  (cost=0.28..34.28 rows=298 width=384)
   Index Cond: (film_id < 300)
(2 rows)

Nestloop join

嵌套循环连接（Nestloop Join）是在两个表做连接时最朴素的一种连接方式。在嵌套循环中，内表被外表驱动，外表返回的每一行都要在内表中检索找到与它匹配的行，因此整个查询返回的结果集不能太大（>10000 不适合），要把返回子集较小的表作为外表，而且在内表的连接字段上要有索引，否则会很慢。

执行的过程为：确定一个驱动表（outer table），另一个表为 inner table，驱动表中的每一行与 inner 表中的相应记录 JOIN 类似一个嵌套的循环。适用于驱动表的记录集比较小（<10000）而且 inner 表有有效的访问方法（Index）。需要注意的是，JOIN 的顺序很重要，驱动表的记录集一定要小，返回结果集的响应时间是最快的。

我们这里强势走 NL 了，不然会走 hash join：

cndba=# set enable_hashjoin=off;
SET
cndba=# set enable_mergejoin=off;
SET
cndba=# explain select f.film_id,title,release_year,fa.last_update  from film f,film_actor fa where f.film_id = fa.film_id  and f.film_id  <300;
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.56..339.44 rows=1899 width=31)
   ->  Index Scan using film_pkey on film f  (cost=0.28..34.28 rows=298 width=23)
         Index Cond: (film_id < 300)
   ->  Index Scan using idx_fk_film_id on film_actor fa  (cost=0.28..0.97 rows=5 width=10)
         Index Cond: (film_id = f.film_id)
(5 rows)

cndba=#

驱动表判断规则：

  1. 同级是先上后下
  2. 不同级是先右后左（先里往外读）

我们这里是同级：
先执行的表为驱动表（film = 外表），后执行的为被驱动表（film_actor = 内表）

Hash Join

优化器使用两个表中较小的表，并利用连接键在内存中建立散列表，然后扫描较大的表并探测散列表，找出与散列表匹配的行。
这种方式适用于较小的表可以完全放于内存中的情况，这样总成本就是访问两个表的成本之和。但是如果表很大，不能完全放入内存，优化器会将它分割成若干不同的分区，把不能放入内存的部分写入磁盘的临时段，此时要有较大的临时段以便尽量提高 I/O 的性能。

cndba=# explain select f.film_id,title,release_year,fa.last_update  from film f,film_actor fa where f.film_id = fa.film_id  and f.film_id  <300;
                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Hash Join  (cost=38.01..153.51 rows=1899 width=31)
   Hash Cond: (fa.film_id = f.film_id)
   ->  Seq Scan on film_actor fa  (cost=0.00..98.72 rows=6372 width=10)
   ->  Hash  (cost=34.28..34.28 rows=298 width=23)
         ->  Index Scan using film_pkey on film f  (cost=0.28..34.28 rows=298 width=23)
               Index Cond: (film_id < 300)
(6 rows)

cndba=#

Merge Join

通常情况下散列连接的效果比合并连接好，如果源数据上有索引，或者结果已经被排过序，在执行排序合并连接时就不需要排序了，这时合并连接的性能会优于散列连接。

cndba=# set enable_nestloop=off;
SET
cndba=# explain select f.film_id,title,release_year,fa.last_update  from film f,film_actor fa where f.film_id = fa.film_id  and f.film_id  <300;
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Merge Join  (cost=0.56..343.10 rows=1899 width=31)
   Merge Cond: (f.film_id = fa.film_id)
   ->  Index Scan using film_pkey on film f  (cost=0.28..34.28 rows=298 width=23)
         Index Cond: (film_id < 300)
   ->  Index Scan using idx_fk_film_id on film_actor fa  (cost=0.28..275.81 rows=6372 width=10)
(5 rows)

cndba=#
0
PostgreSQL
版权归属： Jack.Jia
本文链接： https://www.explorexd.com/archives/cbfc2f9d-956e-4169-8f3e-0dfe83c41cd6
许可协议： 本文使用《署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0)》协议授权

评论区