原文地址https://boringsql.com/posts/portable-stats/无需生产数据即可获取生产查询计划2026-03-08 · 8分钟阅读 · Radim Marek目录在上一篇文章中我们介绍了 PostgreSQL 规划器如何读取pg_class和pg_statistic来估计行数、选择连接策略以及判断索引扫描是否值得。信息很明确当统计信息错误时其他一切都会随之出错。流复制提供逐位复制因此所有副本都与主服务器共享相同的统计信息。但有一件事我们之前没有谈到。统计信息是针对生成它们的数据库集群的。填充它们的主要方式是ANALYZE这需要实际的数据。PostgreSQL 18 改变了这一点。两个新函数pg_restore_relation_stats和pg_restore_attribute_stats直接将数字写入系统目录表。结合pg_dump --statistics-only你可以将优化器统计信息视为可部署的工件。紧凑、可移植、纯 SQL。这个功能是由升级用例驱动的。在过去主要版本升级通常会清空pg_statistic迫使你运行ANALYZE。在大型集群上这可能需要数小时。PostgreSQL 18 的升级现在会自动传输统计信息。但这仅仅是开始。同样的逻辑让你可以从生产环境导出统计信息并将其注入任何地方——测试数据库、本地调试或作为 CI 流水线的一部分。问题所在你的 CI 数据库有 1,000 行。生产环境有 5000 万行。规划器对两者做出的决策完全不同。在 CI 中运行EXPLAIN无法告诉你生产环境中的计划是什么样的。这正是 RegreSQL 背后的核心理念。当规划器看到生产规模的统计信息时在 CI 中捕获查询计划回退会可靠得多。调试也是如此。某个查询在生产环境中很慢你想在本地重现这个计划但你的数据库有不同的统计信息规划器选择了可预测的路径。移植生产统计信息可以为你提供规划器在生产环境中必须考虑的思维快照而无需实际进入生产环境。pg_restore_relation_stats支持可移植 PostgreSQL 统计信息的第一个函数是pg_restore_relation_stats。它通过可变的名/值对将表级数据直接写入pg_class。SELECTpg_restore_relation_stats(schemaname,public,relname,orders,relpages,123513::integer,reltuples,50000000::real,relallvisible,123513::integer,relallfrozen,120000::integer);但这只是一个示例。让我们修改一些真实的统计信息来看看其全部价值。我们将创建一个小表注入类似生产环境的虚假统计信息并观察规划器改变主意。CREATETABLEtest_orders(idintegerGENERATED ALWAYSASIDENTITYPRIMARYKEY,customer_idintegerNOTNULL,amountnumeric(10,2)NOTNULL,statustextNOTNULLDEFAULTpending,created_atdateNOTNULLDEFAULTCURRENT_DATE);INSERTINTOtest_orders(customer_id,amount,status,created_at)SELECT(random()*99991)::int,(random()*50005)::numeric(10,2),(ARRAY[pending,shipped,delivered,cancelled])[floor(random()*41)::int],2024-01-01::date(random()*365)::intFROMgenerate_series(1,10000);CREATEINDEXONtest_orders(created_at);CREATEINDEXONtest_orders(status);ANALYZEtest_orders;当你检查当前统计信息时它具有可预测的数据。SELECTrelname,relpages,reltuplesFROMpg_classWHERErelnametest_orders;relname | relpages | reltuples ---------------------------------- test_orders | 74 | 10000 (1 row)在 74 页上分布着 10,000 行规划器选择了顺序扫描。EXPLAINSELECT*FROMtest_ordersWHEREcreated_at2024-06-01;QUERY PLAN ----------------------------------------------------------------- Seq Scan on test_orders (cost0.00..199.00 rows5891 width26) Filter: (created_at 2024-06-01::date) (2 rows)现在注入生产规模的表统计信息SELECTpg_restore_relation_stats(schemaname,public,relname,test_orders,relpages,123513::integer,reltuples,50000000::real,relallvisible,123513::integer);你可能对结果感到惊讶。EXPLAINSELECT*FROMtest_ordersWHEREcreated_at2024-06-01;QUERY PLAN ------------------------------------------------------------------ Seq Scan on test_orders (cost0.00..448.45 rows17649 width26) Filter: (created_at 2024-06-01::date)规划器仍然使用顺序计划。只有估计的行数发生了变化。为什么如果你还记得上一篇文章这就是列级统计信息发挥作用的地方。直方图边界仍然与我们插入的原始 10,000 行相匹配。pg_restore_attribute_stats此函数将列级统计信息写入pg_statistic即ANALYZE填充 MCV、直方图和相关性的同一个系统目录。在上一节中尽管规划器认为表有 5000 万行我们却让规划器卡在了顺序扫描上。缺失的部分是列级统计信息。让我们从之前的地方继续为created_at注入直方图边界。SELECTpg_restore_attribute_stats(schemaname,public,relname,test_orders,attname,created_at,inherited,false::boolean,null_frac,0.0::real,avg_width,4::integer,n_distinct,-0.05::real,histogram_bounds,{2019-01-01,2019-07-01,2020-01-01,2020-07-01,2021-01-01,2021-07-01,2022-01-01,2022-07-01,2023-01-01,2023-07-01,2024-01-01}::text,correlation,0.98::real);现在规划器知道数据跨越了 5 年。过滤 2024 年最后 6 个月的查询覆盖了一个狭窄的切片。EXPLAINSELECT*FROMtest_ordersWHEREcreated_at2024-06-01;QUERY PLAN ---------------------------------------------------------------------------------------------------- Index Scan using test_orders_created_at_idx on test_orders (cost0.29..153.21 rows6340 width26) Index Cond: (created_at 2024-06-01::date)直方图边界将数据的非 MCV 部分划分为等人口桶。如果most_common_vals占了数据的大部分直方图只覆盖剩余的尾部。桶的数量由default_statistics_target控制默认值为 100意味着 101 个边界。计划翻转了直方图告诉规划器数据跨越了 2019-2024 年所以 2024-06-01匹配一个狭窄的尾部。是 5000 万行中的一小部分。之前被忽略的索引扫描现在成了显而易见的选择。表级统计信息设定了规模列级统计信息塑造了选择性两者共同改变了计划。相关性统计信息告诉规划器物理行顺序与列排序顺序的接近程度。接近 1.0 的值意味着顺序访问模式——使索引扫描更便宜因为下一行很可能在同一页或相邻页上。对于像created_at这样的时间序列数据行按时间顺序插入相关性通常非常高。注入偏斜分布同一个函数处理 MCV 列表。在生产环境中你的status列不是均匀分布的95% 的订单是delivered1.5% 是pending。SELECTpg_restore_attribute_stats(schemaname,public,relname,test_orders,attname,status,inherited,false::boolean,null_frac,0.0::real,avg_width,9::integer,n_distinct,5::real,most_common_vals,{delivered,shipped,cancelled,pending,returned}::text,most_common_freqs,{0.95,0.015,0.015,0.015,0.005}::real[]);你可以看到EXPLAINSELECT*FROMtest_ordersWHEREstatuspending;QUERY PLAN --------------------------------------------------------------------------------------- Bitmap Heap Scan on test_orders (cost8.93..90.42 rows599 width27) Recheck Cond: (status pending::text) - Bitmap Index Scan on test_orders_status_idx (cost0.00..8.78 rows599 width0) Index Cond: (status pending::text) (4 rows)并将其与以下结果比较EXPLAINSELECT*FROMtest_ordersWHEREstatusdelivered;QUERY PLAN ------------------------------------------------------------------ Seq Scan on test_orders (cost0.00..448.45 rows28458 width27) Filter: (status delivered::text) (2 rows)同一列同一个操作符不同的计划。规划器对pending1.5%足够稀有值得使用索引使用位图索引扫描对delivered95%占表的大部分使用顺序扫描。MCV 列表中的选择性比率驱动了计划的选择。你可能注意到行估计值599 和 28458低于你对 5000 万行表的预期。规划器检查实际的物理文件大小。我们的表在磁盘上只有 74 页而不是我们注入的 123513 页。因此规划器按比例缩小了reltuples和relpages。绝对数字缩小了但它们之间的比率保持不变而决定计划形态的正是这些比率。在实践中使用pg_dump --statistics-only时你通常会将统计信息恢复到数据量相当的数据库中因此估计值会自然地对齐。pg_regresql 扩展pg_regresql扩展修复了这个缩放问题。它钩入规划器使其信任注入的relpages值而不是读取物理文件大小因此即使你的测试数据库很小成本估计也能与生产环境匹配。了解更多pg_dump我们介绍的函数是核心机制。对于实际操作pg_dump提供了你所需的一切。PostgreSQL 18 添加了三个标志。标志作用--statistics转储统计信息必须显式请求--statistics-only仅转储统计信息不转储模式或数据--no-statistics不转储统计信息当你导出生产数据库的统计信息时pg_dump --statistics-only-dproduction_dbstats.sql你会看到输出是一系列SELECT pg_restore_relation_stats(...)和SELECT pg_restore_attribute_stats(...)调用。正如我们上面解释的那样。将生产数据转化为可测试计划的完整工作流程可能如下所示# 1. 从生产环境转储模式pg_dump --schema-only-dproduction_dbschema.sql# 2. 从生产环境转储统计信息pg_dump --statistics-only-dproduction_dbstats.sql# 3. 创建包含模式的测试数据库createdb test_db psql-dtest_db-fschema.sql# 4. 加载测试数据可选已脱敏最小化psql-dtest_db-ffixtures.sql# 5. 注入生产统计信息psql-dtest_db-fstats.sql# 6. 查询计划现在与生产环境匹配psql-dtest_db-cEXPLAIN SELECT * FROM test_orders WHERE status pending统计信息转储非常小。一个有数百个表和数千个列的数据库产生的统计信息转储小于 1MB。生产数据可能有数百 GB。描述它的统计信息却适合放在一个文本文件中。保持注入的统计信息有效现在你可能会问有什么陷阱有一个很大的陷阱autovacuum 最终会启动并运行ANALYZE。这会将你注入的统计信息覆盖为真实数字你又回到了起点。为了防止这种情况请在你注入统计信息的表上禁用 autovacuum 分析。-- 禁用 autovacuumALTERTABLEtest_ordersSET(autovacuum_enabledfalse);-- 或者将分析阈值设置得极高使其永远不会触发ALTERTABLEtest_ordersSET(autovacuum_analyze_threshold2147483647);这里要小心。如果你还在开发环境中向这些表写入数据运行迁移、加载测试数据、测试插入那么每次写入都会使注入的统计信息进一步偏离现实。规划器将基于不再反映本地数据的生产分布进行规划。对于只读查询计划测试这正是你想要的。对于修改数据的集成测试你可能需要在每次测试运行后重新注入统计信息。并且请永远不要在生产环境中这样做未涵盖的内容正如我们之前看到的尝试注入relpages是徒劳的因为规划器会检查实际文件大小并按比例缩放。这限制了规划器可能估计的绝对行数。也就是说要获得与生产环境可比的数字你仍然需要创建可比的数据量当谈到此功能的主要用例——恢复备份时这不是问题。同样值得注意的是用于多变量相关性、跨列组的唯一计数以及列组合的 MCV 列表的CREATE STATISTICS在 PostgreSQL 18 中未被涵盖。这些仍然需要在恢复后运行ANALYZE。PostgreSQL 19 将通过pg_restore_extended_stats()填补这一空白。安全性恢复函数需要目标表上的MAINTAIN权限。这与 PostgreSQL 17 中引入的ANALYZE、VACUUM、REINDEX和CLUSTER所需的权限相同。为自动化授予该权限的最简单方法GRANTpg_maintainTOci_service_account;这将对数据库中的所有表授予MAINTAIN权限。足以让 CI 流水线注入统计信息而无需超级用户权限。