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

文章摘要

这篇文章详细描述了从deltest表中进行删除操作的执行计划和性能分析。主要操作包括从deltest表中选择具有row_number大于1的行，并通过多个子查询和扫描操作进行过滤和排序。最终删除了10000行数据，总运行时间为98.912毫秒，且从deltest表中选择计数结果为10000。文章重点突出了删除操作的步骤、执行时间和资源消耗情况。

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