PostgreSQL 12.10 持久连接查询间歇性耗时超10分钟问题

基础环境信息 #

• 客户端：C++ 基于libpq驱动连接数据库
• 服务端：PostgreSQL 12.10
• 业务表结构：

-- 分区父表event，按pkey字段做LIST分区
       Column        |   Type   | Collation | Nullable |  Default   | Storage  | Stats target | Description 
---------------------+----------+-----------+----------+------------+----------+--------------+-------------
 event_id            | bigint   |           | not null |            | plain    |              | 
 event_sec           | integer  |           | not null |            | plain    |              | 
 event_usec          | integer  |           | not null |            | plain    |              | 
 event_op            | smallint |           | not null |            | plain    |              | 
 rd                  | bigint   |           | not null |            | plain    |              | 
 addr                | bigint   |           | not null |            | plain    |              | 
 masklen             | bigint   |           | not null |            | plain    |              | 
 path_id             | bigint   |           |          |            | plain    |              | 
 attribs_tbl_last_id | bigint   |           | not null |            | plain    |              | 
 attribs_tbl_next_id | bigint   |           | not null |            | plain    |              | 
 bgp_id              | bigint   |           | not null |            | plain    |              | 
 last_lbl_stk        | bytea    |           | not null |            | extended |              | 
 next_lbl_stk        | bytea    |           | not null |            | extended |              | 
 last_state          | smallint |           |          |            | plain    |              | 
 next_state          | smallint |           |          |            | plain    |              | 
 pkey                | integer  |           | not null | 1654449420 | plain    |              | 
Partition key: LIST (pkey)
Indexes:
    "event_pkey" PRIMARY KEY, btree (event_id, pkey)
    "event_event_sec_event_usec_idx" btree (event_sec, event_usec) -- 匹配查询排序和过滤条件的二级索引
Partitions: event_spl_1651768781 FOR VALUES IN (1651768781),
            event_spl_1652029140 FOR VALUES IN (1652029140),
            event_spl_1652633760 FOR VALUES IN (1652633760),
            event_spl_1653372439 FOR VALUES IN (1653372439),
            event_spl_1653786420 FOR VALUES IN (1653786420),
            event_spl_1654449420 FOR VALUES IN (1654449420)
-- 业务规则：每周日新增1个分区，新数据全量写入最新分区

• 高频查询逻辑：长连接每30秒执行一次按时间取最早事件ID的查询，正常耗时1~2ms，原查询语句存在括号缺失问题：

SELECT event_id FROM event WHERE (event_sec > time.seconds) OR ((event_sec=time.seconds) AND (event_usec>=time.useconds) ORDER BY event_sec, event_usec LIMIT 1

• 异常特征：
  • 进程连续运行1~3周后，该查询间歇性出现耗时超10分钟的故障
  • 故障仅出现在运行很久的长连接上，新建连接执行同查询耗时正常
  • 重启进程重建连接后，故障立刻消失，查询恢复1~2ms的正常耗时

核心故障原因 #

故障是PostgreSQL 12.10版本分区表缺陷+长连接执行计划缓存机制共同导致的：

1. libpq驱动调用PQexecParams执行参数化SQL时，默认走PostgreSQL扩展查询协议，服务端会对预编译语句做执行计划缓存：前5次执行会根据传入参数生成自定义执行计划，之后优化器会评估生成通用的、与参数无关的缓存计划，如果通用计划估算成本不高于自定义计划，后续所有执行都会复用这个缓存计划，避免重复生成计划的开销。
2. PostgreSQL 12.10存在已知bug：对LIST分区表执行新增分区的DDL操作时，父表的元数据失效事件不会正确触发已缓存的通用执行计划失效。长连接上缓存的通用计划只会保留计划生成时存在的分区的元数据，无法识别后续新增分区上继承的event_event_sec_event_usec_idx二级索引。
3. 随着每周新增分区，缓存的通用计划的元数据和实际表结构偏差越来越大，优化器最终会选择劣化的执行路径：遍历所有缓存计划中记录的分区，对每个分区做全表扫描过滤符合时间条件的记录，再做全局排序取第一条结果，随着分区数、单分区数据量增长，执行耗时会飙升到十分钟级别。
4. 重建连接时，该连接上缓存的所有劣化执行计划会被全部清空，重新执行查询时会基于最新的表结构生成正确的索引扫描计划，因此耗时立刻恢复正常。

按落地成本从低到高排序，可任选一种或组合使用：

• 连接参数配置修复：连接建立后立刻执行SET plan_cache_mode = force_custom_plan;，强制当前连接所有预编译语句每次执行都根据参数和最新表结构生成自定义计划，不使用缓存的通用计划。该查询本身执行仅需1~2ms，计划生成的额外开销在百微秒级别，完全不会影响业务性能。
• 定期清理计划缓存：在每周新增分区的DDL执行完成后，主动在所有存活的业务长连接上执行DISCARD PLANS;，清空当前连接缓存的所有执行计划，强制后续查询重新生成正确计划。
• SQL写法优化：将原OR过滤条件改写为优化器更容易识别的行比较写法，同时补全缺失的括号，逻辑和原语句完全等价，对索引扫描路径的选择更友好：

SELECT event_id FROM event 
WHERE (event_sec, event_usec) >= ($1, $2)
ORDER BY event_sec, event_usec 
LIMIT 1

• 小版本升级：将PostgreSQL 12.10升级到12系列最新小版本，官方已经在后续小版本中修复了分区表DDL操作后执行计划缓存不失效的bug。

内容的提问来源于stack exchange，提问作者Mayank Kandari