On Friday 08 July 2011 10:05:47 Dean Rasheed wrote: > > 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. That's very interesting, and a more plausible not-optimized-yet item than my guesses so far, thanks. Drop me a mail if you work on this, and I'll find some time to test your code. I'm wondering though : this sounds like the behaviour of a "deferrable" fkey, which AFAICS is not the default and not my case ? I haven't explored that area of constraints yet, so there's certainly some detail that I'm missing. > 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); Yes, that's what we ended up doing. We canceled the query after 24h, shortly before the OOM killer would have, and started doing things in smaller batches. -- Vincent de Phily -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
