postgresql数据去除重复数据行出现内存用尽错误

喜之郎 发布于 2015/04/03 09:13
阅读 761
收藏 0

现需要对数据单表去除重复行。在table_name中qq,nickname,type三个字段都相同的情况下才认为是相同的数据行,去重时只保留相同行中id最小的那条。在数据量在百万级的情况下使用以下sql语句可以实现去重: 

DELETE FROM table_name WHERE id in ((select id from table_name) except (SELECT min(id) FROM table_name GROUP BY qq,nickname,type));

当数据到千万级时数据出现异常:
错误:内存用尽
详细:分配内存1008大小失败
因此当时想配置一下postgresql内存占用大小,但由于postgresql安装在windows上,所以那个shared_buffer调到大于约1.5g时就无法启动。百度一下,网上说windows操作系统限制了一个程序占用的最大内存。

因为换了个思路,想办法让去重时占用内存减小,即以下语句: 

WITH temp AS (
SELECT id,qq,nickname,TYPE FROM table_name WHERE (qq,nickname,TYPE) IN(
SELECT qq,nickname,TYPE FROM table_name GROUP BY qq,nickname,TYPE
HAVING COUNT(*)>1
)

DELETE FROM table_name WHERE id IN ((SELECT id FROM temp) except (SELECT MIN(id) FROM temp GROUP BY qq,nickname,TYPE));

思路是先在整个表找到那些有重复行的数据,然后再对这个子集处理。
现在随着数据量的增大,又出现这个内存用尽的问题。不知道如何解决。请高手们赐教。
@宏哥 @mark35 @kenyon_君羊 @postgresql高手
加载中
1
mark35
mark35
试试内层查询使用LIMIT限制输出条数,然后多次执行看看
喜之郎
喜之郎
回复 @mark35 : linux下有没有这个限制?
喜之郎
喜之郎
回复 @mark35 : 是64位的,有8G内存。
mark35
mark35
回复 @喜之郎 : 你应该是32bit的windows,应用程序(可申请的)空间有限制,应该是2G左右。
喜之郎
喜之郎
经测试此办法可行。
0
huan
huan

这种大数据量的操作最好分步进行,因为没有更多信息,这里只能给你几个建议:

1 在去重字段上建立联合索引,查找要保留的行最好能走索引。

2 建立一些临时表,保存要删除或要保留的行id,然后再做下一步操作。

3 建议用插入代替删除,将要保留的行插入到一个新表,然后替换旧表。delete效率很低。

0
宏哥
宏哥

引用来自“huan”的评论

这种大数据量的操作最好分步进行,因为没有更多信息,这里只能给你几个建议:

1 在去重字段上建立联合索引,查找要保留的行最好能走索引。

2 建立一些临时表,保存要删除或要保留的行id,然后再做下一步操作。

3 建议用插入代替删除,将要保留的行插入到一个新表,然后替换旧表。delete效率很低。

返回顶部
顶部