在唯一约束之前清理SQL数据

一尘不染

在唯一约束之前清理SQL数据

sql

我想在对两列施加唯一约束之前清理表中的某些数据。

CREATE TABLE test (
 a integer NOT NULL,
 b integer NOT NULL,
 c integer NOT NULL,
 CONSTRAINT a_pk PRIMARY KEY (a)
);

INSERT INTO test (a,b,c) VALUES
 (1,2,3)
,(2,2,3)
,(3,4,3)
,(4,4,4)
,(5,4,5)
,(6,4,4)
,(7,4,4);

-- SELECT a FROM test WHERE ????

输出应为2,6,7

我正在寻找重复 的第一 行 之后的 所有行b,c

前任：

第1,2行的（b，c）为（2,3）。第1行是正确的，因为它是第一个，第2行不是。
行4,6,7的（b，c）为（4,4），行4可以，因为它是第一个，而6,7则不是。

然后，我将：

DELETE FROM test WHERE a = those IDs;

..并添加唯一约束。

我当时正在考虑与自身进行测试的交集，但不确定从哪里开始。

阅读 146

2021-05-23

共1个答案

一尘不染

我进行了几次测试。EXISTS事实证明，该变体要快得多-
正如我预期的那样，与@Tometzky发布的相反。

使用窗口函数应该比这个答案快得多：

select a
from (
  select a, rank() over (partition by b, c order by a) as rank
  from test ) as _
where rank>1;

在PostgreSQL 9.1.2上以适当的设置测试10.000行的平台：

CREATE TEMP TABLE test (
  a serial
 ,b int NOT NULL
 ,c int NOT NULL
);

INSERT INTO test (b,c)
SELECT (random()* 100)::int AS b, (random()* 100)::int AS c
FROM   generate_series(1, 10000);

ALTER TABLE test ADD CONSTRAINT a_pk PRIMARY KEY (a);

在第一轮和第二轮测试之间，我进行了：

ANALYZE test;

当我最终应用DELETE时，删除了3368个重复项。如果您重复的次数太多或更少，性能可能会有所不同。

我与每个查询一起运行了几次，EXPLAIN ANALYZE并取得了最佳结果。通常，最佳与第一或最差没有什么不同。
裸露SELECT（不带DELETE）显示相似的结果。

1. CTE与 `rank()`

总运行时间：150.411毫秒
总运行时间：149.853毫秒-在分析之后

WITH x AS (
    SELECT a
          ,rank() OVER (PARTITION BY b, c ORDER BY a) AS rk
    FROM   test
    )
DELETE FROM test
USING  x
WHERE  x.a = test.a
AND    rk > 1;

2. CTE与 `row_number()`

总运行时间：148.240毫秒
总运行时间：147.711毫秒-进行分析后

WITH x AS (
    SELECT a
          ,row_number() OVER (PARTITION BY b, c ORDER BY a) AS rn
    FROM   test
    )
DELETE FROM test
USING  x
WHERE  x.a = test.a
AND    rn > 1;

3.`row_number()`在子查询中

总运行时间：134.753毫秒
总运行时间：134.298毫秒-在分析之后

DELETE FROM test
USING (
    SELECT a
          ,row_number() OVER (PARTITION BY b, c ORDER BY a) AS rn
    FROM   test
    )  x
WHERE  x.a = test.a
AND    rn > 1;

4.`EXISTS`半联接

总运行时间：143.777毫秒
总运行时间： 69.072毫秒 -进行分析后

DELETE FROM test t
WHERE EXISTS (
    SELECT 1
    FROM   test t1
    WHERE  t1.a < t.a
    AND   (t1.b, t1.c) = (t.b, t.c)
    );

第二轮的区别在于切换到 哈希半联接， 而不是附加的“ 排序+合并半联接” 。

结果

EXISTS 凭借最新的tp-date表统计信息显然可以胜出。
row_number()在子查询中使用过时的统计信息是最快的。
rank() 是最慢的变体。
CTE比子查询慢。
ANALYZE（更新的统计信息）有助于提高性能，并且会有所帮助。Autovacuum（默认值）应或多或少自动处理此问题-临时表或在对该表进行重大更改之后立即除外。

用100.000行进行测试

我用100.000行和63045重复重复了测试。相似的结果，只是EXISTS速度较慢，即使在以后也是如此ANALYZE。

总运行时间：1648.601毫秒
总运行时间：1623.759毫秒
总运行时间：1568.893 ms
总运行时间：1692.249毫秒

将统计目标提高到1000，然后提高到最大10000（实际直播中的过度杀伤），另一个目标ANALYZE将所有查询的速度提高了约1％，但查询计划者仍选择使用
Sort + Merge Semi Join 作为EXISTS。

ALTER TABLE test ALTER COLUMN b SET STATISTICS 10000;
ALTER TABLE test ALTER COLUMN c SET STATISTICS 10000;
ANALYZE test;

只有在我强迫计划程序避免合并联接之后，计划程序才使用 哈希半联接 再次花费了一半的时间：

SET enable_mergejoin = off

总运行时间：850.615毫秒

更新

从那时起，对查询计划器进行了改进。直接进入 Hash Semi Join 对PostgreSQL 9.1.7进行重新测试。

2021-05-23