PostgreSQL-执行计划分析分析基础（一）

PostgreSQL-执行计划分析分析基础（一）

前言

当执行的SQL出现瓶颈、慢查询等问题的时候，我们就需要分析SQL的执行计划，通过分析执行计划可以帮助我们对SQL找到具体的瓶颈点。
我们可以调整SQL查询条件或加索引来优化，对比执优化前后的行计划，找到适合业务场景的最佳优化方案。
下面通过示例，解读下pgsql的执行计划，让我们对分析执行计划有个初步的入门。

执行计划语法格式

两种语法结构：
explain [ ( option [,...] ) ] statement
explain [ analyze ] [ verbose ] statement

option选项有：
analyze [ boolean ] //会实际执行SQL，并返回SQL实际执行的相关统计信息
verbose [ boolean ] //显示执行计划的附加信息
costs [ boolean ] //默认开启，显示每个计划节点的启动成本、总成本，预计返回行数，预估返回结果集每行平均宽度
buffers [ boolean ] //显示缓冲区使用信息
format [ text | xml | json | yaml ] //执行计划执行输出格式
boolean：取值范围0、1、true、false；
statement：标准SQL语句；

--示例：
--最常用的一种方式
explain analyse select * from myschema.mytab;
explain analyse verbose select * from myschema.mytab;
--多个操作项，通过开关控制是否开启
explain (analyse 1) select * from myschema.mytab;
explain (analyse 0) select * from myschema.mytab;
explain (analyse 1,verbose 1) select * from myschema.mytab;
explain (analyse 1,verbose 0) select * from myschema.mytab;
--使用格式化输出
explain (analyse,format yaml) select * from myschema.mytab;
explain (analyse,format json) select * from myschema.mytab;
explain (analyse,format xml) select * from myschema.mytab;
explain (analyse,format text) select * from myschema.mytab;

创建测试表

数据库版本

PostgreSQL 14.2, compiled by Visual C++ build 1914, 64-bit

--建立测试表
create table mytab
(
    id            serial constraint mytab_pkey primary key,
    age           integer,
    name          varchar(10),
    sex           char(2),
    createtime    timestamp with time zone
);

生成测试数据

使用 generate_series() 函数生成测试数据；

--生成 1000 条测试数据
insert into myschema.mytab(age,name,sex,createtime)
select
    id%100 as age,
    '张'||id as name,
    case when id%2=0 then '男' else '女' end as sex,
    now() as ct
from (select generate_series(1,1000) id) t;
--验证数据
select * from myschema.mytab order by id desc;

执行计划结构

EXPLAIN的输出结果组织成为一系列的查询计划节点。
在这个结构当中的最低层次包含了查看表、扫描表或者使用索引来搜索等内容的节点项。
上级节点将会使用下级节点的输出内容并在此结果上进行操作。
当运行EXPLAIN的时候，其输出当中的每一行都是一个查询计划的节点项目。

--执行
explain analyse
select * from myschema.mytab;
--输出
Seq Scan on mytab  (cost=0.00..298.96 rows=15096 width=52) (actual time=0.018..1.684 rows=20000 loops=1)
Planning Time: 0.075 ms
Execution Time: 2.071 ms

输出的执行计划包含一个节点，该节点有两个分组，即 Seq Scan 节点，和括号分割的两个分组内容。

1. 第一组数据报告了该执行计划的评估信息，这些信息是不带有analyze的explain所能看到的唯一信息。
   (cost=0.00…30.45 rows=15096 width=52)

• cost=0.00…2.08
  cose展示2个成本使用…分割。
  第一个成本就是该节点的启动成本，即在该节点第一行输出产生之前所进行的工作量的大小。
  在这种情况之下其输出为0，这是因为Seq Scan立即返回了数据行。如果使用排序操作启动成本将花费一些时间来返回第一行数据。
  第二个是整个节点运行直至结束的估计成本。
  cost是基于如下的一些规则计算出的数字(默认)：

  • 顺序扫描一个块，cost的值为 1;
  • 随机扫描一个块，cost的值为 4;
  • 处理一个数据行的CPU代价，cost的值为 0.01;
  • 处理一个索引行的CPU代价，cost的值为 0.005;
  • 每个操作的CPU代价为 0.0025;

• rows=15096
  该节点表示运行完成所预期输出的记录数。

• width=52
  估计该节点每行输出所包含的平均字节数。对本示例而言大概将产生(15096*52)字节的输出。

2. 第二组输出当中的“actual”数据显示出了该查询实际运行的程度。
   (actual time=0.018…1.684 rows=20000 loops=1)

• actual time=0.018…1.684
  表明实际的启动成本并不是0，在开始产生输出结果时会占用一小部分时间。
  一旦真正开始执行，那么它总计将会花费1.684秒去执行该查询计划节点。
• rows=20000
  查询计划输出的记录数。
• loops=1
  诸如执行连接操作的某些节点，将会执行一次以上。在那样的情况下，其loops值显示则大于1，同时实际时间与数据行的数量值将会按照每次循环的数量显示，而不是总计数据。此时为了得到实际的总计数据就必须要与loops的实际值相乘。

总结

通过以上内容，相信大家对执行计划的使用和输出内容的含义有了初步的了解。
后续会继续分析limit、join的执行计划的输出。

PostgerSQL 9.0 性能调校：10.3节