如何高效地在Postgres中对表进行Vacuum分析

Question

17

我在Postgres上运行一个巨大的查询，其中一个联接表总是进行顺序扫描。约束列上有索引，但Postgres没有使用它。我运行了VACUUM ANALYZE，现在Postgres查询计划指示正在使用索引扫描。

我的问题是，运行VACUUM ANALYZE的最有效方式是什么？它是否锁定了表？如果是的话，如何在生产环境中运行VACUUM ANALYZE？

- Ramanan

其中一张连接表总是执行顺序扫描。该列上的索引已经存在，但Postgres没有使用它。这与VACUUM无关。 - wildplasser

也许这与此无关。但是在我进行了一次vacuum分析之后，计划发生了变化。如果您能提供一个更有意义的答案，那将会更有帮助，而不仅仅是说它与此无关。 - Ramanan

是的，VACUUM ANALYZE 会更新统计信息，这可能会导致选择另一个执行计划。通常只有在表发生更改（例如添加索引或将新数据导入新表）时才会产生影响（抱歉，我之前忽略了 ANALYZE 部分）。 - wildplasser

2个回答

9

您可以仅运行分析，无需运行vacuum。语法如下：

ANALYZE [ VERBOSE ] [ table_name [ ( column_name [, ...] ) ] ]

在文档中提到：

ANALYZE仅需要目标表上的读锁，因此它可以与表上的其他活动并行运行。

您可以在此处找到更多信息：

真空是否分析锁定表格？

不，是“FULL VACUUM”命令会锁定表格。

- lsilva

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Omer Farooq · Accepted Answer

"清理空间操作（Vacuum Analyze）"实际上执行两个完全不同的任务：

"清理空间操作（Vacuum Analyze）"是一种手动清理操作，通常每周或每月执行一次，具体取决于对数据库执行更新/删除操作的频率。此操作可以在特定表上执行，也可以在整个数据库上执行。根据数据库的大小和执行此操作的频率，此操作可能需要花费30分钟甚至数天的时间。

何时使用VACUUM FULL和ANALYZE：

如果您的数据库占用了过多的空间，没有剩余空间供操作系统执行其他操作，则需要进行VACUUM FULL操作，建议同时添加ANALYZE选项。如果您有一个高写入频率的数据库，则建议每3-6个月执行此操作一次。

VACUUM(FULL, ANALYZE, VERBOSE);

如果您无法锁定整个数据库，并且只需要释放由某个执行大量更新/删除的表占用的空间。那么请对该特定表使用VACUUM FULL。

VACUUM FULL VERBOSE your_table_name;

如果您遇到一个问题，即您的查询随着时间的推移变得越来越慢，例如在查询上运行 EXPLAIN 时有时使用顺序扫描，而具有不同参数的相同查询使用索引扫描。那么这意味着您的表没有完全被分析。可以对整个数据库或特定表执行分析。在此操作期间，数据库或表不会被锁定，并且此操作后，您的查询将执行得更好。

ANALYZE VERBOSE your_table_name

自动分析：

尽管您可能永远不需要手动分析数据库，因为自动分析守护程序会在后台运行，并分析更新/删除量超过表大小的10％（默认阈值）的表格。但在大型表上，这个阈值从未达到，即使是5％的阈值查询也会变慢。因此，应定期手动执行ANALYZE和VACUUM FULL。

自动清理：

自动清理是另一个在后台运行的守护程序，可以在不锁定表格的情况下对其进行清理。自动清理还会自动运行自动分析，因此自动清理还会自动分析表格。默认情况下，自动清理对于表格的更新/删除量必须达到表大小的20％才能执行操作。

示例：

假设有一张有4,000万行数据的表格，当表格接收到8,000,000次更新或删除时，自动清理将会运行。同样，该表格需要接收4,000,000次更新或删除才能启动自动分析。通常，在达到这个阈值之前，这样大小的表格会变得缓慢，因此建议定期手动执行VACUUM FULL ANALYZE。