postgresql执行计划
作者:mmseoamin日期:2023-12-27

简介

        执行计划功能展示了SQL在执行的过程中走向、成本以及命中情况。主要作用于SQL调优,输出SQL执行的详细信息,有利于调优人员及时分析性能下降原因。

        

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层。
    • 每一层都标识了扫描方式、估算成本、输出字段、内存命中。

    postgresql执行计划,第2张

            

    怎么查看执行计划的结构?

    • 从下到上:每个箭头表示1个节点。上一个节点的输入信息来自于它的下一个节点,所以需要从最下面开始分析,依次读到最顶层。
    • 从里往外:由于上层结果是下层结果的输出,所以在理清层次结构后,需要先分析最里层,再依次往外分析。

      例如:最里层的启动成本是0.00,结束成本是10.00;那么上一层可能就是启动成本从10.00开始,结束成本大于10.00。以下示意图中蓝框就是最好的例子:

      postgresql执行计划,第3张

      红框:每个节点的开头说明

      • 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种策略:
                                1. Plain:不分组的聚集计算。
                                2. Sorted:下层节点提供排好序的元组(类似group方法)。
                                3. 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

                                              postgresql执行计划,第4张

                                              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:操作符或函数的开销代价因子。

                                                  postgresql执行计划,第5张

                                                      

                                              analyze

                                              postgresql执行计划,第6张

                                              (这里手动把costs关了,因为它是默认打开的,主要为了展示analyze的输出结果)

                                              analyze主要输出真实的成本(真正的去执行了这条SQL语句)

                                              • time=0.010..0.013:表示启动成本0.010,结束成本0.013
                                              • rows:该节点返回的行数
                                              • loops:该节点循环次数
                                              • Planning Time:计划执行的时间
                                              • Execution Time:实际执行的时间

                                                如果不想让该SQL执行成功,可以利用事务回滚

                                                postgresql执行计划,第7张

                                                      

                                              verbose

                                              postgresql执行计划,第8张

                                              verbose用于输出该节点执行的事情 

                                                      

                                              buffers

                                              postgresql执行计划,第9张

                                              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时,可以考虑适当增加该参数大小。