Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id 97CEEB5DBF8
	for <pgsql-general-postgresql.org@mail.postgresql.org>;
	Fri,  8 Jul 2011 06:44:39 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 86829-02; Fri,  8 Jul 2011 09:44:33 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail-wy0-f174.google.com (mail-wy0-f174.google.com
	[74.125.82.174])
	by mail.postgresql.org (Postfix) with ESMTP id AA6B4B5DBF6;
	Fri,  8 Jul 2011 06:44:32 -0300 (ADT)
Received: by wyg8 with SMTP id 8so1183944wyg.19
	for <multiple recipients>; Fri, 08 Jul 2011 02:44:32 -0700 (PDT)
Received: by 10.227.162.200 with SMTP id w8mr1580008wbx.114.1310118272068;
	Fri, 08 Jul 2011 02:44:32 -0700 (PDT)
Received: from moltowork.localnet ([86.47.169.233])
	by mx.google.com with ESMTPS id gg16sm7551280wbb.0.2011.07.08.02.44.30
	(version=SSLv3 cipher=OTHER); Fri, 08 Jul 2011 02:44:31 -0700 (PDT)
From: Vincent de Phily <vincent.dephily@mobile-devices.fr>
To: Dean Rasheed <dean.a.rasheed@gmail.com>
Cc: Guillaume Lelarge <guillaume@lelarge.info>, pgsql-general@postgresql.org,
	pgsql-performance@postgresql.org
Subject: Re: DELETE taking too much memory
Date: Fri, 08 Jul 2011 11:44:38 +0200
Message-ID: <7826710.6DxyrmAymL@moltowork>
Organization: Mobile Devices
User-Agent: KMail/4.6.0 (Linux/2.6.38.4; KDE/4.6.4; x86_64; ; )
In-Reply-To: 
 <CAEZATCWOgxYrczTWkFDGkdUwvqjq7pnNhmXxYYLZxHZgGcdPuw@mail.gmail.com>
References: 
 <CAHdkwt5b=g_Nj0mogB-8u62U9wEB1KdCbPiBkJKGJyjM4q=FvQ@mail.gmail.com>
	<1310070406.2046.7.camel@laptop>
	<CAEZATCWOgxYrczTWkFDGkdUwvqjq7pnNhmXxYYLZxHZgGcdPuw@mail.gmail.com>
MIME-Version: 1.0
Content-Transfer-Encoding: 7Bit
Content-Type: text/plain; charset="us-ascii"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.13 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	RCVD_IN_SORBS_WEB=0.77
X-Spam-Level: 
X-Archive-Number: 201107/220
X-Sequence-Number: 177599

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