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

Question

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

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 行的 (2,3) 为 b,c第 1 行没问题，因为它是第一行，第 2 行不是。

• 第 4、6、7 行的 (4,4) 为 b,c第 4 行可以，因为它是第一行，第 6,7 行不是。

然后我会:

DELETE FROM test WHERE a = those IDs;

.. 并添加唯一约束。

我正在考虑与自身相交的测试，但不确定从那里去哪里。

Answer 1

我进行了几次测试。 EXISTS 变体被证明要快得多 - 正如我预期的那样，与 what @Tometzky posted 相反.

在 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。使用 rank()

的 CTE

总运行时间: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。使用 row_number()

的 CTE

总运行时间: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)
    );

第二次运行的不同之处在于切换到Hash Semi Join而不是额外的排序 + 合并半连接。

结果

• EXISTS 凭借最新的表统计信息显然胜出。
• 对于过时的统计信息，子查询中的 row_number() 是最快的。
• rank() 是最慢的变体。
• CTE 比子查询慢。
• ANALYZE(更新的统计信息)有助于提高性能并且可以帮助很大。 Autovacuum (默认)应该或多或少地自动处理这个 - 除了临时表或在对表进行重大更改后立即。阅读更多 here或 here .

测试 100.000 行

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

1. 总运行时间:1648.601 毫秒
2. 总运行时间:1623.759 毫秒
3. 总运行时间:1568.893 毫秒
4. 总运行时间:1692.249 毫秒

将统计目标提高到 1000，然后提高到最大值 10000(在现实生活中过度杀伤)和另一个 ANALYZE 将所有查询加速了约 1%，但查询规划器仍然与 排序 + 合并半连接 EXISTS。

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

只有在我强制规划器避免合并连接后，规划器才使用Hash Semi Join再次花费一半的时间:

SET enable_mergejoin = off

1. 总运行时间:850.615 毫秒

更新

从那时起，查询规划器有了改进。在使用 PostgreSQL 9.1.7 重新测试时直接使用Hash Semi Join。