如何在PostgreSQL中手动更新表的统计数据 [英] How to manually update the statistics data of tables in PostgreSQL

问题描述

可以在PostgreSQL中使用ANALYZE语句来收集表的统计数据.但是，我不想真正将这些数据插入表中，我只需要评估一些查询的成本，是否有必要在PostgreSQL中手动指定表的统计数据而不实际将数据放入表中?

The ANALYZE statement can be used in PostgreSQL to collect the statistics data of tables. However, I do not want to actually insert these data into tables, I just need to evaluate the cost of some queries, is there anyway to manually specify the statistics data of tables in PostgreSQL without actually putting data into it?

推荐答案

我认为您正在将ANALYZE与EXPLAIN ANALYZE混淆.有不同的事情.

I think you are muddling ANALYZE with EXPLAIN ANALYZE. There are different things.

如果您希望在不应用更改的情况下查询成本和时间安排，那么唯一真正的选择就是开始交易，在EXPLAIN ANALYZE下执行查询，然后在ROLLBACK下执行.

If you want query costs and timing without applying the changes, the only real option you have is to begin a transaction, execute the query under EXPLAIN ANALYZE, and then ROLLBACK.

这仍然会执行查询，这意味着:

This still executes the query, meaning that:

• 消耗了CPU时间和I/O
• 在此期间，锁仍被占用并保持住
• 实际上将新行写入表和索引，但从未将其标记为可见.它们将在下一个VACUUM中清理.

• CPU time and I/O are consumed
• Locks are still taken and held for the duration
• New rows are actually written to the tables and indexes, but are never marked visible. They are cleaned up in the next VACUUM.

这篇关于如何在PostgreSQL中手动更新表的统计数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！