背景

1、产品的问题点

• PG SQL无法穿越

2、问题点背后涉及的技术原理

• PG SQL 的执行计划是怎么生成的?

• • 通过SQL统计信息、结合PG的一些代价系数参数设置、通过公式计算cost, 最后选择cost最低的plan作为plan tree. (多表JOIN触发geqo的除外)

• PG SQL 是按什么执行计划执行的?

• • 如果时generic plan, 则按cached plan执行.
  • 如果cached plan算出来的代价大于custom plan的avg(cost), 则使用custom plan(相当于硬解析).

• • • 《执行计划选择算法 与 绑定变量 - PostgreSQL prepared statement: SPI_prepare, prepare|execute COMMAND, PL/pgsql STYLE: custom & generic plan cache》

• 使用绑定变量时就一定会用会话中已经缓存的执行计划吗?

• • 不一定, 参考如上. 如果cached plan算出来的代价大于custom plan的avg(cost), 则使用custom plan(相当于硬解析).

• 怎么知道过去某个时刻当时SQL的执行计划?

• • 不知道, 除非打印出来. 例如, 使用auto_explain插件

3、这个问题将影响哪些行业以及业务场景

• 通用

4、会导致什么问题?

• 无法轻松了解SQL在过去某个时刻的执行计划, 排查因为执行计划问题导致的性能抖动非常困难.

• • 例如: 统计信息未及时更新, 导致的执行计划问题
  • 例如: 数据倾斜, 并且产生plan cache的前几次导致执行计划运算有问题.

• • • 《PostgreSQL plan cache 源码浅析 - 如何确保不会计划倾斜》

5、业务上应该如何避免这个坑

• 可以开启auto_explain插件, 记录抖动SQL的执行计划以及每个NODE的开销, 耗时, IO, 命中, 等.
• 避免plan错误的一些手段

• • 缩短长连接使用生命周期, 避免错误的plan cache造成长时间的影响.
  • 加快统计信息收集频率, 避免统计信息不及时造成的query plan不正确.
  • 分析型的业务设置plan_cache_mode为force_custom_plan, 避免大量数据的变化统计信息频繁变化, 导致cache plan不争气的问题. force_custom_plan要求每次执行SQL时都重新生成query plan.

• • • 《PostgreSQL 11 preview - 增加强制custom plan GUC开关(plan_cache_mode)，对付倾斜》

6、业务上避免这个坑牺牲了什么, 会引入什么新的问题

• auto explain 开启后, 会打开时间计数器, 影响全局. 导致性能下降.

• • 《Linux 时钟精度 与 PostgreSQL auto_explain (explain timing on Linux时钟开销估算)》

7、数据库未来产品迭代如何修复这个坑

• 希望内核可以支持统计信息、元数据信息快照功能，用于回放SQL，得到过去的执行计划信息。
• 当query执行计划发生变化时, 通过参数控制, 例如SQL执行时间抖动超过多少时, 可以将前后的plan tree打印到日志中, 同时输出类似auto_explain的详细内容.