Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 42AF6B5DBF6
	for <pgsql-general-postgresql.org@mail.postgresql.org>;
	Fri,  8 Jul 2011 06:50:16 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024)
	with ESMTP id 90484-03; Fri,  8 Jul 2011 09:50:10 +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 B04ECB5DBF8;
	Fri,  8 Jul 2011 06:50:09 -0300 (ADT)
Received: by wyg8 with SMTP id 8so1186975wyg.19
	for <multiple recipients>; Fri, 08 Jul 2011 02:50:09 -0700 (PDT)
Received: by 10.216.242.205 with SMTP id i55mr498829wer.66.1310118609069;
	Fri, 08 Jul 2011 02:50:09 -0700 (PDT)
Received: from moltowork.localnet ([86.47.169.233])
	by mx.google.com with ESMTPS id p14sm3294999wbh.47.2011.07.08.02.50.07
	(version=SSLv3 cipher=OTHER); Fri, 08 Jul 2011 02:50:08 -0700 (PDT)
From: Vincent de Phily <vincent.dephily@mobile-devices.fr>
To: "French, Martin" <frenchm@cromwell.co.uk>
Cc: pgsql-general@postgresql.org, pgsql-performance@postgresql.org
Subject: Re: [PERFORM] DELETE taking too much memory
Date: Fri, 08 Jul 2011 11:50:16 +0200
Message-ID: <2882675.kL2jiuFQz7@moltowork>
Organization: Mobile Devices
User-Agent: KMail/4.6.0 (Linux/2.6.38.4; KDE/4.6.4; x86_64; ; )
In-Reply-To: 
 <81976671721DF04B9DCA6ECD87941A402B40FBD5@roundway.Cromwell-tools.co.uk>
References: 
 <CAHdkwt5b=g_Nj0mogB-8u62U9wEB1KdCbPiBkJKGJyjM4q=FvQ@mail.gmail.com>
	<6960221.CJCJgSSb5M@moltowork>
	<81976671721DF04B9DCA6ECD87941A402B40FBD5@roundway.Cromwell-tools.co.uk>
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/221
X-Sequence-Number: 177600

On Friday 08 July 2011 10:31:33 French, Martin wrote:
> If the query planner thinks it has the default amount of memory (128MB)
> and the stats are out of date, then it will by no means be able to plan
> proper execution.
> 
> I would recommend setting the effective_cache_size to an appropriate
> value, running "analyze" on both tables with an appropriate stats
> target, and then explaining the query again to see if it's more
> accurate.

Yes, I'll schedule those two to run during the night and repost an explain, 
for information. However, we worked around the initial problem by running the 
delete in smaller batches.

Thanks.
-- 
Vincent de Phily