PostgreSQL执行计划

ゞ浴缸里的玫瑰 2021-10-23 09:33 472阅读 0赞

转载自：[https://blog.csdn.net/JAVA528416037/article/details/91998019][https_blog.csdn.net_JAVA528416037_article_details_91998019]

**简介**

PostgreSQL是“世界上最先进的开源关系型数据库”。因为出现较晚，所以客户人群基数较MySQL少，但是发展势头很猛，最大优势是完全开源。

MySQL是“世界上最流行的开源关系型数据库”。当前客户基数大，随着被Oracle收购，开源程度减小，尤其是近期单独拉了免费的MariaDB分支，更表明MySQL有闭源的倾向；

至于两者孰优孰劣，不是本文要讨论的重点，在一般的使用中，没什么大的差别，下面我们只讨论PG中执行计划。

**执行计划**

pg在查询规划路径过程中，查询请求的不同执行方案是通过建立不同的路径来表达的，在生成较多符合条件的路径之后，要从中选择出代价最小的路径，把它转化为一个执行计划，传递给执行器执行。那么如何生成最小代价的计划呢？基于统计信息估计计划中各个节点的成本，其中与之相关的参数如下所示：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0pBVkE1Mjg0MTYwMzc_size_16_color_FFFFFF_t_70][]

计算代价：

# 估算代价：
    total_cost = seq_page_cost * relpages + cpu_tuple_cost *  reltuples
    
    
    # 有时我们不想用系统默认的执行计划，这时可以通过禁止/开启某种运算的语法来强制控制执行计划：
    enable_bitmapscan = on
    enable_hashagg = on
    enable_hashjoin = on
    enable_indexscan = on #索引扫描
    enable_indexonlyscan = on #只读索引扫描
    enable_material = on #物化视图
    enable_mergejoin = on
    enable_nestloop = on
    enable_seqscan = on
    enable_sort = on
    enable_tidscan = on
    
    # 按照上面扫描方式并过滤代价：
    Cost = seq_page_cost * relpages + cpu_tuple_cost *  reltuples + cpu_operation_cost * reltuples

每个SQL语句都会有自己的执行计划，我们可以使用explain指令获取执行计划，语法如下：

nsc=# \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 ]  -- 是否真正执行，默认false
        VERBOSE [ boolean ]  -- 是否显示详细信息，默认false
        COSTS [ boolean ]    -- 是否显示代价信息，默认true
        BUFFERS [ boolean ]  -- 是否显示缓存信息，默认false，前置事件是analyze
        TIMING [ boolean ]   -- 是否显示时间信息
        FORMAT { TEXT | XML | JSON | YAML }  -- 输格式，默认为text

如下图所示，cost是比较重要的指标，cost=1000.00..1205.30，执行sql代价，分为两个部分，前一部分表示启动时间(startup)是1000ms，执行到返回第一行时需要的cost值，后一部分表示总时间(total)是1205.30ms，执行整个SQL的cost。rows表示预测的行数，与实际的记录数可能有出入，数据库经常vacuum或analyze，该值越接近实际值。width表示查询结果的所有字段的总宽度为285个字节。

![2019061419114039.png][]

可以在explain后添加analyze关键字来通过执行这个SQL获得真实的执行计划和执行时间，actual time中的第一个数字表示返回第一行需要的时间，第二个数字表示执行整个sql花费的时间。loops为该节点循环次数，当loops大于1时，总成本为：actual time \* loops

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0pBVkE1Mjg0MTYwMzc_size_16_color_FFFFFF_t_70 1][]

**执行计划节点类型**

在PostgreSQL的执行计划中，是自上而下阅读的，通常执行计划会有相关的索引来表示不同的计划节点，其中计划节点类型分为四类：控制节点（Control Node），扫描节点（Scan Node），物化节点（Materialization Node），连接节点（Join Node）。

**控制节点**：append，组织多个字表或子查询的执行节点，主要用于union操作。

**扫描节点**：用于扫描表等对象以获取元组

Seq Scan（全表扫描）：把表的所有数据块从头到尾读一遍，筛选出符合条件的数据块；

