PostgreSQL 18: pg_upgrade 升级时保留优化器统计信息

你在担心升级 PostgreSQL 大版本会丢失优化器统计信息吗？

特性提交日志

在 pg_upgrade 升级时迁移统计信息。

为 pg_dump 添加转储统计信息的支持，并在 pg_upgrade 执行过程中使用该功能，以便在升级时迁移统计信息。在大多数情况下，这避免了升级后重新进行耗时的 ANALYZE 的需要。

部分统计信息不会被迁移，例如扩展统计信息，或具有自定义 stakind 的统计信息。

现在，pg_dump 支持以下选项：–schema-only（仅导出结构）、–no-schema（无结构）、–data-only（仅导出数据）、–no-data（无数据）、–statistics-only（仅导出统计信息）和 –no-statistics（无统计信息）；这些选项可组合使用，以选择需要结构、数据和统计信息。选项的命名方式旨在与之前的 –schema-only 和 –data-only 选项保持兼容。

统计信息属于 SECTION_DATA 部分，除非其所属对象本身属于 SECTION_POST_DATA 部分。

统计信息通过调用 pg_restore_relation_stats() 和 pg_restore_attribute_stats() 函数来表示。

讨论：https://postgr.es/m/CADkLM=fzX7QX6r78fShWDjNN3Vcr4PVAnvXxQ4DiGy6V=0bCUA@mail.gmail.com

怎么做到的？

PostgreSQL 新版本将在pg_upgrade升级过程中，使用转储/恢复等操作保留优化器统计信息。

默认情况下，pg_dump不会导出优化器统计信息。您必须使用--with-statistics选项来导出优化器统计信息。

PostgreSQL 会转储表、表列和表达式索引的优化器统计信息。但是，它不会转储扩展统计信息的数据（仅转储对象定义）。因此，如果您使用了扩展统计信息，则应对所有使用它们的表运行ANALYZE。否则，您的 SQL 语句的性能可能会达不到预期水平。

PostgreSQL 18 为vacuumdb添加了一个新的选项，专门用于解决这个缺陷。您应该使用pg_upgrade升级后，运行下面的命令：

$ vacuumdb --all --analyze-only --missing-stats-only

此命令只会收集仍然缺失的优化器统计信息。升级后，这些统计信息将成为扩展统计信息。使用--missing-stats-only，您可以在最短的时间内完成准备工作。

PostgreSQL 18 导出的表的统计信息如下：

SELECT * FROM pg_catalog.pg_restore_relation_stats(
    'version', '180000'::integer,
    'schemaname', 'myschema',
    'relname', 'tab',
    'relpages', '443'::integer,
    'reltuples', '100000'::real,
    'relallvisible', '443'::integer,
    'relallfrozen', '443'::integer
);

导出的表或索引列的统计信息如下所示：

SELECT * FROM pg_catalog.pg_restore_attribute_stats(
    'version', '180000'::integer,
    'schemaname', 'myschema',
    'relname', 'tab',
    'attname', 'col',
    'inherited', 'f'::boolean,
    'null_frac', '0'::real,
    'avg_width', '4'::integer,
    'n_distinct', '-0.74276'::real,
    'most_common_vals', '{352,616,709,137,153,176}',
    'most_common_freqs', '{0.0003,0.0003,0.0003,0.00023333334,0.00023333334,0.00023333334}',
    'histogram_bounds', '{1,139,248,359,474,578,685,794,897,1021,2173,3217,4259,5413,6582}',
    'correlation', '0.8223619'::real
);

系统函数pg_restore_relation_stats()和pg_restore_attribute_stats()，会将数据写入系统表pg_class和pg_statistic。

非常不错的特性。感谢社区的所有相关人员。

参考

提交日志：https://git.postgresql.org/pg/commitdiff/1fd1bd871012732e3c6c482667d2f2c56f1a9395