django - Postgres EXPLAIN ANALYZE 成本估算行数大大高于实际行数。没有吸尘？

Question

我有一个在 Django 项目中的 Heroku 上运行的 Postgres 9.4.18 数据库。我注意到查询变得越来越慢，所以我对一个查询运行了“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 中找到了这个命令，它提供了 autovacuum 统计信息，这是输出(不幸的是，我在手动清理后运行了它。

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 页面，其中包含关于 vacuuming 设置的文档: https://devcenter.heroku.com/articles/managing-vacuum-on-heroku-postgres

Answer 1

要查明 autovacuum 是否按应有的方式启用，请运行

SHOW autovacuum;

要查明你的特定表是否禁用了 autovacuum，请运行

SELECT reloptions FROM pg_class WHERE relname = 'listings_listing';

B)的答案很简单:

如果 autovacuum 没有运行，每个 UPDATE 或 DELETE 都会在表中创建一个“死元组”(或“死行版本”)。这些将永远不会被清理，除非您手动运行 VACUUM 并且会导致表增长，从而使顺序扫描变慢。

A) 的答案更难:

有几件事可以阻止 autovacuum 完成其工作:

• 此表的更改率可能如此之高，以至于默认运行缓慢的 autovacuum 无法跟上，以免影响正常事件。

  在这种情况下，您应该将 autovacuum 调整为对该表更具侵略性:

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

  如果这还不够好，你可以

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

• 存在并发的长事务。

  Autovacuum 只能删除比最旧的运行事务更早的死元组，因此长事务会阻止它完成工作。

  故事还有很多；阅读 this blog post .

  但是，这也会使 VACUUM (FULL) 无法正常工作，所以这可能不是您的问题。

• 该表经常被SHARE UPDATE EXCLUSIVE 或更强的锁锁定，例如通过运行“LOCK listings_listing”。

  当 autovacuum 遇到这样的锁时，它会退却而不是阻止用户事件。

确定发生了什么的有用方法是像这样查询 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 以外的值，并偶尔查看日志。