PostgreSQL执行计划解析（上）

PostgreSQL执行计划解析（上）

Whoami：5年+金融、政府、医疗领域工作经验的DBA
Certificate：OCP、PCP
Skill：Oracle、Mysql、PostgreSQL
Platform：CSDN、墨天伦、公众号（呆呆的私房菜）

业务范围：数据库安装部署、日常维护、主备切换、故障处理、性能优化、技术培训等。
需要的伙伴或者商业合作请移步 公众号【呆呆的私房菜】获取联系方式。

阅读本文可以了解到PostgreSQL优化器、执行计划查看、解析和影响执行计划变化的因素等。

01 SQL执行计划

要讲执行计划，我们要首先了解一下SQL的执行过程。

  1. 解析：SQL语句首先被解析器解析，检查语句的语法是否正确，将SQL语句转换成解析树。
  2. 重写：如果涉及到规则重写，如视图或某些触发器，SQL语句可能会被转换为另一种形式。
  3. 优化：解析树被优化器分析，生成一个或多个可能的执行计划，选择成本最低的执行计划。
  4. 执行：执行计划被执行器执行，涉及数据的检索、排序、连接等操作。
  5. 返回结果：执行结果被返回给客户端，用户可以查看查询结果或进行进一步的操作。

02 优化器概述

PostgreSQL查询优化的本质是通过选择最佳的查询执行计划来提高查询性能和效率。其核心目标是最小化查询执行所需的资源（如CPU时间、内存、磁盘I/O等）和响应时间。查询优化主要体现在下面几个方面：

  1. 查询重写：在解析查询之后，重写系统会对查询进行转换和简化。其中包含视图展开、变量重叠、子查询消除等，查询重写在于将查询转换成更简单、更容易优化的形似。

  2. 选择性估算：选择性是优化器的重要任务，选择性估算不准确会导致选择次优的执行计划，PostgreSQL通过统计信息来进行选择性估算。

  3. 成本模型：采用成本模型来评估不同执行计划的代价，其中包含CPU消耗、I/O操作、内存占用等因素，面对不同的执行计划进行比较，选择成本最低的一个。

  4. 索引利用：优化器会评估查询中是否可以使用索引，以减少扫描的行数和查询速度。其中索引类型包含b tree、hash、gin、gist等。

  5. 连接方法选择：对于多表连接，优化器会根据成本模型选择合适的连接方法（如nestloop join、hash join等）。

  6. 并行查询：在某些条件下，优化器会选择并行执行查询，以充分利用多核CPU计算能力，从而加速查询处理。并行查询包含并行扫描、并行连接和并行聚合等。

  7. 物化视图和缓存：利用物化视图可以提前计算和缓存查询结果，减少查询的计算开销，缓存机制则通过缓存热点数据来减少重复计算和I/O操作。

  8. 分区表：通过分区表将大表按照一定规则分割成多个小表，可以加速查询性能，尤其是在涉及范围查询或特定分区键查询的时候。

03 ex概述

explain命令显示每个给定的dml语句选中的执行计划的预计开销；
使用explain analyze选项会执行语句，也将会显示实际执行时间；
生成的执行计划阅读方式是自下而上的，每行信息代表一个节点；
ps：可以使用visual explain工具图形化方式显示执行计划，有助于理解复杂的dml语句。

语法格式：explain [(选项[,...])] 语句
选项可以是如下内容的一个或多个：
analyze ：真正执行实际sql，会修改数据库表结构，可以把explain analyze放到事务里边，执行完成后再回滚；
verbose ：显示执行计划的附加信息（sql输出的列、触发器名称等信息），默认关闭
costs ：显示启动成本和总成本，估计行数和每行宽度，默认开启
buffers ：显示缓冲区的使用信息，与analyze一起使用，默认关闭
format {text | xml | json} ：执行输出格式，，默认text
timing ：显示sql执行时间

那么，执行计划的代价是怎么计算的呢？计算采用的是假定的单位，与下列参数有关（cost值描述一个sql执行的代价）

seq_page_cost ：表示当预期按照顺序扫描一个数据页代价因子，cost为1；
random_page_cost：表示随机扫描一个数据页代价因子，cost值为4；
cpu_tuple_cost：表示处理一个数据行的cpu代价因子，cost值为0.01；
cpu_index_tuple_cost：表示处理一个索引行的cpu代价因子，cost值为0.005；
cpu_operator_cost：表示处理一个操作符或函数的开销代价因子，cost值为0.0025。

postgres=# create table a (id int, name varchar(10));
CREATE TABLE
postgres=# insert into a values (1, 't1');
INSERT 0 1
postgres=# analyze a;
ANALYZE
postgres=# explain select * from a;
                   QUERY PLAN
-------------------------------------------------
 Seq Scan on a  (cost=0.00..1.01 rows=1 width=7)
