登录/ 注册
 找回密码
 立即注册
关闭

CSDN热搜

程序园 精品问答 技术交流 资源下载
返回列表 发新帖
首页 › 业界区 › 安全 ›PostgreSQL 执行计划缓存参数plan_cache_mode（译） ...

PostgreSQL 执行计划缓存参数plan_cache_mode（译）

巴沛若 昨天 15:16
原文地址：https://vladmihalcea.com/postgresql-plan-cache-mode/
本文阐述了PostgreSQL对于prepared statement预处理语句生成执行计划的规则，原文中并没有提及测试环境的PostgreSQL版本，笔者在PostgreSQL 16下测试，遇到了一些与原文不一致的地方，文末有详细的测试和验证。
以下为译文。



您是否正在为Spring、Jakarta EE或Java EE应用的性能问题所困扰？
想象一下，如果有款工具能自动检测JPA和Hibernate数据访问层的性能瓶颈根源，会怎样？
若能在开发阶段就借助这样的工具监控应用性能，在问题影响生产系统之前防患于未然，岂非绝佳之选？
Hypersistence Optimizer（译者注：原文作者推广的一款插件）正是这样的神器！它完美兼容Spring Boot、Spring Framework、Jakarta EE、Java EE、Quarkus、Micronaut及Play Framework。
与其在周六深夜紧急修复生产系统性能问题，不如使用Hypersistence Optimizer主动预防隐患，把宝贵时间留给真正热爱的事物！
 
Introduction

在本文中，我们将深入分析PostgreSQL的plan_cache_mode配置参数，并探讨何时适合采用force_custom_plan策略来替换generic plan通用执行计划。
 
 
Domain Model

我们假设有如下一个表结构
1.png


其中post_status 字段是一个如下的枚举类型:
  1. CREATE TYPE post_status AS ENUM (
  2. 'PENDING',
  3. 'APPROVED',
  4. 'SPAM'
  5. )
复制代码
假设该表中中有10万条贴文记录，但这些记录的status状态值分布极不均匀（存在严重的数据倾斜）。
例如，执行如下查询
  1. SELECT
  2.     a.*,
  3.     matching_records::float/row_count AS selectivity
  4. FROM (
  5.      SELECT
  6.          status,
  7.          COUNT(<em>) AS matching_records
  8.      FROM post
  9.      GROUP BY status
  10. ) a
  11. CROSS JOIN LATERAL (
  12.     SELECT COUNT(</em>) AS row_count
  13.     FROM post
  14. ) b
  15. ORDER BY selectivity DESC
复制代码
可以得到如下结果:
  1. | status | matching_records | selectivity |
  3. | -------- | ---------------- | ----------- |
  5. | APPROVED | 95000 | 0.95 |
  7. | PENDING | 4000 | 0.04 |
  9. | SPAM | 1000 | 0.01 |
复制代码
95% 的帖子是APPROVED状态.
4% 的帖子是PENDING状态.
1% 的帖子是spam状态.
 
 
基于status字段的筛选 Filtering by status

现在有基于status字段的这么一个查询，如下:
  1. SELECT id, title, status
  2. FROM post
  3. WHERE status = ?
复制代码
为了避免全表扫描，大多数情况下，你可能会想到在status字段上创建一个如下的索引
CREATE INDEX idx_post_status ON post (status)
尽管如此，基于status字段的索引的选择性（selectivity），上面的查询的执行的时候，PostgreSQL不一定会用到idx_post_status这个索引
case 1

比如，当用status为PENDING为过滤条件的时候，PostgreSQL的执行计划如下
  1. Bitmap Heap Scan on post
  2.   (cost=46.44..1231.07 rows=3890 width=57)
  3.   (actual time=0.233..1.132 rows=4000 loops=1)
  4.   Recheck Cond: (status = 'PENDING'::post_status)
  5.   Heap Blocks: exact=46
  6.   ->  Bitmap Index Scan on idx_post_status
  7.         (cost=0.00..45.47 rows=3890 width=0)
  8.         (actual time=0.211..0.212 rows=4000 loops=1)
  9.         Index Cond: (status = 'PENDING'::post_status)
复制代码
执行计划首先扫描idx_post_status索引，然后通过bitmap在内存页面中标记处目标数据库所在的数据页，随后，位图堆扫描（Bitmap Heap Scan） 会根据位图中的页面标识符，到 post 表的数据页中定位具体记录。
case 2

当根据status字段为SPAM的条件过滤的时候，PostgreSQL将会使用index scan替代bitmap index scan。
  1. Index Scan using idx_post_status on post
  2.   (cost=0.29..1089.83 rows=963 width=57)
  3.   (actual time=0.059..0.500 rows=1000 loops=1)
  4.       Index Cond: (status = 'SPAM'::post_status)
复制代码
case 3

