Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 81D7A37C690
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Fri, 10 Oct 2008 05:24:17 -0300 (ADT)
Received: from postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 41640-06 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Fri, 10 Oct 2008 05:24:10 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from yw-out-2324.google.com (yw-out-2324.google.com [74.125.46.28])
	by postgresql.org (Postfix) with ESMTP id 2CE8837C688
	for <pgsql-performance@postgresql.org>;
	Fri, 10 Oct 2008 05:24:09 -0300 (ADT)
Received: by yw-out-2324.google.com with SMTP id 3so135625ywj.73
	for <pgsql-performance@postgresql.org>;
	Fri, 10 Oct 2008 01:24:08 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:received:received:message-id:date:from:subject
	:cc:in-reply-to:mime-version:content-type:references;
	bh=1Db56Ir39ohNy6NBmz2bSH8wg2saqSor21Vu6PC3mQc=;
	b=qVXz1Z/Km+z1TF+jmalKRJWD3nK+XTp5M50Qfp4XTesqtjJZOuhNJmWD724d8KsnMC
	pVSyzgKMeAKAor4Ea7oppfMum8xs/Ee/m+CWfDHelMSXR+aUMeNM16XGkyyEfGKcyeDk
	KkC9Eyv4Etq5/Xu21i1IwdmThNHxMihx6BOyw=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:subject:cc:in-reply-to:mime-version
	:content-type:references;
	b=uOOXGsERNw9Bv7P1X+8beaDXVYmKdpS++agcUxPGKzcXDpa5ynTaTSMYevRPQFen8y
	V4KP+7He8843ywkxP2VIedH71b2CryD8EEZ3hJmJjwEFbOYBbIl8ZMqvzNwLX23gqOyd
	rvqydXnCdz96LQmxdbuECQqFrgPzl/YkKwJyI=
Received: by 10.151.110.9 with SMTP id n9mr1779168ybm.189.1223627048285;
	Fri, 10 Oct 2008 01:24:08 -0700 (PDT)
Received: by 10.65.51.18 with HTTP; Fri, 10 Oct 2008 01:24:08 -0700 (PDT)
Message-ID: <331e40660810100124h7aef87cap4cabbf15848b9380@mail.gmail.com>
Date: Fri, 10 Oct 2008 11:24:08 +0300
From: "=?ISO-8859-5?B?svbi0Nv22SDC2Nzn2OjY3Q==?=" <tivv00@gmail.com>
Subject: Re: Delete performance again
Cc: pgsql-performance@postgresql.org
In-Reply-To: <331e40660810090554n4633de19gc2ee5fd4aa85457c@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_99789_4913476.1223627048234"
References: <331e40660810020242u2f0f5de2qe146312e448a7df4@mail.gmail.com>
	<23315.1222949660@sss.pgh.pa.us>
	<331e40660810020821w4c4f212br3943df435589f731@mail.gmail.com>
	<CA896D7906BF224F8A6D74A1B7E54AB3039E8BEF@JENMAIL01.ad.intershop.net>
	<331e40660810090554n4633de19gc2ee5fd4aa85457c@mail.gmail.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=1.582 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001, MISSING_HEADERS=1.581
X-Spam-Level: *
X-Archive-Number: 200810/53
X-Sequence-Number: 31634

------=_Part_99789_4913476.1223627048234
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

BTW: Have just tried "clean" (without any foreign keys constraints)
peformance of
"delete from tbl where field not in (select)"
vs
"create temporary table tmp(id)  as select distinct field from tbl; delete
from tmp where id in (select); delete from tbl where field in (select id
from tmp)".
both tbl and select are huge.
tbl cardinality is ~5 million, select is ~1 milliion. Number of records to
delete is small.
select is simply "select id from table2".

First (simple) one could not do in a night, second did in few seconds.

------=_Part_99789_4913476.1223627048234
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

<div dir="ltr">BTW: Have just tried &quot;clean&quot; (without any foreign keys constraints) peformance of <br>&quot;delete from tbl where field not in (select)&quot; <br>vs <br>&quot;create temporary table tmp(id)&nbsp; as select distinct field from tbl; delete from tmp where id in (select); delete from tbl where field in (select id from tmp)&quot;.<br>
both tbl and select are huge. <br>tbl cardinality is ~5 million, select is ~1 milliion. Number of records to delete is small.<br>select is simply &quot;select id from table2&quot;.<br><br>First (simple) one could not do in a night, second did in few seconds.<br>
<span style="display: none; visibility: hidden;" id="wikEdSetupFlag"></span><span style="display: none; visibility: hidden;" id="wikEdDiffSetupFlag"></span></div>

------=_Part_99789_4913476.1223627048234--