PG数据库中的执行计划

PG数据库中的执行计划

目录

1、PGSQL执行计划的查看方式

PGSQL中查看执行计划主要使用EXPLAIN命令

EXPLAIN [(option [,…])]statement
EXPLAIN[ANALYEZ][VERBOSE]statement
命令的可选项“options”为:

ANALYZE  [boolean]
VERBOSE  [boolean]
COST     [boolean]
BUFFERS  [boolean]
TIMING   [boolean]
FORMAT { TEXT | XML | JOSN | YAML }

ANALYZE：选项通过实际执行的SQL来获得相应的执行计划。因为它真正被执行，所以可以看到执行计划每一步花掉了多少时间，以及它实际返回的行数目。
VERBOSE（默认false）：选项用于显示计划的附加信息。这些附加信息有：计划树中每个节点输出的各个列，如果触发器被触发，还会输出触发器的名称。 
COSTS（默认true）：选项显示每个计划节点的启动成本和总成本，以及估计行数和每行宽度。
TIMING(默认true）：analyze出现时可选。显示每个节点的启动时间和总时间花费。
BUFFERS（默认false）：选项显示关于缓冲区使用的信息。该参数只能与anlyze参数一起使用。显示的缓冲区信息包括共享块、本地块、和临时块读和写的块数。共享块、本地块、和临时块分别包含表和索引、临时表和临时索引，以及在排序和物化计划中使用的磁盘块。上层节点显示出来的块数包括其所有子节点使用的块数。

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

begin;
explain analyze …;
rollback;

下面为执行计划示例：

testdb=> explain  (analyze,buffers,verbose) select * from t1 where id=2;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_t1_id on test.t1  (cost=0.14..8.16 rows=1 width=524) (actual time=0.020..0.021 rows=1 loops=1)
   Output: id, t, name
   Index Cond: (t1.id = 2)
   Buffers: shared hit=2
 Planning Time: 0.031 ms
 Execution Time: 0.031 ms
(6 rows)


结果中“Index Scan using”表示索引扫描表“idx_t1_id”；
后面的内容“(cost=0.14…8.16 rows=1 width=524)”可以分为三部分：
1）“cost=0.14…8.16”：“cost=”后面两个数字，中间是由“…”分割，第一个数字“0.14”表示启动的成本（注意这里不是时间），也就是说返回第一行pgsql需要消耗多少cost值；第二个数字表示返回所有的数据的成本。注意：每个节点中的COST都包含该节点之前节点的COST并且都为预估值。

2）rows=1：表示会返回1行。

3）width=524：表示每行平均宽度为524byte（int=4byte，character varying=2byte）。

actual部分为真实的消耗，也可以分为三个部分：

1)“time=0.020…0.021”：“0.020”为返回第一条数据花费的真实时间，“0.021”为返回所有数据花费的时间。
2)“rows=1” ：真实的返回行数
3)“loops=1”：该步骤循环的次数

buffers：缓冲命中数
output： 输出的字段名
planning time: 生成执行计划时间
execution time：执行执行计划时间

2、PGSQL执行计划的阅读

阅读顺序：
嵌套层次最深的，最先执行,同样嵌套深度的，从上到下，先予执行每一步的cost包括上一步。
以下面的执行计划为例：


testdb=> explain  (analyze,buffers,verbose) select * from t1,t2 where t1.id=t2.id;
                                                 QUERY PLAN                                                  
-------------------------------------------------------------------------------------------------------------
1 Hash Join  (cost=1.07..13.02 rows=3 width=532) (actual time=0.050..0.052 rows=3 loops=1)
    Output: t1.id, t1.t, t1.name, t2.id, t2.salary
    Hash Cond: (t1.id = t2.id)
    Buffers: shared hit=5
2   ->  Seq Scan on test.t1  (cost=0.00..11.40 rows=140 width=524) (actual time=0.007..0.007 rows=3 loops=1)
          Output: t1.id, t1.t, t1.name
          Buffers: shared hit=1
3   ->  Hash  (cost=1.03..1.03 rows=3 width=8) (actual time=0.019..0.020 rows=3 loops=1)
          Output: t2.id, t2.salary
          Buckets: 1024  Batches: 1  Memory Usage: 9kB
         Buffers: shared hit=1