Index Scan（索引扫描）：为了加快查询速度，在索引中找到需要的数据行的物理位置，再到表数据块中把对应数据读出来，如B树，GiST，GIN，BRIN，HASH

Bitmap Index/Heap Scan（位图索引/结果扫描）：把满足条件的行或块在内存中建一个位图，扫描完索引后，再根据位图列表的数据文件把对应的数据读出来，先通过Bitmap Index Scan在索引中找到符合条件的行，在内存中建立位图，之后再到表中扫描Bitmap Heap Scan。

**物化节点**：能够缓存执行结果到缓存中，即第一次被执行时生成的结果元组缓存，等待上层节点使用，例如，sort节点能够获取下层节点返回的所有元组并根据指定的属性排序，并将排序结果缓存，每次上层节点取元组时就从缓存中按需读取。

Materialize：对下层节点返回的元组进行缓存（如连接表时）

Sort：对下层返回的节点进行排序（如果内存超过iwork\_mem参数指定大小，则节点工作空间切换到临时文件，性能急剧下降）

Group：对下层排序元组进行分组操作

Agg：执行聚集函数（sum/max/min/avg）

条件过滤，一般在where后加上过滤条件，当扫描数据行时，会找出满足过滤条件的行，条件过滤在执行计划里面显示Filter，如果条件的列上面有索引，可能会走索引，不会走过滤。

**连接节点**：对应于关系代数中的连接操作，可以实现多种连接方式（条件连接/左连接/右连接/全连接/自然连接）

Nestedloop Join（嵌套连接）： 内表被外表驱动，外表返回的每一行都要在内表中检索找到与它匹配的行，因此整个查询返回的结果集不能太大，要把返回子集较小的表作为外表，且内表的连接字段上要有索引。 执行过程为，确定一个驱动表（outer table），另一个表为inner table，驱动表中每一行与inner table中的相应记录关联；

Hash Join（哈希连接）：优化器使用两个比较的表，并利用连接属性在内存中建立散列表，然后扫描较大的表并探测散列表，找出与散列表匹配的行；

Merge Join（合并连接）：通常hash连接的性能要比merge连接好，但如果源数据上有索引，或结果已经被排过序，这时merge连接性能会优于hash连接；

**运算类型(explain)**

<table> 
 <tbody> 
  <tr> 
   <td>运算类型</td> 
   <td>操作说明</td> 
   <td>是否有启动时间</td> 
  </tr> 
  <tr> 
   <td>Seq Scan</td> 
   <td>顺序扫描表</td> 
   <td>无启动时间</td> 
  </tr> 
  <tr> 
   <td>Index Scan</td> 
   <td>索引扫描</td> 
   <td>无启动时间</td> 
  </tr> 
  <tr> 
   <td>Bitmap Index Scan</td> 
   <td>索引扫描</td> 
   <td>有启动时间</td> 
  </tr> 
  <tr> 
   <td>Bitmap Heap Scan</td> 
   <td>索引扫描</td> 
   <td>有启动时间</td> 
  </tr> 
  <tr> 
   <td>Subquery Scan</td> 
   <td>子查询</td> 
   <td>无启动时间</td> 
  </tr> 
  <tr> 
   <td>Tid Scan</td> 
   <td>行号检索</td> 
   <td>无启动时间</td> 
  </tr> 
  <tr> 
   <td>Function Scan</td> 
   <td>函数扫描</td> 
   <td>无启动时间</td> 
  </tr> 
  <tr> 
   <td>Nested Loop Join</td> 
   <td>嵌套连接</td> 
   <td>无启动时间</td> 
  </tr> 
  <tr> 
   <td>Merge Join</td> 
   <td>合并连接</td> 
   <td>有启动时间</td> 
  </tr> 
  <tr> 
   <td>Hash Join</td> 
   <td>哈希连接</td> 
   <td>有启动时间</td> 
  </tr> 
  <tr> 
   <td>Sort</td> 
   <td>排序(order by)</td> 
   <td>有启动时间</td> 
  </tr> 
  <tr> 
   <td>Hash</td> 
   <td>哈希运算</td> 
   <td>有启动时间</td> 
  </tr> 
  <tr> 
   <td>Result</td> 
   <td>函数扫描，和具体的表无关</td> 
   <td>无启动时间</td> 
  </tr> 
  <tr> 
   <td>Unique</td> 
   <td>distinct/union</td> 
   <td>有启动时间</td> 
  </tr> 
  <tr> 
   <td>Limit</td> 
   <td>limit/offset</td> 
   <td>有启动时间</td> 
  </tr> 
  <tr> 
   <td>Aggregate</td> 
   <td>count, sum,avg等聚集函数</td> 
   <td>有启动时间</td> 
  </tr> 
  <tr> 
   <td>Group</td> 
   <td>group by</td> 
   <td>有启动时间</td> 
  </tr> 
  <tr> 
   <td>Append</td> 
   <td>union操作</td> 
   <td>无启动时间</td> 
  </tr> 
  <tr> 
   <td>Materialize</td> 
   <td>子查询</td> 
   <td>有启动时间</td> 
  </tr> 
  <tr> 
   <td>SetOp</td> 
   <td>intersect/except</td> 
   <td>有启动时间</td> 
  </tr> 
 </tbody> 
