一个postgresql查询无法完成。

Question

在PostgreSQL 9.0中，我们有一个SQL查询：

SELECT count(*) FROM lane 
WHERE not exists 
    (SELECT 1 FROM id_map 
    WHERE id_map.new_id=lane.lane_id  
    and id_map.column_name='lane_id' 
    and id_map.table_name='lane') 
and lane.lane_id is not null;

这个查询通常需要大约1.5秒左右才能完成。 以下是执行计划：http://explain.depesz.com/s/axNN

然而有时候，这个查询会挂起并且无法完成。它甚至可能持续运行11个小时而没有任何结果。 同时，它会占用100%的CPU。

这个查询所占用的唯一锁是 "AccessShareLock"，并且它们都已被授予。

SELECT a.datname,
         c.relname,
         l.transactionid,
         l.mode,
         l.granted,
         a.usename,
         a.current_query, 
         a.query_start,
         age(now(), a.query_start) AS "age", 
         a.procpid 
    FROM  pg_stat_activity a
     JOIN pg_locks         l ON l.pid = a.procpid
     JOIN pg_class         c ON c.oid = l.relation
    ORDER BY a.query_start;

该查询作为Java进程的一部分运行，使用连接池连接到数据库，并依次执行类似于以下格式的选择查询：

SELECT count(*) FROM {}  WHERE not exists (SELECT 1 FROM id_map WHERE id_map.new_id={}.{} and id_map.column_name='{}' and id_map.table_name='{}') and {}.{} is not null

在这个过程中并没有并行更新或删除操作，因此我不认为吸尘是问题所在。在运行整个过程（运行6个这种查询之前），对所有表格进行了分析。

由于长时间运行的查询从未完成，因此postgres日志中没有任何条目记录它们。

有什么想法可以导致这种行为，以及如何防止它发生？

没有分析计划的解释：

Aggregate  (cost=874337.91..874337.92 rows=1 width=0)
  ->  Nested Loop Anti Join  (cost=0.00..870424.70 rows=1565283 width=0)
        Join Filter: (id_map.new_id = lane.lane_id)
        ->  Seq Scan on lane  (cost=0.00..30281.84 rows=1565284 width=8)
              Filter: (lane_id IS NOT NULL)
        ->  Materialize  (cost=0.00..816663.60 rows=1 width=8)
              ->  Seq Scan on id_map  (cost=0.00..816663.60 rows=1 width=8)
                    Filter: (((column_name)::text = 'lane_id'::text) AND ((table_name)::text = 'lane'::text))

- norbitheeviljester

---

我不熟悉任何检查正在运行的SQL查询执行计划的方法。我在帖子中附上了SQL查询的执行计划。您知道如何访问正在运行的查询的实际计划吗？ - norbitheeviljester

除非有一些高级扩展程序，否则你无法这样做...我要求的是解释选择...（跳过分析）以查看预期计划。 - Vao Tsun

执行计划在这里：http://explain.depesz.com/s/axNN，问题中还附加了未经分析的执行计划。 - norbitheeviljester

啊哈 - 所以它确实切换到了嵌套循环。现在尝试将缓存设置为更高的值，并且更重要的是，执行vacuum analyze verbose; - Vao Tsun

很遗憾，我无法在不发出工单的情况下更改我们开发机器的配置。至于这个"vacuum" - 这是一个仅执行插入和选择操作的表格。里面绝对没有死元组。我看不到任何需要进行清理的理由？ - norbitheeviljester

Answer 1

VACUUM ANALYZE VERBOSE;

刷新统计数据有助于数据库选择最佳执行计划，而不是使用我认为会占用100% CPU的嵌套循环。

- Vao Tsun

---

我成功验证了这确实是问题的根本原因。嵌套循环会创建150万个id_map表的序列扫描，需要数天才能完成。在运行查询之前对两个表进行vacuum analyze，查询将在不到2秒钟内完成。 - norbitheeviljester

Answer 2

这个问题可能是由以下原因引起的（据我所了解）：

• Postgres已经用完了可用的事务ID数量（当所有20亿个可用的事务ID都被使用后，事务ID会重新从1开始，这会导致环绕问题，可能会导致严重的数据丢失或数据库关闭）
• 数据库过于分段，即DELETE或UPDATE（由Postgres转换为INSERT + DELETE）命令将元组标记为已删除，但不会物理删除它。

如果您有像GCloud这样的云服务器，可以在数据库标志上设置一些变量，以使VACUUM自动调用并清除标记为已删除但仍在您的数据库中的元组，并且ANALYZE收集有关执行计划中使用的频繁更新表的最新统计信息。例如：

autovacuum: on
autovacuum_analyze_scale_factor: 0.05
autovacuum_analyze_threshold: 10
autovacuum_naptime: 15
autovacuum_vacuum_cost_delay: 10
autovacuum_vacuum_cost_limit: 1000
autovacuum_vacuum_scale_factor: 0.1
autovacuum_vacuum_threshold: 25
log_autovacuum_min_duration: 0
track_counts: on

源代码：

https://www.postgresql.org/docs/9.5/runtime-config-autovacuum.html https://www.techonthenet.com/postgresql/autovacuum.php https://aws.amazon.com/premiumsupport/knowledge-center/transaction-id-wraparound-effects/

- Roni Castro