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

原文地址：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

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

其中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改进的一个特性。

 

 

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