Postgres EXPLAIN ANALYZE的成本估计行数远高于实际行数。没有进行清理吗？

Question

我在一个Django项目中运行了一个Postgres 9.4.18数据库，托管在Heroku上。我注意到查询变得越来越慢，因此我对其中一个查询运行了"EXPLAIN ANALYZE"，发现其中一个节点的行估计值比实际行数要高得多：

->  Seq Scan on listings_listing u1  (cost=0.00..1536692.01 rows=5030003 width=8) (actual time=0.811..11263.410 rows=173537 loops=1)

我随后在表上运行了"VACUUM FULL ANALYZE"命令，然后重新运行了查询的"EXPLAIN ANALYZE"命令，得到如下结果:

->  Seq Scan on listings_listing u1  (cost=0.00..23554.61 rows=173537 width=8) (actual time=0.001..33.884 rows=173537 loops=1)

执行时间现在快了100倍。

所以两个问题是：A）自动清理不应该防止这种情况吗？（如何检查是否启用？）B）如果没有执行清理，它是如何变成这样的？

--------------------------------- 更新

我在Heroku找到了这个命令，可以提供自动清理统计信息，以下是输出结果（不幸的是，我在手动清理后才运行它）。

heroku pg:vacuum_stats DATABASE_URL

schema |                  table                  | last_vacuum | last_autovacuum  |    rowcount    | dead_rowcount  | autovacuum_threshold | expect_autovacuum 
--------+-----------------------------------------+-------------+------------------+----------------+----------------+----------------------+-------------------
 public | listings_listing                        |             | 2018-06-27 15:36 |        173,537 |              0 |         34,757       | 

看起来的阈值应该早就使它运行了。

此外，这里是Heroku页面上有关清理设置的文档： https://devcenter.heroku.com/articles/managing-vacuum-on-heroku-postgres

- jeffjv

Answer 1

要确定是否已启用自动清理程序，请运行以下命令：

SHOW autovacuum;

要查看特定表是否禁用了自动清理，请运行：

SELECT reloptions FROM pg_class WHERE relname = 'listings_listing';

答案B很简单：如果自动清理未运行，则每次UPDATE或DELETE都会在表中创建一个“死元组”（或“死行版本”）。除非您手动运行VACUUM，否则这些将永远不会被清理，并导致表变大，从而使顺序扫描变慢。

答案A更加困难：有几件事情可能会阻止自动清理工作：

• The rate of changes to this table can be so high that autovacuum, which runs slow be default so that it does not disturb normal activity, cannot keep up.

  In this case you should tune autovacuum to be more aggressive for this table:

  ALTER TABLE listings_listing SET (
     autovacuum_vacuum_cost_limit = 1000,
     toast.autovacuum_vacuum_cost_limit = 1000
  );
  

  If that is not good enough, you can

  ALTER TABLE listings_listing SET (
     autovacuum_vacuum_cost_delay = 0,
     toast.autovacuum_vacuum_cost_delay = 0
  );
  

• There are concurrent long transactions.

  Autovacuum can only remove dead tuples that are older than the oldest running transaction, so long transactions can keep it from doing its job.

  There is more to the story; read this blog post.

  However, this would also keep VACUUM (FULL) from doing its job, so maybe that is not your problem.

• The table is frequently locked with SHARE UPDATE EXCLUSIVE or stronger locks, for example by running “LOCK listings_listing”.

  When autovacuum encounters such a lock, it backs down rather than blocking user activity.

确定正在发生的情况的有用方法是像这样查询pg_stat_user_tables：

SELECT n_live_tup, n_dead_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'listings_listing';

但是现在你已经运行了VACUUM (FULL)，这些证据可能已经被销毁了。

另外一个好的做法是将log_autovacuum_min_duration设置为除-1之外的其他值，并偶尔查看日志。

- Laurenz Albe

---

谢谢。运行您的命令显示autovacuum已开启，并且未禁用此表。pg_stat_user_tables显示last_autovacuum是在2018-06-27完成的。我还运行了一个Heroku命令，显示autovacuum_threshold为34,757行。所以我不明白为什么它还没有运行。或者这与autovacuum_vacuum_cost_delay不同吗？我不会对此表执行长时间锁定。 - jeffjv

也许只是批量删除了一些数据。监控表膨胀情况，看看会发生什么。 - Laurenz Albe

我每15分钟运行一次任务来删除过期的列表，因此批量删除的可能性很小。我将尝试监控膨胀情况。更改cost_delay的想法是可能正在尝试进行清理，但它一直处于睡眠状态吗？我的服务并不那么受欢迎，所以我很难相信这一点。 - jeffjv

自动清理默认速度较慢，而且会定期休息。除非你确定有必要，否则不要进行调整。 - Laurenz Albe

Answer 2

Laurenz Albe的回答很好地解释了自动清理的问题，但我现在想回答一下我后来发现的关于为什么我的死元组计数会膨胀的原因。

基本上由于我的代码中有一个bug，我每15分钟更新一次数据库中的每一行而不只是匹配过滤器的那些行。每次更新都会创建一个死元组，它的膨胀速度非常快，以至于清理程序不能跟上。花了一段时间才找到这个错误，因为我只看代码中的删除操作，没有意识到（当时）更新操作也会创建死元组。

修复后就无需更改任何自动清理设置了。增长是正常的。

- jeffjv