本文介绍了在Postgres中使用CTE删除比使用TEMP表慢的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我想知道有没有人能解释一下为什么使用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表慢的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

11-01 23:11