在Postgres中使用CTE删除比使用TEMP表慢 [英] Delete using CTE slower than using temp table in Postgres

问题描述

我想知道有没有人能解释一下为什么使用CTE而不是临时表会运行这么长的时间……我基本上是从Customer表中删除重复信息(为什么存在重复信息不在本文的讨论范围内)。

这是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秒内完成。

知道这可能是什么原因吗？

推荐答案

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

TFM(7.8.2节)规定： WITH中的数据修改语句只执行一次，并且始终完成，与主查询是否读取其所有(或实际上是任何)输出无关，。 请注意，这与SELECT IN WITH的规则不同：如上一节所述，只有在主查询需要其输出时才执行SELECT。

因此，这是一个优化障碍；对于优化器来说，不允许拆卸CTE，即使它会产生一个更聪明的计划，但结果相同。

CTE解决方案可以重构为连接子查询(类似于问题中的临时表)。如今，在Postgres中，连接子查询通常比Existes()变量更快。

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 tablecase，但在语义上等同于联接的子查询形式(它们产生的完全相同的查询计划，至少在本例中是这样)。这是因为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]

这篇关于在Postgres中使用CTE删除比使用TEMP表慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！