postgresql执行计划_百度小程序_上海毫米网络优化公司

1. 语法

EXPLAIN [statement]
EXPLAIN [option] [statement]
EXPLAIN [all_option] [statement]

1.1. 参数选项

analyze
    -执行真实的SQL，除估算成本外，额外输出一项实际结果
verbose
    -输出每个结点的详细信息
costs
    -输出估算成本(默认开启)
buffers  --(前提：必须打开analyze)
    -输出缓存使用信息命中率、脏数据、写，包含：共享块、本地块、临时块
format { text | xml | json | yaml }
    -指定输出格式

若想查询DDL真实语句成本，可以利用事务回滚方法来使用执行计划。例如：
--开始事务
BEGIN;
--DDL语句执行查询计划
    EXPLAIN ANALYZE UPDATE [table] SET id = id + 1;
--回滚事务
ROLLBACK;
事务回滚后并不会更新真正的数据，这样就能达到既想查看DDL语句的真实成本，又不会改变数据的好处。

2. 查看执行计划

执行计划输出结构示意图：

postgresql执行计划,第1张

2.1. 整体结构解析

执行计划的结构是怎样的？

按树形结构划分，每层分支用节点来表示，每个结点表示SQL在这一阶段做了什么。
红框表示第1层，蓝框表示第2层，绿框表示第3层，黄框表示第4层。
- 每一层都标识了扫描方式、估算成本、输出字段、内存命中。
怎么查看执行计划的结构？
- 从下到上：每个箭头表示1个节点。上一个节点的输入信息来自于它的下一个节点，所以需要从最下面开始分析，依次读到最顶层。
- 从里往外：由于上层结果是下层结果的输出，所以在理清层次结构后，需要先分析最里层，再依次往外分析。
  例如：最里层的启动成本是0.00，结束成本是10.00；那么上一层可能就是启动成本从10.00开始，结束成本大于10.00。以下示意图中蓝框就是最好的例子：
  红框：每个节点的开头说明
  - HashAggregate：表示走的hash聚合函数
  - Seq Scan：表示全部扫描
    蓝框：该节点的估算成本
    - cost=1.92..1.95：表示启动成本1.92，结束成本1.95
    - rows：该语句返回的行数
    - width：该行的平均字节数
      绿框：该节点的真实成本
      
      time=0.087..0.093：表示启动成本0.087，结束成本0.093
      rows：该节点返回的行数
      loops：该节点循环次数
      其他：该节点的详细信息
      
      Output：该节点输出的sql语句
      Batches：该节点内存使用大小
      Buffers：内存命中率
  简单说明