然而，如果我们通过status为APPROVED的条件进行过滤，PostgreSQL将会忽略idx_post_status索引，并在Post表上使用full-table scan。
  1. Seq Scan on post
  2.   (cost=0.00..2386.00 rows=95147 width=57)
  3.   (actual time=0.034..36.765 rows=95000 loops=1)
  4.       Filter: (status = 'APPROVED'::post_status)
  5.       Rows Removed by Filter: 5000
复制代码
当需要扫描表中 95% 的数据时，使用索引的成本反而远高于全表扫描, 这就是为什么按 APPROVED 状态过滤时会选择顺序扫描。
正如前文中所述，由于状态值分布极不均匀，我们只需为选择性高的值（如 PENDING 和 SPAM）建立索引才具有实际意义。
因此，既然 idx_post_status 索引无法用于 APPROVED 状态值，我们可以将其修改为排除 APPROVED值的部分索引：
CREATE INDEX idx_post_status ON post (status) WHERE (status  'APPROVED')
 

默认的PostgreSQL plan_cache_mode

正如在这篇文章里提到的，PostgreSQL对于预处理语句（prepared statement），执行的前4次，生成定制化执行计划（custom execution plan）。从第5次开始，PostgreSQL将会生成一个通用执行计划（generic plan）来替代定制化执行计划。
译者注：这里并不清楚原文作者测试的PostgreSQL版本，测试中提到“前4次执行并未在服务器端预编译，从第5次开始，预处理语句就使用了在当前连接期间缓存的通用执行计划。”，这一点于笔者在PostgreSQL 16中的测试并不相符，详见本文末尾的测试。
我们可以使用如下的测试案例来验证这种行为：
  1. executeStatement(
  2.     connection,
  3.     "LOAD 'auto_explain'",
  4.     "SET auto_explain.log_analyze=true",
  5.     "SET auto_explain.log_min_duration=0"
  6. );
  7. LOGGER.info(
  8.     "Plan cache mode: {}",
  9.     selectColumn(
  10.         connection,
  11.         "SHOW plan_cache_mode",
  12.         String.class
  13.     )
  14. );
  15. try (PreparedStatement statement = connection.prepareStatement("""
  16.         SELECT id, title, status
  17.         FROM post
  18.         WHERE status = ?
  19.         """)) {
  20.     for (int i = 1; i <= 10; i++) {
  21.         executeStatementWithStatus(statement, PostStatus.APPROVED);
  22.     }
  23.     executeStatementWithStatus(statement, PostStatus.SPAM);
  24. }
复制代码
太棒了，对吧？
 
结论

当执行预处理语句时，PostgreSQL 可能采用定制执行计划（custom plan）或通用执行计划（generic plan）。
若发现 PostgreSQL 选择的通用执行计划不适用于当前绑定参数值，则可采用 force_custom_plan 策略，强制 PostgreSQL 跳过通用计划，改为每次查询都生成定制执行计划。
 
 
 
笔者验证测试于脚本


文中并没有提及测试环境的PostgreSQL版本，笔者在PostgreSQL 16下测试，遇到了一些与原文不一致的地方，也就是说，在预处理语句会针对不同的参数值生成不同的执行计划，执行超过5次之后，会生成通用执行计划，但是该通用执行计划并不是非常规参数的值对应的执行计划，而是基于一个常规值，比如前5次用一个分布站总行数95%的值做查询，执行计划是seq table scan，  最后生成generic plan的时候，并非基于该倾斜值生成的，而是基于一个一般值，生成一个index scan的执行计划缓存。
  1. protected int executeStatementWithStatus(
  2.         PreparedStatement statement,
  3.         PostStatus status) throws SQLException {
  4.     LOGGER.info(
  5.         "Statement is {}prepared on the server",
  6.         PostgreSQLQueries.isUseServerPrepare(statement) ?
  7.             "" :
  8.             "not "
  9.     );
  10.     int rowCount = 0;
  11.     statement.setObject(
  12.         1,
  13.         PostgreSQLQueries.toEnum(status, "post_status"),
  14.         Types.OTHER
  15.     );
  16.     try(ResultSet resultSet = statement.executeQuery()) {
  17.         while (resultSet.next()) {
  18.             rowCount++;
  19.         }
  20.     }
  21.     return rowCount;
  22. }
复制代码
 
笔者的测试环境是PostgreSQL 16，于原文中的测试有一些出入，总结起来就是：PostgreSQL在为prepared statement生成执行generic plan计划的时候，并不会为倾斜值（分布多，唯一性很低，默认情况下会做seq scan）生成执行计划，而是基于一个常规值生成执行计划（常规值是唯一性高，选择性高），这一点可以认为是PostgreSQL改进的一个特性。
2.png

 
3.png

 

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

使用道具 举报
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册
发帖
巴沛若
昨天 15:16

0

粉丝关注

16

主题发布

板块介绍填写区域，请于后台编辑
微信扫一扫