使用 CTE 删除比在 Postgres 中使用临时表慢

Question

我想知道是否有人可以解释为什么使用 CTE 而不是临时表运行的时间更长......我基本上是从客户表中删除重复信息（为什么存在重复信息超出了本文的范围）。

这是Postgres 9.5。

CTE 版本是这样的：

with targets as
    (
        select
            id,
            row_number() over(partition by uuid order by created_date desc) as rn
        from
            customer
    )
delete from
    customer
where
    id in
        (
            select
                id
            from
                targets
            where
                rn > 1
        );

今天早上，我在跑了一个多小时后杀死了那个版本。

临时表版本是这样的：

create temp table
    targets
as select
    id,
    row_number() over(partition by uuid order by created_date desc) as rn
from
    customer;
delete from
    customer
where
    id in
        (
            select
                id
            from
                targets
            where
                rn > 1
        );

此版本在大约 7 秒内完成。

知道是什么原因造成的吗？

Answer 1

CTE 较慢，因为它必须原封不动地执行（通过 CTE 扫描）。

全聚焦方式（TFM）（第7.8.2节）指出： WITH 中的数据修改语句只执行一次，并且始终执行完成，与主查询是否读取其所有（或任何）输出无关。请注意，这与 WITH 中的 SELECT 规则不同：如上一节所述，SELECT 的执行仅在主查询需要其输出时才执行。

因此，这是一个优化障碍;对于优化者来说，不允许拆除CTE，即使它会导致具有相同结果的更智能计划。

不过，CTE 解决方案可以重构为联接子查询（类似于问题中的临时表）。如今，在postgres中，连接的子查询通常比EXISTS（）变体更快。

DELETE FROM customer del
USING ( SELECT id
        , row_number() over(partition by uuid order by created_date desc)
                 as rn
        FROM customer
        ) sub
WHERE sub.id = del.id
AND sub.rn > 1
        ;

---

另一种方法是使用 TEMP VIEW .这在语法上等效于temp table情况，但在语义上等效于连接的子查询表单（它们产生完全相同的查询计划，至少在这种情况下）。这是因为 Postgres 的优化器会分解视图并将其与主查询（上拉）相结合。您可以将view视为PG中的一种宏。

CREATE TEMP VIEW targets
AS SELECT id
        , row_number() over(partition by uuid ORDER BY created_date DESC) AS rn
FROM customer;
EXPLAIN
DELETE FROM customer
WHERE id IN ( SELECT id
            FROM targets
            WHERE rn > 1
        );

---

[更新：我错了 CTE 需要始终执行完成，这只是数据修改 CTE 的情况]

Answer 2

使用

CTE 可能会导致与使用临时表不同的瓶颈。我不熟悉PostgreSQL如何实现CTE，但它很可能在内存中，所以如果你的服务器内存不足并且你的CTE的结果集非常大，那么你可能会在那里遇到问题。我会在运行查询时监视服务器，并尝试找到瓶颈所在。

执行删除的另一种方法可能比两种方法都快：

DELETE C
FROM
    Customer C
WHERE
    EXISTS (SELECT * FROM Customer C2 WHERE C2.uuid = C.uuid AND C2.created_date > C.created_date)

这不会处理与created_date完全匹配的情况，但这也可以通过将id添加到子查询来解决。