如何确定Postgres表的统计信息是否最新？

Question

在pgAdmin中，当表格的统计信息过时时，它会提示：

建议运行VACUUM

表模式.表中的预估行数与实际行数有很大偏差。您应该在此表上运行VACUUM ANALYZE。

我已经使用pgAdmin 3和Postgres 8.4.4进行了测试，并关闭了自动清理功能。每当我点击已更改的表时，提示框立即显示。

假设我正在用Java开发基于Web的系统，如何检测表是否过时，以便我可以显示像pgAdmin中那样的提示？

由于我的应用程序的性质，这里有一些规则必须遵循：

1. 我想知道pg_stats和pg_statistic中某个表的统计信息是否是最新的。

2. 我无法设置postgresql.conf中的autovacuum标志。(换句话说，autovacuum标志可以打开或关闭。我无法控制它。我需要知道是否有更新的统计数据，不管autovacuum标志是否打开.)

3. 我不能每次都运行vacuum/analyze来使其最新。

4. 当有任何对该表进行的更新（例如删除、插入和更新）未反映在pg_stats和pg_statistic中时，当用户选择该表时，我需要显示表已过时的提示。

通过分析pg_catalog.pg_stat_all_tables中的时间戳似乎不可行。当然，如果一个表之前没有被分析过，我可以检查last_analyze中是否有时间戳来判断该表是否是最新的。然而，使用这种方法，当已经存在时间戳时，我无法检测表是否是最新的。换句话说，无论我向表中添加多少行，在pg_stat_all_tables中它的last_analyze时间戳始终是第一次分析的时间（假设autovacuum标志关闭）。因此，我只能在第一次显示“建议运行VACUUM”提示。

通过比较last_analyze时间戳和当前时间戳也不可行。表可能几天内没有更新，也可能在一个小时内有大量更新。

在这种情况下，我该如何始终确定表的统计信息是否是最新的？

- Beibei

Answer 1

检查系统目录。

=> SELECT schemaname, relname, last_autoanalyze, last_analyze FROM pg_stat_all_tables WHERE relname = 'accounts';
schemaname | relname  |       last_autoanalyze        | last_analyze 
------------+----------+-------------------------------+--------------
public     | accounts | 2022-11-22 07:49:16.215009+00 | 
(1 row)

=>

https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ALL-TABLES-VIEW

这里有各种有用的信息:

test=# \d pg_stat_all_tables           View "pg_catalog.pg_stat_all_tables"
      Column       |           Type           | Modifiers 
-------------------+--------------------------+-----------
 relid             | oid                      | 
 schemaname        | name                     | 
 relname           | name                     | 
 seq_scan          | bigint                   | 
 seq_tup_read      | bigint                   | 
 idx_scan          | bigint                   | 
 idx_tup_fetch     | bigint                   | 
 n_tup_ins         | bigint                   | 
 n_tup_upd         | bigint                   | 
 n_tup_del         | bigint                   | 
 n_tup_hot_upd     | bigint                   | 
 n_live_tup        | bigint                   | 
 n_dead_tup        | bigint                   | 
 last_vacuum       | timestamp with time zone | 
 last_autovacuum   | timestamp with time zone | 
 last_analyze      | timestamp with time zone | 
 last_autoanalyze  | timestamp with time zone | 
 vacuum_count      | bigint                   | 
 autovacuum_count  | bigint                   | 
 analyze_count     | bigint                   | 
 autoanalyze_count | bigint                   |

- Sean

---

谢谢你的回答，Sean。我确实尝试了pg_stat_all_tables。在分析之前，我能够确定第一次过时的表格。但是我不知道如何告诉同一张表格有更多的更改。请查看我的更新问题。 - Beibei

好的，我已经找到了如何判断表的统计信息是否是最新的方法，即当表中添加或删除行时。诀窍是将视图“pg_catalog.pg_stat_all_tables”中的n_tup_ins（或n_live_tup）与表“pg_catalog.pg_class”中的reltuples进行比较。虽然这种方法无法检测行数保持不变时的更新，但它满足了我的问题。 - Beibei

仅记录后端的所有查询并查看使用pgAdmin连接时会发生什么。请注意来自其他发布者的上述评论，有关启用autovacuum功能的内容。除非您具有某些异常的需求，并且知道您正在尝试通过使用autovacuum来避免什么（很可能您不知道，也不应该避免autovacuum），否则应使用autovacuum运行。如果这不是您的决定，请向决策者提出此案例。 - Sean

Answer 2

您不需要在应用程序中担心vac'ing。 相反，您应该在服务器上配置autovac进程（在postgresql.conf中），并且服务器根据其自身的内部统计数据处理VACCUM和ANALYZE进程。 您可以配置它运行的频率以及它处理的阈值变量。

- atrain

---

嗨，Aaron，感谢您的回答。但由于我的应用程序的性质，我无法在postgresql.conf中设置autovacuum标志。 autovacuum标志可以打开或关闭。我对此没有控制权。 - Beibei

你能联系上你的数据库管理员吗？即使是托管应用程序，autovac守护进程也应该在运行，因为Postgres没有它会变得非常分散，特别是如果你正在执行大量删除操作。 - atrain