(1 row)
解读：
1. Seq Scan on a：这表示执行计划将使用顺序扫描来检索表 a 中的数据。顺序扫描意味着数据库将从表的开始到结束顺序地读取每一行。
2. (cost=0.00..1.01)：这表示执行这个查询的估计总成本。成本是从查询开始到完成的资源消耗估计，包括 I/O、CPU 等。这里的成本从 0 开始，直到完成查询的 1.01，意味着这是一个相对低成本的操作。
3. rows=1：这表示查询预计只返回一行数据。这个数字是一个估计值。
4. width=7：这是每行数据的平均字节数的估计值。这个数字包括了所有列的存储空间以及一些额外的存储开销。

# 使用 analyze 选项：
postgres=# explain analyze select * from a;
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Seq Scan on a  (cost=0.00..1.01 rows=1 width=7) (actual time=0.050..0.051 rows=1 loops=1)
 Planning Time: 0.059 ms
 Execution Time: 0.065 ms
(3 rows)
解读：
1. Seq Scan on a：表示执行计划使用了顺序扫描来检索表 a 中的数据；
2. Planning Time：数据库优化器生成执行计划所需的时间。
3. Execution Time：执行查询所需的实际时间。
4. Actual Time：查询实际开始到结束的时间，时间单位ms。
5. Rows：实际返回的行数。
6. Loops：查询执行的次数，对于单次查询通常是 1。

总的运行时间 = 执行器startup时间 + sql processing时间 + 执行器shutdown时间
注意：该时间不包含解析、重写或planning time。

04 explain标识符

标识符 **描述 **
scans 查询扫描方式
sort 使用in-memory或on-disk sort对结果集排序
unique 在输入集中检查重复行
limit 限制结果集大小
aggregate 读取数据集的所有行，计算集合函数的值，例如sum或average
appen 实现union
result 用于不从表返回数据的查询
joins 多表查询的物理连接方式（merge or hash）
filters 查询过滤条件
group 用于group by子句
subquery scan 用于union
subplan 用于子查询
materialize 用于物化一个子查询
setop 用于intersect和except操作符

查询扫描类型如下：

类型 描述
seq scan 顺序扫描 当没有可用过索引或者表的大部分记录都预计被返回时（会读取表中的dead row
index scan 索引扫描 对满足一个选择性where条件的索引存在
index only scan 仅索引扫描 当表中的所有列都在一个索引中时，直接从索引中取数，不在访问表文件
位图堆扫描 两个索引都应答同一个查询相关时，执行位图索引扫描首先要读取索引信息，填充位图，然后按照顺序读表

05 影响执行计划变化的因素

  1. 统计信息
    pg查询规划器的运行依赖于analyze命令的统计信息；
    analyze可以自行触发，或者作为vacuum的一个可选步骤；
    analyze命令可以运行在某个表上，甚至表上的某个列上；
    如果应用需要，可以灵活地选择更新某些统计数据，而不是全部；
    建议：规划一个数据库范围内的ANALYZE，然后每天在系统不太繁忙的时候运行一次。
表统计语法：
analyze [verbose] [table_name [(column_name [,...])]]

Tips：
1. 当analyze运行时，统计数据将被更新，存储在pg_class和pg_stat中；
2. 表上会加读锁，基本不影响其他查询业务；
3. 使用pg_stats可以很方便地进行查询；
4. 对大表读取部分内容做随机抽样；
5. 调整default_statistics_target变量可以控制分析量；
6. 捕获的关键统计信息（不同值的数目、最常见值、使用最频繁值）
  1. 索引
合理的索引使用可以减少查询的扫描范围，提高查询速度；
B-Tree索引适用于等值查询和范围查询；
GIN索引适用于全文搜索和数组类型列的查询；
GiST索引适用于地理空间数据类型和模糊匹配等。

  1. 查询结构
    查询中涉及的表连接方式、WHERE子句中的条件、以及查询中包含的操作类型（如排序、聚合等）都会影响执行计划的生成；
    tips：如果一个索引扫描匹配上了排序键(sort keys)或者结果集已被merge join 排序了，那么排序(Sorting)就是不必要的了。

  2. 规划器方法设定

索引方式：
set enable_bitmap_scan = false | true;
set enable_tidscan = false | true;
set enable_seqscan = false | true;
set enable_index_scan = false | true;

连接方式：
set enable_nestloop = false | true;
set enable_mergejoin = false | true;
set enable_hashjoin = false | true;

聚合方式：
set enable_hashagg = false | true;

排序方式：
set enable_sort = false | true;
  1. 数据库参数设定
    shared_buffers、work_mem、maintenance_work_mem 等参数对查询性能有直接影响。

06 性能优化建议

  1. 利用压测工具测试硬件及数据库服务的负载；
  2. 设定kpi指标，监控发现性能瓶颈；
  3. 谨慎修改参数配置优化；
  4. 合理优化索引；
  5. 使用sql高级特性，例如使用with查询；
  6. 选择性利用并行查询。
posted @ 2025-04-03 10:21  呆呆的私房菜  阅读(90)  评论(0)    收藏  举报