4         ->  Seq Scan on test.t2  (cost=0.00..1.03 rows=3 width=8) (actual time=0.010..0.011 rows=3 loops=1)
               Output: t2.id, t2.salary
               Buffers: shared hit=1
 Planning Time: 0.362 ms
 Execution Time: 0.078 ms
(16 rows)

PG数据库的执行计划没有步骤编号，为了方便讲解这里我为每个步骤从上到下依次加上了编号。
我们现在来解读一下这个执行计划，先从上往下看，可以看到编号2和编号3这两个步骤嵌套深度是相同的。按照前面的阅读顺序规则，这里应该编号2
步骤先执行，然后再执行编号3步骤，再往下到了嵌套最深的编号4步骤，该步骤执行后最后执行编号1步骤。
最终该执行计划的执行顺序就是2->3->4->1。

知道了执行顺序还不够还需要知道每个步骤具体是在干什么，按照执行顺序来看，先看编号2步骤，'Seq Scan on test.t1’该句的意思是对test用户下的t1表进行全表扫描，后面的COST和ACTUAL部分前面已经解释过这里就不重复解释了。'Output: t1.id, t1.t, t1.name, t2.id, t2.salary’部分显示的是返回的表中哪些列。然后执行了编号3步骤，这里进行了HAHS运算把编号2步骤返回的结果生成了HASH表。然后执行编号4步骤，对t2表进行全表扫描。最后执行编号1步骤以t1表作为驱动表与T2表做HASH连接最终返回3行记录。


Explaining → 执行计划运算类型
     Seq Scan：   扫描表。
     Index Scan： 索引扫描。
     Bitmap Index Scan：索引扫描。
     Bitmap Heap Scan： 索引扫描。
     Subquery Scan： 子查询。
     Tid Scan：ctid = 以CTID为查询条件。
     Function Scan： 函数扫描。
     Nested Loop： 循环连接。
     Merge Join： 排序合并连接。
     Hash Join： 哈希连接。
     Sort：  排序，ORDER BY操作。
     Hash：  哈希运算。
     Result： 函数扫描，和具体的表无关。
     Unique： DISTINCT，UNION操作。
     Limit： LIMIT，OFFSET操作。有
     Aggregate： count, sum,avg, stddev集约函数。
     Group： GROUP BY分组操作。
     Append： UNION操作。
     Materialize： 子查询。
     SetOp： INTERCECT，EXCEPT

常见的访问方式

在 SQL 执行计划中，常见的访问方式（或称节点类型）主要包括以下几类：

1. 扫描节点（数据访问方式）

顺序扫描 (Seq Scan)

  • 全表扫描，逐行读取表中的所有数据块。

  • 适用于小表或需要返回表中大部分数据的情况；当表很大且选择性不高时成本较高。

  • 描述：直接读取表的整个数据文件（堆表），从第一个数据块到最后一个，逐行检查是否满足条件。

  • 工作原理：PostgreSQL 会一次性读取多个块（通过预读），然后对每一行应用过滤条件。由于是顺序 I/O，对于大表的全量读取效率较高，但若只需要少量行，则会浪费大量 I/O。

  • 适用场景：

    • 小表（通常小于 work_mem 或 shared_buffers 能缓存的大小）。
    • 查询需要返回表中大部分数据（例如没有过滤条件或过滤条件选择性差）。
    • 表上没有可用索引，或优化器认为索引扫描成本更高（例如表非常小）。

  • 成本特征：启动成本为读取第一个块的成本，总成本与表大小成正比。

索引扫描 (Index Scan)

  • 先遍历索引找到匹配行的物理位置（TID），再回表访问数据行。

  • 适用于选择性高的查询（返回行数较少），能大幅减少 I/O。

  • 描述：先遍历索引找到匹配行的物理位置（TID：块号和行号），然后根据 TID 到堆表中读取对应的数据行。

  • 工作原理：索引是独立的存储结构，按索引键值排序。扫描索引时，利用 B-Tree 快速定位到匹配的键值，然后获取对应的 TID，再回表访问数据页。由于回表是随机 I/O，当返回行数较多时成本会很高。

  • 适用场景：

    • 查询条件能利用索引（如等值查询、范围查询），且选择性较高（返回行数少）。
    • 需要排序或分组，索引可以避免显式排序（若索引顺序与 ORDER BY 一致）。

  • 成本特征：启动成本包括索引根节点到叶子节点的遍历，总成本与索引扫描行数和回表随机 I/O 相关。

