postgresql - 使用 ANALYZE 从零/从头开始重建 PostgreSQL 统计信息的最快方法是什么？

Question

我有一个大小约为 100GB 的 PostgreSQL v10 数据库。

重建统计信息的最有效(最快)方法是什么，例如在主要版本升级之后？

ANALYZE 默认情况下没有参数更新整个数据库的统计信息——速度慢得令人痛苦!这似乎是一个单一的过程。

有什么方法可以并行化以加快速度吗？

Answer 1

您可以将 vacuumdb 与 pg_upgrade 建议的相同选项一起使用:

vacuumdb --all --analyze-in-stages

The documentation描述它的作用:

Only calculate statistics for use by the optimizer (no vacuum), like --analyze-only. Run several (currently three) stages of analyze with different configuration settings, to produce usable statistics faster.

This option is useful to analyze a database that was newly populated from a restored dump or by pg_upgrade. This option will try to create some statistics as fast as possible, to make the database usable, and then produce full statistics in the subsequent stages.

要计算多个并行进程的统计信息，可以使用 vacuumdb 的选项 -j。