再次审视 PostgreSQL CTE 物化和非幂等子查询

几天前，我写了一篇关于 PostgreSQL 中 CTE、DELETE 和 LIMIT 的规划器行为的意外文章，这篇文章是我在公交车上匆匆整理的。那篇文章显然只是触及了一个更深层次问题的表面，而这个问题我后来花了太多时间去探索。所以，这里有一些更成熟的想法和发现。

重新审视核心问题

让我们快速回顾一下：当使用如下查询时，规划器可能会以你意想不到的方式执行查询。具体来说，如下查询：

WITH deleted_tasks AS (
  DELETE FROM task_queue
  WHERE id IN (
    SELECT id FROM task_queue
    WHERE queue_group_id = 15
    LIMIT 1
    FOR UPDATE SKIP LOCKED
  )
  RETURNING item_id
)
SELECT * FROM deleted_tasks;

…有时尽管限制为 1，但仍可以删除多行。

这首先让我感到惊讶，因为规划器可能会在单个LIMIT N命令的执行过程中执行包含多次的子查询。 DELETE

为什么会发生这种情况？

PostgreSQL 的查询规划器会尝试优化整体查询执行。在某些情况下，其基于成本的分析可能会导致其选择一个计划，其中子查询（包含 的部分LIMIT）在单个命令的上下文中被多次执行DELETE。如果子查询不能保证每次都返回相同的行（例如，由于LIMIT没有ORDER BY或 因为FOR UPDATE SKIP LOCKED），则每次执行都可能针对不同的行，从而导致删除的行数超出LIMIT预期。

然而，这其中还有更多的细微差别，所以让我们看看编写查询的不同方式如何影响执行计划和结果。

有问题的基准情况：IN 带有 LIMIT N

这是我们开始提出的查询，用于演示该问题。

-- Original problematic query
EXPLAIN ANALYZE
WITH deleted_tasks AS (
  DELETE FROM task_queue
  WHERE id IN (
    SELECT id FROM task_queue t2
    WHERE t2.queue_group_id = 5 -- Using group 5 from test data
    LIMIT 10                   -- Limit without ORDER BY
    FOR UPDATE SKIP LOCKED
  )
  RETURNING item_id
)
SELECT * FROM deleted_tasks;

-- Plan
Delete on task_queue ... (loops=1)
  ->  Nested Loop Semi Join ... (loops=1)
        ->  Seq Scan on task_queue ... (loops=1)
        ->  Subquery Scan on "ANY_subquery" ... (loops=14) -- <<< SUBQUERY INVOLVED IN MANY LOOPS
              ->  Limit ... (actual time=... rows=1 loops=14)  -- <<< LIMIT EXECUTED MANY TIMES
                    ->  Index Scan on task_queue t2 ...

行为：不正确。非确定性LIMIT 10子查询执行多次（示例计划中共执行了 14 次循环）。每次执行还可能选择不同的行，导致其DELETE可能影响远超 10 行的数据。

使用 ORDER BY 增加确定性

让我们通过添加来使子查询在行选择ORDER BY方面具有确定性，看看会发生什么：

EXPLAIN ANALYZE
WITH deleted_tasks AS (
  DELETE FROM task_queue
  WHERE id IN (
    SELECT id FROM task_queue t2
    WHERE t2.queue_group_id = 5
    ORDER BY id -- <<< Added ORDER BY
    LIMIT 10
    FOR UPDATE SKIP LOCKED
  )
  RETURNING item_id
)
SELECT * FROM deleted_tasks;

-- Plan
Delete on task_queue ...
  ->  Nested Loop Semi Join ... (loops=1)
        ->  Seq Scan on task_queue ... (loops=1)
        ->  Subquery Scan on "ANY_subquery" ... (loops=14) -- <<< STILL MIGHT BE EXECUTED MULTIPLE TIMES
              ->  Limit ... (actual time=... rows=10 loops=14)

行为：仍然可能不正确。虽然在没有跳过任何行的情况下，添加ORDER BY id可以使行的选择具有确定性，但与的组合意味着子查询结果在每次执行时仍可能发生变化。如果规划器多次执行子查询（如所示），它可能会在每次执行中锁定并跳过不同的行。这种组合仍然可能导致删除比指定的行更多的行，因为由于锁定的副作用，子查询返回的集合在执行过程中并不稳定。它仅保证在可用的情况下首先选择哪些行，而不是在所有潜在执行中只删除一行。FOR UPDATE SKIP LOCKEDloops=14LIMITidLIMIT

使用 ORDER BY（无锁）实现更高的确定性

