内容提要
文章以多租户传感器数据库为例,指出预编译语句的通用计划会按小租户数据估算,导致大租户查询低效。作者通过实验展示缓冲访问与冷缓存I/O的差异,并提出将LIMIT下推到连接前(延迟连接)及使用覆盖索引,使各租户的计划性能趋于一致。
延伸解读
预编译语句的通用计划如何“误伤”大租户
文章揭示了一个常见陷阱:当预编译语句在多次执行后切换到通用计划,PostgreSQL 会基于小租户的数据分布估算参数,导致大租户查询时沿用为小数据量设计的计划。例如,通用计划假设 site_id 平均只有 1100 行,而实际 site 1 有 20 万行,造成大量不必要的嵌套循环和缓冲区访问。连接池中每个连接独立选择计划,先到达的租户决定了后续所有租户的执行方式。
缓冲访问数高不等于慢,但冷缓存下差异明显
通用计划在 site 1 一周查询中产生 21823 次缓冲访问,远高于定制计划的 1362 次,但热缓存下两者耗时相近(约 6ms)。然而冷缓存时,通用计划的普通索引扫描逐页请求,而定制计划的位图堆扫描能按块顺序预读,导致 16.8ms 对 11.7ms 的差距。文章提醒,仅看缓冲计数会忽略 I/O 模式的影响,实际磁盘或网络存储上差距会更大。
延迟连接与覆盖索引让计划性能趋于一致
将 LIMIT 下推到连接前(延迟连接)可确保只对前 50 行做连接查找,使查找开销不随租户数据量增长。配合覆盖索引(INCLUDE 包含所需列),site 1 的查询在冷热缓存下均降至 2ms 以内,且定制与通用计划差异小于 0.5ms。代价是索引增大 57MB 并增加写入开销,且索引仅扫描依赖可见性映射,新插入数据可能仍需回表。
Q&A
为什么多租户数据库里大租户的查询反而会变慢?
因为预编译语句的通用计划会基于小租户的数据进行估算,当大租户执行时,计划仍然按照小租户的行数来执行,导致大量不必要的查找和排序,从而变慢。
PostgreSQL 预编译语句什么时候会切换到通用计划?
前五次执行使用自定义计划,从第六次开始会构建通用计划,并比较其成本与之前自定义计划的平均成本,如果通用计划成本更低则切换。
如何查看 PostgreSQL 预编译语句使用的是通用计划还是自定义计划?
可以查询 pg_prepared_statements 视图中的 generic_plans 和 custom_plans 字段,或者使用 SET plan_cache_mode = force_generic_plan 强制使用通用计划,并用 EXPLAIN (GENERIC_PLAN) 查看。
什么是延迟连接(deferred join),它如何改善多租户查询性能?
延迟连接是将 LIMIT 下推到连接之前,先在子查询中取出前 N 行,再与其它表连接。这样大租户的查找次数不再随租户数据量增长,从而提升性能。
覆盖索引如何帮助解决预编译语句的计划问题?
覆盖索引包含内层查询所需的所有列,使得索引扫描无需回表,减少了堆访问,并且使自定义计划和通用计划的性能差异变得很小,从而消除计划选择的影响。
使用覆盖索引有什么代价?
覆盖索引会增加存储空间(例如 123 MB 对比 66 MB),并且每次插入新数据时都需要更新索引,增加 WAL 写入。此外,索引-only 扫描依赖可见性映射,新插入的数据可能仍需回表。