仅索引扫描 (Index Only Scan)

  • 索引中包含了查询所需的所有列，无需回表，直接从索引返回数据。

  • 效率更高，但要求索引列覆盖查询列，且可见性映射（Visibility Map）显示所有元组可见。

  • 描述：索引本身包含了查询所需的所有列，因此无需回表，直接从索引返回数据。

  • 工作原理：PostgreSQL 的索引中存储了索引列的值，以及对应行的 TID。如果查询只涉及索引列，并且所有需要的列都在索引中，那么可以只扫描索引。但需要注意可见性：PostgreSQL 需要通过可见性映射（Visibility Map）检查行是否对所有事务可见，若不可见仍需回表获取实际行的可见性信息。

  • 适用场景：

    • 索引覆盖了查询的所有列（即索引列包含 SELECT 列表和 WHERE 条件中的列）。
    • 表大部分数据是可见的（例如更新不频繁的表），可见性映射能有效避免回表。

  • 优点：极大减少 I/O，提升查询速度。

位图扫描 (Bitmap Scan)

  • 分为 Bitmap Index Scan 和 Bitmap Heap Scan 两步：

  • 先通过索引构建一个位图，标识哪些数据块包含匹配的行；

  • 然后按照物理顺序扫描这些数据块，减少随机 I/O。

  • 适用于索引返回较多行，但又未达到全表扫描阈值的情况。

  • 描述：结合索引和堆表扫描，先利用索引构建一个位图，标识哪些数据块包含匹配行，然后按物理顺序扫描这些块，减少随机 I/O。

  • 组成部分：

    • Bitmap Index Scan：扫描索引，根据匹配行的 TID 构建位图。位图中的每一位对应一个数据块，标记该块是否有匹配行。
    • Bitmap Heap Scan：根据位图读取对应的数据块，并按块顺序扫描其中的行，应用过滤条件。

  • 适用场景：

    • 查询条件通过索引返回较多行（例如选择性不高但又不是全表），此时纯索引扫描会导致大量随机 I/O，而顺序扫描又可能读取太多无用块。位图扫描在两者之间取得平衡。
    • 多个索引条件可以通过位图 AND/OR 组合（BitmapAnd / BitmapOr）。

  • 优点：将随机 I/O 转换为顺序 I/O，提高效率。

2. 连接节点（表连接方式）

嵌套循环连接 (Nested Loop)

  • 对于外部表的每一行，遍历内部表查找匹配行。

  • 适用于外部表较小，且内部表有索引支持等值连接时效率较高。

  • 描述：对于外部表（驱动表）的每一行，遍历内部表查找匹配行。

  • 工作原理：

    • 如果内部表有索引，则对每一行使用索引快速查找匹配行（称为“索引嵌套循环”），效率较高。
    • 如果内部表无索引，则需要对内部表进行全表扫描，成本随外部表行数线性增长。

  • 适用场景：

    • 外部表较小，且内部表连接列有索引。
    • 连接条件不是等值条件（如 <, >），此时哈希连接和合并连接可能不适用。

  • 优点：能够快速响应前几行结果（启动成本低），适合 OLTP 类型的小数据量查询。

哈希连接 (Hash Join)

  • 先用内部表构建哈希表，再扫描外部表探测哈希表进行匹配。

  • 适用于两表大小相差不大，且连接条件为等值连接；无索引时通常优于嵌套循环。

  • 描述：先用内表构建一个哈希表，然后扫描外表，对每一行计算哈希值并在哈希表中探测匹配。

  • 工作原理：

    • Hash 节点：扫描内表，根据连接键计算哈希值，将行存入哈希桶中。如果哈希表太大，会分批写入临时文件（使用 work_mem）。
    • Hash Join 节点：扫描外表，对每一行计算哈希值，在哈希表中查找匹配行，返回结果。

  • 适用场景：

    • 等值连接。
    • 两表大小相当，或内表可以完全装入 work_mem（避免磁盘溢出）。
    • 无索引时，通常比嵌套循环高效。

  • 优点：只需要扫描内表一次（构建哈希表），外表一次（探测），总成本较低。