如果我们删除FOR UPDATE SKIP LOCKED但保留会怎么样ORDER BY？这能让子查询真正具有确定性吗？

EXPLAIN ANALYZE
WITH deleted_tasks AS (
  DELETE FROM task_queue
  WHERE id IN (
    SELECT id FROM task_queue t2
    WHERE t2.queue_group_id = 5
    ORDER BY id -- <<< Keep ORDER BY
    LIMIT 10
    -- <<< No FOR UPDATE SKIP LOCKED
  )
  RETURNING item_id
)
SELECT * FROM deleted_tasks;

-- Plan
Delete on task_queue ... (loops=1)
  ->  Nested Loop Semi Join ... (loops=1)
        ->  Seq Scan on task_queue ... (loops=1)
        ->  Subquery Scan on "ANY_subquery" ... (loops=12) -- <<< STILL EXECUTED MULTIPLE TIMES
              ->  Limit ... (actual time=... rows=10 loops=12)
                    ->  Sort ... (actual time=... rows=... loops=12)
                          ->  Index Scan on task_queue t2 ...

行为：正确，但可能效率低下。删除SKIP LOCKED和添加操作ORDER BY id会使子查询完全幂等——它将始终返回完全相同的 10 个元素的集合。由于sid集合是稳定的，因此只会正确地影响这特定的 10 行，同时遵循。但是，正如示例计划所示（），规划器仍然不会被强制只执行一次子查询。它可能会选择一个重复执行幂等子查询的计划。这不会影响正确性，但与强制单次执行相比，会导致冗余工作和潜在的效率低下。idDELETELIMITloops=12

强制单次评估：使用等号运算符 LIMIT 1

在具体处理的时候LIMIT 1，使用=给规划者提供了强有力的提示。

-- Using = for LIMIT 1
EXPLAIN ANALYZE
DELETE FROM task_queue
WHERE id = ( -- <<< Using =
  SELECT id FROM task_queue
  WHERE queue_group_id = 5
  ORDER BY id -- Keep ORDER BY for determinism
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
RETURNING item_id;

-- Plan
Delete on task_queue (cost=0.57..8.59 ...)
  ->  Nested Loop (cost=0.57..8.59 ...)
        ->  Result (cost=0.00..0.01 ...)
              InitPlan 1 (returns $0) -- <<< SUBQUERY EVALUATED ONCE HERE
                ->  Limit (cost=0.29..0.43 ... loops=1) -- <<< loops=1
                      ->  Index Scan ... (queue_group_id = 5)
        ->  Index Scan using task_queue_pkey on task_queue (cost=0.28..8.15 ...)
              Index Cond: (id = $0)

行为：正确且保证单次执行。对于涉及 CTE LIMIT N（尤其是在 N > 1 的情况下）的情况，推荐采用此方法，因为正确性和可预测的行为至关重要。该关键字明确指示 PostgreSQL仅执行一次MATERIALIZEDCTE 查询（rows_to_delete），并临时存储其结果。然后，主查询将对这组固定的结果进行操作（通过）。这完全避免了之前提到的子查询重新求值问题，确保了正确性（在单次执行中准确识别行，并遵守 和），以及可预测的性能。DELETECTE ScanLIMIT NORDER BYSKIP LOCKED

关键要点

理解 CTE 的物化和子查询求值，让我对 PostgreSQL 查询规划有了更深入的了解。以下是一些我总结的要点：

• CTE 子查询中的幂等性和确定性至关重要，尤其是在不使用的情况下MATERIALIZED。如果没有它们，规划器多次执行可能会导致错误的结果或意想不到的副作用，就像我们看到的那样。
• 一个好的经验法则是，始终LIMIT与配对ORDER BY以确保确定性的行选择。虽然这不能保证非物化 CTE 的单次执行，但它可以避免出现不确定的结果。
• EXPLAIN ANALYZE这对于发现异常行为非常有用。loops > 1在 DML 操作中查找子查询/CTE 节点，看看是否发生了重新求值。
• 考虑一下在 CTE 中直接使用 DML（如DELETE或），尤其是受这些重新评估风险影响的 DML，是否是适合您的用例的最清晰或最安全的方法。UPDATE
  最后，这一切都是一段充满反复尝试和深入研究 PostgreSQL 代码的旅程。如果您发现我遗漏了什么，我很乐意与您分享。

原文地址：https://www.shayon.dev/post/2025/124/another-look-into-postgresql-cte-materialization-and-non-idempotent-subqueries/
原文作者：Shayon Mukherjee