</table>

**示例讲解**

慢sql如下：

SELECT
    	te.event_type,
    	sum(tett.feat_bytes) AS traffic
    FROM t_event te
    LEFT JOIN t_event_traffic_total tett
    ON tett.event_id = te.event_id
    WHERE
    	((te.event_type >= 1 AND te.event_type <= 17) OR (te.event_type >= 23 AND te.event_type <= 26) OR (te.event_type >= 129 AND te.event_type <= 256))
    AND te.end_time >= '2017-10-01 09:39:41+08:00'
    AND te.begin_time <= '2018-01-01 09:39:41+08:00'
    AND tett.stat_time >= '2017-10-01 09:39:41+08:00'
    AND tett.stat_time < '2018-01-01 09:39:41+08:00'
    GROUP BY te.event_type
    ORDER BY total_count DESC
    LIMIT 10

耗时：约4s

作用：事件表和事件流量表关联，查出一段时间内按照总流量大小排列的TOP10事件类型

记录数：

select count(1) from t_event;  -- 535881条
    select count(1) from t_event_traffic_total; -- 2123235条

结果：

event_type    traffic
    17	2.26441505638877E17
    2	2.25307250128674E17
    7	1.20629298837E15
    26	285103860959500
    1	169208970599500
    13	47640495350000
    6	15576058500000
    3	12671721671000
    15	1351423772000
    11	699609230000

执行计划：

