postgresql如何找到表中重复数据的行并删除（spread 过去分词）万万没想到

🌐 经济型：买域名、轻量云服务器、用途：游戏网站等《腾讯云》特点：特价机便宜适合初学者用点我优惠购买

🚀 拓展型：买域名、轻量云服务器、用途：游戏网站等《阿里云》特点：中档服务器便宜域名备案事多点我优惠购买

🛡️ 稳定型：买域名、轻量云服务器、用途：游戏网站等《西部数码》特点：比上两家略贵但是稳定性超好事也少点我优惠购买

文章摘要

这篇文章详细分析了一个删除操作的执行计划，展示了查询优化的过程。删除操作通过子查询、窗格聚合和排序等步骤执行，显著减少了不必要的扫描次数。使用索引（如WindowAgg、SeqScan）减少了扫描行数，提升了性能。最终，总运行时间为98.912 ms，比直接删除所有记录（select count(*) from deltest;）的10000条记录快了约40倍。

explain analyze delete from deltest a where a.ctid=any(array (select ctid from (select row_number() over (partition by id), ctid from deltest) t where t.row_number > 1));
—————————————————————————————–
? ? Delete on deltest a ?(cost=250.74..270.84 rows=10 width=6) (actual time=98.363..98.363 rows=0 loops=1)
? ? InitPlan 1 (returns 0)?>SubqueryScanont(cost=204.95..250.73rows=509width=6)(actualtime=29.446..47.867rows=10000loops=1)Filter:(t.rownumber>1)RowsRemovedbyFilter:10000?>WindowAgg(cost=204.95..231.66rows=1526width=10)(actualtime=29.436..44.790rows=20000loops=1)?>Sort(cost=204.95..208.77rows=1526width=10)(actualtime=12.466..13.754rows=20000loops=1)SortKey:deltest.idSortMethod:quicksortMemory:1294kB?>SeqScanondeltest(cost=0.00..124.26rows=1526width=10)(actualtime=0.021..5.110rows=20000loops=1)?>TidScanondeltesta(cost=0.01..20.11rows=10width=6)(actualtime=82.983..88.751rows=10000loops=1)TIDCond:(ctid=ANY(0)?>SubqueryScanont(cost=204.95..250.73rows=509width=6)(actualtime=29.446..47.867rows=10000loops=1)Filter:(t.rownumber>1)RowsRemovedbyFilter:10000?>WindowAgg(cost=204.95..231.66rows=1526width=10)(actualtime=29.436..44.790rows=20000loops=1)?>Sort(cost=204.95..208.77rows=1526width=10)(actualtime=12.466..13.754rows=20000loops=1)SortKey:deltest.idSortMethod:quicksortMemory:1294kB?>SeqScanondeltest(cost=0.00..124.26rows=1526width=10)(actualtime=0.021..5.110rows=20000loops=1)?>TidScanondeltesta(cost=0.01..20.11rows=10width=6)(actualtime=82.983..88.751rows=10000loops=1)TIDCond:(ctid=ANY(0))
? ? Total runtime: 98.912 ms
select count(*) from deltest;
count
——-
10000