我进行了一些测试。这EXISTS事实证明变体要快得多 - 正如我所预期的那样，与@Tometzky 发布了什么 https://stackoverflow.com/a/10837954/939860.
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并取得了最好的成绩。一般来说，最好的与第一个或最差的几乎没有什么不同。
A bare SELECT（没有DELETE）显示了类似的结果。
1. CTE 与rank()
总运行时间：150.411 毫秒
总运行时间：149.853 ms——分析后
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()
总运行时间：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)
);
第二次运行的差异来自于切换到哈希半连接而不是额外的排序+合并半连接.
Results
-
EXISTS凭借最新的牌桌统计数据，显然获胜。
- 过时的统计数据
row_number()在子查询中是最快的。
-
rank()是最慢的变体。
- CTE 比子查询慢。
-
ANALYZE（更新的统计数据）有助于提高性能和can有很多帮助。自动真空 http://www.postgresql.org/docs/current/interactive/routine-vacuuming.html（默认）或多或少应该自动处理这个问题 - 除了临时表或在对表进行重大更改后立即进行。阅读更多here https://dba.stackexchange.com/questions/18664/are-regular-vacuum-analyze-stil-recommended-under-9-1 or here https://dba.stackexchange.com/questions/35327/should-i-manually-vacuum-my-postgresql-database-if-autovacuum-is-turned-on.
使用 100.000 行进行测试
我用 100.000 行和 63045 个重复项重复了测试。类似的结果，除了EXISTS速度较慢，即使在之后ANALYZE.
- 总运行时间：1648.601 毫秒
- 总运行时间：1623.759 毫秒
- 总运行时间：1568.893 毫秒
- 总运行时间：1692.249 毫秒
将统计目标提高到 1000，然后提高到最大 10000（现实生活中的杀伤力），然后再提高ANALYZE将所有查询速度加快了约 1%，但查询规划器仍然采用排序+合并半连接 for 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 毫秒
Update
曾经有过改进从那时起到查询规划器。直接去了哈希半连接使用 PostgreSQL 9.1.7 进行重新测试。