合并连接 (Merge Join)

  • 要求两个表按连接键排序，然后同时扫描并合并匹配行。

  • 适用于连接键已排序（如索引扫描输出）或显式排序后使用，适合等值连接和部分非等值连接。

  • 描述：两个表按连接键排序后，同时扫描并合并匹配行。

  • 工作原理：

  • 要求两个输入都已按连接键排序（可能来自索引扫描，或显式排序节点）。

  • 类似归并排序中的归并过程：同时推进两个指针，比较键值，输出匹配行。

  • 适用场景：

  • 等值连接或部分非等值连接（如 <, > 但需要数据有序）。

  • 两个表的连接列都有索引，且查询可以利用索引顺序避免显式排序。

  • 适用于大数据集且需要排序结果的情况。

  • 优点：对大数据集稳定高效，但若输入未排序则需要额外的排序开销。

3. 其他操作节点

物化 (Materialize)

  • 将子查询或中间结果存储在内存（work_mem）或临时文件中，以便重复扫描。

  • 常见于 CTE、子查询引用多次，或嵌套循环中对内表进行物化以减少重复扫描成本。

  • 描述：将子查询或中间结果存储在内存（work_mem）或临时文件中，以便重复扫描。

  • 工作原理：当执行计划中需要多次扫描同一个结果集（例如在嵌套循环中内部表被多次访问），物化节点会先一次性读取所有行并缓存，后续扫描直接从缓存读取，避免重复执行子查询或扫描基表。

  • 适用场景：

  • 嵌套循环连接中，内部表被反复扫描（且没有索引），物化可以避免多次扫描基表。

  • CTE（WITH 查询）被多次引用时。

  • 优点：减少重复计算或扫描开销。

排序 (Sort)

  • 对结果集按指定列排序，可能使用内存或磁盘文件。

  • 出现在 ORDER BY、GROUP BY、Merge Join 等需要有序数据的场景。

  • 描述：对输入行按指定列进行排序。

  • 工作原理：如果数据量小于 work_mem，在内存中进行快速排序；否则，使用临时文件进行外部排序（归并排序）。

  • 适用场景：

  • ORDER BY 子句。

  • 分组操作（GroupAggregate）需要输入有序。

  • 合并连接要求输入有序。

  • DISTINCT 或 UNION 等需要去重或合并的场景。
    注意：如果索引已经提供了所需顺序，可以避免显式排序。

聚合 (Aggregate)

  • 分组聚合计算，如 GroupAggregate、HashAggregate 等。

  • HashAggregate 使用哈希表进行分组，GroupAggregate 要求输入有序。

  • 描述：对行进行分组并计算聚合函数（如 SUM、COUNT、AVG）。

  • 常见类型：

    • HashAggregate：使用哈希表对输入行分组，适合无序输入，但需要内存容纳所有分组。
    • GroupAggregate：要求输入有序（通常来自排序节点或索引扫描），然后顺序扫描分组，适合大数据量但分组数较多的情况。

  • 适用场景：GROUP BY 子句，或者没有 GROUP BY 的全局聚合。

子查询扫描 (Subquery Scan)

  • 对子查询结果作为一个整体进行处理。

  • 了解这些访问方式有助于分析 SQL 性能瓶颈，并指导索引设计或 SQL 改写。

  • 描述：将子查询的结果作为一个整体进行处理，通常出现在 FROM 子句中的子查询（派生表）。

  • 工作原理：先执行子查询，将结果存储在临时表中，然后外层查询扫描该临时表。优化器通常会将子查询提升（flattening）以避免此节点，但某些情况下无法提升。

唯一扫描 (Unique)

  • 描述：从已排序的输入中去除重复行。
  • 工作原理：假设输入已按去重键排序，然后顺序扫描，只保留每个键的第一行。
  • 适用场景：SELECT DISTINCT 且输入有序，或者 UNION 等操作。
posted @ 2026-05-18 10:42  数据库小白（专注）  阅读(61)  评论(0)    收藏  举报