```
                                          QUERY PLAN
-------------------------------------------------------------------------------------------
 HashAggregate  (cost=1.92..1.95 rows=3 width=21) (actual time=0.100..0.102 rows=2 loops=1)
 'hash聚合扫描'  '(估算成本=启动成本..结束成本 , 执行SQL返回的行数 , 每行平均字节数)'  '(实际成本)'
   Output: count(*), "position"
     '表示该节点执行的字段'
   Group Key: pay_scale."position"
     '表示该节点执行的字段'
   Batches: 1  Memory Usage: 24kB
     '表示该节点内存使用大小'
   Buffers: shared hit=7 read=10
     '表示共享内存中有7个命中块，10个未命中(可能在系统缓存中命中的)'
```
  2.2. 各个节点说明
  
  ① 扫描节点
  
  顺序扫描（seq scan）
  - 控制参数：enable_seqscan = on
  - 解释说明：根据实际的数据存取顺序，连续扫描所有数据。（多用于无索引的情况下）
    适用情况：
    - 一般为数据量小、且选择率高的表。
    - 1000条数据以下，select 查出结果大于500条
    索引扫描（Index scan）
    - 控制参数：enable_indexscan = on
    - 解释说明：根据查询条件扫描索引。因为索引是有序的，所以采用对半查找方式，快速找到符合条件的索引数据。再过滤条件和索引键值进行比较。
      适用情况：
      
      一般数据量大，但选择率较低的表。
      1w条数据以上，select 查出结果低于实际条数的20%。
      注意情况：
      
      如果索引条件不存在（选择率非常高），性能会严重下降，甚至不如全表扫描。
      位图扫描（Bitmap scan）
      - 控制参数：enable_bitmapscan = on
      - 解释说明：
        1、先通过Bitmap Index Scan索引扫描，在内存中创建一个位图表，每个bit表示一个与过滤条件有关的页面（此页面有可能数据为1，不可能为0）。
        2、再通过Bitmap Heap Scan表扫描，在内存中创建好的位图表指针对应的页面进行顺序扫描，排除不符合的记录，返回需要的结果。
        
        适用情况：
        
        列中含有重复值。
        查询中包含and、or等范围性查找。
        
        
        
        TID扫描（TID Scan）
        
        控制参数：enable_tidscan = on
        解释说明：根据数据实际存储位置的ctid进行扫描，获取元组。通过隐藏字段ctid扫描时标记数据位置的字段，通过这个字段来查找数据（速度较快）
        
        适用情况：
        
        where条件中带ctid的表。
        
        
        
        覆盖索引扫描（Index Only Scan）
        
        控制参数：enable_indexonlyscan = on
        解释说明：允许直接从索引得到元组。覆盖索引扫描要求查询中的某个表，所需要数据均可从这张表的同一索引的索引页面中获得。
        
        适用情况：
        
        更新少的表
        
        
        
        其他扫描节点
        
        Sample Scan
        
        数据取样功能，支持查询返回取样数据。
        
        当前只在常规表和物化视图上接受tables ample子句。
        
        Subquery Scan
        
        以另一个查询计划树（子计划）为扫描对象进行元组扫描，其扫描过程最终被转换为子计划的执行。
        
        主要包含：exists、in、not in、any/some、all。
        
        Function Scan
        
        扫描对象为：妇女会元组集的函数。
        
        该节点在Scan的基础上扩展定义了function列表字段，存放Function Scan涉及的函数，以及funcordinality字段，是否返回结果加上序号列。
        
        Valies Scan
        
        values计算由值表达式指定一个行值或一组行值。
        
        常见的：把它用来生成一个大型命令内的常量表，但是它也可以被独自使用。
        
        CTE Scan
        
        with提供了一种方式来书写大型查询中使用的辅助语句，这些语句通常被称为公共表达式或CTE，它可以被看成是被定义在一个查询中存在的临时表。
        
        with子句中的每个辅助语句可以是：select、insert、update、delete。
        with子句本身也可以被附加到一个主语句，主语句也可以是select、insert、update、delete。
        
        WorkTable Scan
        
        它与Recursive Union共同完成递归合并子查询。
        
        Foreign Scan
        
        扫描外部表，用于fdw或dblink和外部数据的交互情况。
        
        Custom Scan
        
        自定义扫描接口
        
        
        
        ② 连接节点
        
        嵌套循环连接（Nest Loop join）
        
        控制参数：enable_nestloop = on
        解释说明：扫描每条外表数据（m条），再与内表中所有的记录（n条）去连接。时间复杂度为：m * n。
        
        适用情况：数据量小的表。
        
        
        
        哈希连接（Hash join）
        
        控制参数：enable_hashjoin = on
        解释说明：对内表建立hash表，扫描所有内表数据到各个hash桶；再建立hash桶逐个扫描外表每一行，对外表数据进行hash到某个桶，再与这个桶里的数据进行连接。
        
        适用情况：数据分布随机，重复值不多的表。
        
        
        
        归并连接（Merge join）
        
        控制参数：enable_mergejoin = on
        解释说明：先对两张表排序，再做连接。
        
        适用情况：两张表的数据都是有序的。
        
        
        
        ③ 物化节点
        
        说明：物化节点是一类可缓存元组的节点。在执行过程中，很多扩展的物理操作符需要先获取所有元组后才能操作，这时就需要用物化节点将元组缓存（例如：聚合函数、无索引的排序）。
        
        
        物化节点（Material）
        
        控制参数：enable_material = on
        解释说明：用户缓存子节点结果。对于需要重复多次扫描的子节点，可以减少执行的代价。
        
        适用情况：结果在子查询中会被多次使用。
        
        
        
        分组节点（3种情况）
        
        1、Hash Aggregate
        
        控制参数：enable_hashagg = on
        解释说明：通过hash算法，把相同的值hash到同一桶中，再求聚集。
        
        适用情况：数据无序的表。
        
        2、Group Aggregate
        
        解释说明：通过排序的方式进行分组，再求聚集。
        
        适用情况：数据有序的表。
        
        3、Aggregate
        
        解释说明：执行含有聚集函数的group by操作，其中有3种策略：
        Plain：不分组的聚集计算。
        Sorted：下层节点提供排好序的元组（类似group方法）。
        Hash：先对下层节点提供的末排序元组进行分组，再计算。
        
        适用情况：含有聚集函数的group by操作。
        
        
        
        排序节点（Sort）
        
        控制参数：enable_sort = on
        解释说明：对数据进行排序。
        
        适用情况：输出结果是有序的情况。
        
        
        
        去重节点（Unique）
        
        解释说明：对下层节点返回已排序的元组进行去重。
        
        适用情况：查询中带distinct关键字（当要求去重的属性被order by子句引用时，一般会使用该节点）。
        
        
        
        其他物化节点
        
        Window Agg
        
        窗口函数
        T_SetOp
        
        setop语法节点
        Lock Rows
        
        使用锁定子句（for update、for share）
        Limit
        
        使用limit时的节点
        
        
        
        ④ 控制节点
        
        BitmapAnd / BitmapOr节点
        
        解释说明：这两个节点实现了2个或多个位图的and和or运算（将产生每一个位图的子计划放在一个链表中，在执行过程中先执行子计划节点获取位图，再进行and / or操作）。
        
        适用情况：2种节点都是位图类型，用于位图计算。
        
        
        
        Result节点
        
        解释说明：执行计划不需要扫描表，执行器会直接计算select的投影属性，或使用values子句构造元组。
        
        适用情况：针对那些不扫描的查询，用来优化包含仅需计算一次的过滤条件。
        
        
        
        Append节点
        
        解释说明：该节点会逐个处理这些子计划，当一个子计划返回所有结果后，会接着执行链表中的下一个子计划，直到全部执行完。
        
        适用情况：用于处理包含一个或多个子计划的链表。
        
        
        
        Recursive Union节点
        
        解释说明：对节点递归进行处理。
        
        适用情况：用于处理递归定义的union语句。
        
        
        
        ⑤ 并行节点
        
        并行全表扫描（Parallel SeqScan）
        
        当表数据量大、选择率低时，自动使用并行。
        
        当表数据量大、选择率高时，不自动使用并行（大于50%）。
        
        
        
        并行hash（Parallel Hash）
        
        例如使用hash join
        
        每个worker都是先扫描小表score计算hash，再并行扫描大表，最后做hash。将数据汇总到gather节点合并最终结果集。
        
        
        
        并行嵌套（Parallel NestedLoop）
        
        支持并行嵌套查询
        
        如果不开启并行，普通的hash join性能会比开启低很多。
        
        
        
        也支持其他并行
        
        Gather / Gather Merge
        并行聚集（Partial / Finalize Aggregate / HashAggregate / GroupAggregate）
        并行排序（Gather Merge）
        并行B-Tree索引扫描（B-tree Index Scan）
        并行Bitmap扫描（Bitmap Heap Scan）
        并行Append（Parallel Append）
        并行Union（Parallel Union）
        
        
        
        开启并行的参数
        
        max_worker_processes（默认8）
        
        OS支持的最大后台进程数
        max_parallel_workers（默认8）
        
        最大并行worker数
        max_parallel_workers_per_gather（默认2）
        
        最大并行执行worker数
        max_parallel_maintenance_workers（默认2）
        
        最大并行维护worker数
        它们之间的配置关系：
        
        以 max_worker_processes 为主配置
        max_parallel_workers 不能超过主配置数量
        max_parallel_workers_per_gather 和 max_parallel_maintenance_workers 相加的值也不能超过主配置数量
        
        
        
        并行的触发条件
        
        表
        
        表的存储空间至少大于 min_parallel_table_scan_size（默认 8MB）
        索引
        
        索引的存储空间至少大于 min_parallel_index_scan_size （默认512KB）
        查询某张表的索引大小
        
        SELECT pg_size_pretty( pg_relation_size('[表名]'));
        
        
        
        并行注意项
        
        在开启并行时，并不是所有的SQL都适合开并行，也并不是并行越多性能就越好，每条SQL都有适合自己的worker数，需要考虑开启并行后对系统开销成本计算。
        如果cpu、共享内存、worker进程等资源是整个数据库共享。一个select如果消耗了大量的资源（比如开启了64个worker），其他会话能够申请的资源会变得有限，这一行为在OLTP事务应用中尤为重要。所以需要将worker设置为合理的范围。
        
        
        
        2.3. 参数输出说明
        
        postgresql的执行计划是以树形的方式输出该SQL的执行顺序，树形的呈现方式就是以节点呈现，而每个结点输出的详细信息由参数控制。
        costs
        
        costs主要输出执行计划的估算成本，而不是实际成本
        
        cost=0.00..1.60：表示启动成本0.00，结束成本1.60
        rows：该语句返回的行数
        width：该行的平均字节数
        计算估算成本的参数分别有：
        
        seq_page_cost：全表扫描的单个数据块代价因子。
        random_page_cost：索引扫描的单个数据块代价因子。
        cpu_tuple_cost：处理每条记录的CPU开销代价因子。
        cpu_index_tuple_cost：索引扫描时，每个索引条目的CPU开销代价因子。
        cpu_operator_cost：操作符或函数的开销代价因子。
        
        
        
        analyze
        
        （这里手动把costs关了，因为它是默认打开的，主要为了展示analyze的输出结果）
        
        analyze主要输出真实的成本（真正的去执行了这条SQL语句）
        
        time=0.010..0.013：表示启动成本0.010，结束成本0.013
        rows：该节点返回的行数
        loops：该节点循环次数
        Planning Time：计划执行的时间
        Execution Time：实际执行的时间
        如果不想让该SQL执行成功，可以利用事务回滚
        
        
        
        verbose
        
        verbose用于输出该节点执行的事情
        
        
        
        buffers
        
        buffers用于输出该节点的缓存命中率，前提是必须开启analyze
        
        hit：该节点shared_buffer命中的page数量。
        read：该节点shared_buffer没有命中的page，但可能在系统缓存中命中。
        dirtied：该节点shared_buffer中出现的脏块。
        written：该节点写入磁盘的page。
        shared hit blocks（共享块）：例如表、索引、序列的数据块。
        local hit blocks（本地块）：例如临时表、索引的数据块。
        temp read blocks（临时块）：例如排序、hash、物化节点。
        
        
        
        3. 优化建议
        
        （来源于postgresql官方文档）
        
        扫描节点优化建议
        
        过滤条件尽量提早使用。
        过滤性越高的字段靠前，过滤性低的字段靠后（id1 < 10 and id2 < 100)。
        核心SQL可以考虑采用"覆盖索引"方式，确保尽可能高效。
        多SQL总和考虑重复利用索引。
        不干扰过滤的前提下，order by 排序字段加入索引。
        索引应尽量使用字节数小的列，对于重复值多的列不建议使用索引。
        
        连接节点优化建议
        
        每次使用执行计划前，先对表进行分析（analyze [表名]）。
        调整合适的连接顺序（选择率低的join先执行）
        
        耗时节点的合理性
        
        1、数据扫描是否可以走索引、分区、物化视图？
        
        返回大量行数的表，采用顺序扫描。
        返回行数较少的表，采用索引扫描。
        2、多表的连接顺序是否合理？
        
        当使用3张表查询时（1张表数据小，2张表数据大），在做连接操作性，可以先让大表和小表连接，再去连接另一张大表。
        3、两张表的连接算法是否合理？
        
        查看基数估算结果是否准确，出现2表连接方式不合理。
        4、返回行数估算是否准确？
        
        比较估算成本与实际成本。若统计信息差距过大，进而导致选择了次优的执行计划。
        5、是否有内存不足的情况？
        
        当存在排序等情况时，操作的表超过了work_mem时，可以考虑适当增加该参数大小。