> On Thu, 2011-07-07 at 15:34 +0200, vincent dephily wrote: >> Hi, >> >> I have a delete query taking 7.2G of ram (and counting) but I do not >> understant why so much memory is necessary. The server has 12G, and >> I'm afraid it'll go into swap. Using postgres 8.3.14. >> >> I'm purging some old data from table t1, which should cascade-delete >> referencing rows in t2. Here's an anonymized rundown : >> >> # explain delete from t1 where t1id in (select t1id from t2 where >> foo=0 and bar < '20101101'); It looks as though you're hitting one of the known issues with PostgreSQL and FKs. The FK constraint checks and CASCADE actions are implemented using AFTER triggers, which are queued up during the query to be executed at the end. For very large queries, this queue of pending triggers can become very large, using up all available memory. There's a TODO item to try to fix this for a future version of PostgreSQL (maybe I'll have another go at it for 9.2), but at the moment all versions of PostgreSQL suffer from this problem. The simplest work-around for you might be to break your deletes up into smaller chunks, say 100k or 1M rows at a time, eg: delete from t1 where t1id in (select t1id from t2 where foo=0 and bar < '20101101' limit 100000); Regards, Dean -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
