用ANALYZE从零开始重新构建PostgreSQL统计信息的最快方法是什么？ [英] What is the fastest way to rebuild PostgreSQL statistics from zero/scratch with ANALYZE?

问题描述

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

I have a PostgreSQL v10 database with a size of about 100GB.

什么是最有效（最快）的重建统计信息的方法，例如在主版本之后

What is the most efficient (fastest) way to rebuild statistics, for example after a major version upgrade?

ANALYZE 没有参数会更新整个数据库的统计信息，默认情况下—太慢了！这似乎是一个单一过程。

ANALYZE with no parameters updates statistics for then entire database by default — it's painfully slow! This seems like a single process.

有没有办法并行化它来加快速度？

Is there any way to parallelize this to speed it up?

推荐答案

您可以将 vacuumdb 与 pg_upgrade 建议的选项相同：

You could use vacuumdb with the same options that pg_upgrade suggests:

vacuumdb --all --analyze-in-stages

文档描述其作用：

仅计算统计信息以供优化程序使用（无真空），例如-仅分析。使用不同的配置设置运行$ 3 $的几个（当前三个）阶段，以更快地生成可用的统计信息。

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.

此选项对于分析新的数据库很有用从还原的转储或 pg_upgrade 填充。此选项将尝试
尽快创建一些统计信息，以使数据库可用，然后在后续阶段中生成完整的统​​计信息。

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 。

To calculate statistics with several parallel processes, you can use the option -j of vacuumdb.

这篇关于用ANALYZE从零开始重新构建PostgreSQL统计信息的最快方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！