Limit  (cost=5723930.01..5723930.04 rows=10 width=12) (actual time=3762.383..3762.384 rows=10 loops=1)
      Output: te.event_type, (sum(tett.feat_bytes))
      Buffers: shared hit=1899 read=16463, temp read=21553 written=21553
      ->  Sort  (cost=5723930.01..5723930.51 rows=200 width=12) (actual time=3762.382..3762.382 rows=10 loops=1)
            Output: te.event_type, (sum(tett.feat_bytes))
            Sort Key: (sum(tett.feat_bytes))
            Sort Method: quicksort  Memory: 25kB
            Buffers: shared hit=1899 read=16463, temp read=21553 written=21553
            ->  HashAggregate  (cost=5723923.69..5723925.69 rows=200 width=12) (actual time=3762.360..3762.363 rows=18 loops=1)
                  Output: te.event_type, sum(tett.feat_bytes)
                  Buffers: shared hit=1899 read=16463, temp read=21553 written=21553
                  ->  Merge Join  (cost=384982.63..4390546.88 rows=266675361 width=12) (actual time=2310.395..3119.886 rows=2031023 loops=1)
                        Output: te.event_type, tett.feat_bytes
                        Merge Cond: (te.event_id = tett.event_id)
                        Buffers: shared hit=1899 read=16463, temp read=21553 written=21553
                        ->  Sort  (cost=3284.60..3347.40 rows=25119 width=12) (actual time=21.509..27.978 rows=26225 loops=1)
                              Output: te.event_type, te.event_id
                              Sort Key: te.event_id
                              Sort Method: external merge  Disk: 664kB
                              Buffers: shared hit=652, temp read=84 written=84
                              ->  Append  (cost=0.00..1448.84 rows=25119 width=12) (actual time=0.027..7.975 rows=26225 loops=1)
                                    Buffers: shared hit=652
                                    ->  Seq Scan on public.t_event te  (cost=0.00..0.00 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=1)
                                          Output: te.event_type, te.event_id
                                          Filter: ((te.end_time >= '2017-10-01 09:39:41+08'::timestamp with time zone) AND (te.begin_time <= '2018-01-01 09:39:41+08'::timestamp with time zone) AND (((te.event_type >= 1) AND (te.event_type <= 17)) OR ((te.event_type >= 23) AND (te.event_type <= 26)) OR ((te.event_type >= 129) AND (te.event_type <= 256))))
                                    ->  扫描子表过程，省略...
                        ->  Materialize  (cost=381698.04..392314.52 rows=2123296 width=16) (actual time=2288.881..2858.256 rows=2123235 loops=1)
                              Output: tett.feat_bytes, tett.event_id
                              Buffers: shared hit=1247 read=16463, temp read=21469 written=21469
                              ->  Sort  (cost=381698.04..387006.28 rows=2123296 width=16) (actual time=2288.877..2720.994 rows=2123235 loops=1)
                                    Output: tett.feat_bytes, tett.event_id
                                    Sort Key: tett.event_id
                                    Sort Method: external merge  Disk: 53952kB
                                    Buffers: shared hit=1247 read=16463, temp read=21469 written=21469
                                    ->  Append  (cost=0.00..49698.20 rows=2123296 width=16) (actual time=0.026..470.610 rows=2123235 loops=1)
                                          Buffers: shared hit=1247 read=16463
                                          ->  Seq Scan on public.t_event_traffic_total tett  (cost=0.00..0.00 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=1)
                                                Output: tett.feat_bytes, tett.event_id
                                                Filter: ((tett.stat_time >= '2017-10-01 09:39:41+08'::timestamp with time zone) AND (tett.stat_time < '2018-01-01 09:39:41+08'::timestamp with time zone))
                                          ->  扫描子表过程，省略...
    Total runtime: 3771.346 ms

执行计划解读：

第40->30行：通过结束时间上创建的索引，顺序扫描t\_event\_traffic\_total表，根据时间跨度三个月过滤出符合条件的数据，共2123235条记录；

第26->21行：根据时间过滤出t\_event表中符合条件的记录，共26225条记录；

第30->27行：根据流量大小排序，执行sort操作；

第12->09行：两个表执行join操作，执行完记录200条；

第08->04行：对最终的200条记录按照大小排序；

第01行：执行limit取10条记录。

整个执行计划中花时间最长的是根据时间条件过滤t\_event\_traffic\_total表，因为字表较多，记录较多，导致花费2.8s之多，所以我们优化的思路就比较简单了，直接根据actual time，花费较多的子表去查看表中是否有索引，以及记录是不是很多，有没有优化的空间，而经过排查，发现一个子表中的数据量达到1531147条。

欢迎关注技术公众号：架构师成长营

![20190820174744269.jpeg][]

[https_blog.csdn.net_JAVA528416037_article_details_91998019]: https://blog.csdn.net/JAVA528416037/article/details/91998019
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0pBVkE1Mjg0MTYwMzc_size_16_color_FFFFFF_t_70]: /images/20211023/faa08da4270b48f590f2b761d47ed918.png
[2019061419114039.png]: /images/20211023/fc868e6dfd934672a84317c0adf5301d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0pBVkE1Mjg0MTYwMzc_size_16_color_FFFFFF_t_70 1]: /images/20211023/0aa1e0786e99445f97940c964f9e03a5.png
[20190820174744269.jpeg]: https://img-blog.csdnimg.cn/20190820174744269.jpeg