为什么PostgreSQL不自动缓存执行计划？这可能是最硬核的优化解读

为什么PostgreSQL不自动缓存执行计划？这可能是最硬核的优化解读
 
前言

在数据库性能方面，查询语句的执行计划是最关键的因素之一。每当数据库接收到一个查询时，它必须决定如何以最有效的方式执行该查询。这个决策过程——称为执行计划。 计算并生成最优的执行计划在时间上可能非常昂贵，尤其是对于经常执行的查询语句。为了减轻这种开销，数据库采用执行计划缓存（Oracle和SQL Server都会自动缓存执行计划）来解决这个问题，使它们能够重用先前计算的执行策略，而不是每次执行时都重新计算执行计划。
然而，执行计划缓存的工作方式在不同的数据库系统之间可能存在显著差异。PostgreSQL 对执行计划缓存采用了一种更加动态和自适应的方法，而 SQL Server 默认则会积极缓存所有语句的执行计划，从而导致性能上的巨大差异。
这里会深入探讨 PostgreSQL 如何处理执行计划缓存，并将它与 SQL Server 进行比较，以及如何在针对这两个数据库进行性能优化。然后还将讨论预处理语句、函数缓存、通用与自定义执行计划，以及两个数据库中的常见性能陷阱。



PostgreSQL 如何缓存执行计划

PostgreSQL 并不会自动缓存SQL 语句的执行计划。每次执行SQL 查询（如 SELECT、INSERT、UPDATE 或 DELETE）时，PostgreSQL 都会从头开始解析、优化生成执行计划并执行该查询。
这一行为与 SQL Server 大相径庭，但是后者默认会全局缓存执行计划。虽然这看起来一开始是一个劣势，但实际上，这使得 PostgreSQL 在数据分布随时间变化的动态环境中能够做出更好的执行计划优化决策。然而，PostgreSQL 确实会在某些情况下缓存执行计划，包括prepare预处理语句和 PL/pgSQL 函数。
 
预处理语句和查询计划缓存
在 PostgreSQL 中如果要使用执行计划缓存，可以使用预处理语句。预处理语句允许 PostgreSQL 缓存查询的执行计划，这样每次执行查询时就不需要重新编译它。

1. PREPARE get_users (INT) AS
2. SELECT * FROM users WHERE age > $1;
4. EXECUTE get_users(30);

复制代码

在上面例子中：

• 第一次调用 PREPARE 语句时，PostgreSQL 解析并规划查询。
  
• 当 EXECUTE 执行时，会重用缓存的执行计划，从而避免了额外的解析和规划开销。
  

自定义执行计划 vs. 通用执行计划

默认情况下，PostgreSQL 从自定义执行计划开始——该执行计划针对特定的参数值进行了优化。然而，如果一个prepare预处理语句被多次执行（通常是5次或更多次），PostgreSQL 会评估是否应该切换到通用执行计划。自定义执行计划是根据实际的参数值进行优化的，并且可能使用索引扫描、顺序扫描或其他优化的执行路径，这取决于数据的分布情况。
另一方面，通用执行计划是没有特定参数值的，而是依赖于来自 pg_statistic 的表统计信息来估算行的基数/选择性。这种方法消除了每次执行时的执行计划生成开销，但如果数据分布不均匀（数据倾斜），可能会导致查询使用的执行计划不佳。



PL/pgSQL 函数和执行计划缓存

PostgreSQL 还会在 PL/pgSQL 函数中缓存执行计划。当函数包含SQL语句时，PostgreSQL 在第一次执行后会缓存执行计划。

1. CREATE FUNCTION get_users_by_age(age_limit INT)
2. RETURNS SETOF users AS $$
3. BEGIN
4.     RETURN QUERY SELECT * FROM users WHERE age > age_limit;
5. END;
6. $$ LANGUAGE plpgsql;

复制代码

第一次运行该函数时，PostgreSQL 会为 SELECT 语句创建一个缓存的执行计划。如果该函数被频繁调用，PostgreSQL 可能会像处理预处理语句一样切换到通用执行计划。为了控制这一行为，PostgreSQL 允许开发人员手动强制指定缓存策略：

1. ALTER FUNCTION get_users_by_age SET plan_cache_mode = 'force_custom_plan';
2. ALTER FUNCTION get_users_by_age SET plan_cache_mode = 'force_generic_plan';

复制代码

理解索引下的执行计划行为

PostgreSQL 决定使用自定义执行计划还是通用执行计划的一个主要因素是索引选择性。如果某个字段具有高基数（有很多唯一值），通常使用索引扫描是最佳选择。然而，如果某个字段具有低基数（唯一值较少），顺序扫描可能更高效。例如，考虑以下情况：

1. CREATE INDEX idx_users_age ON users(age);

复制代码

如果大多数年龄值分布均匀，PostgreSQL 可能更倾向于使用通用执行计划，因为参数变化对执行时间的影响不大。然而，如果某些值出现频率较高（例如：年龄 = 25 占据了表 60% 的行数），使用自定义执行计划将更有效。
总结

理解执行计划缓存的工作原理对于数据库性能调优至关重要。PostgreSQL 采取动态方法，避免了参数嗅探的陷阱，但需要明确配置以实现执行计划的重用。另一方面，SQL Server 激进地缓存执行计划，这可以减少每次执行计划生成的开销，但当参数值变化较大时，可能会引发参数嗅探问题。
对于从 SQL Server 转向 PostgreSQL 的开发人员，适应 PostgreSQL 的执行计划缓存行为可能需要一些时间。然而，通过谨慎使用prepare预处理语句、函数和 plan_cache_mode参数设置，开发人员可以精细调优 PostgreSQL，以实现最佳性能。
 
 

本文版权归作者所有，未经作者同意不得转载。

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

热心回复！

不错，里面软件多更新就更好了

这个有用。

这个有用。

这个有用。

感谢，下载保存了

新版吗？好像是停更了吧。

不错，里面软件多更新就更好了

新版吗？好像是停更了吧。

yyds。多谢分享

感谢分享，学习下。

感谢分享，下载保存了，貌似很强大

感谢，下载保存了

东西不错很实用谢谢分享

收藏一下   不知道什么时候能用到

感谢发布原创作品，程序园因你更精彩

谢谢楼主提供！

东西不错很实用谢谢分享

这个有用。