Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 595F037BF0E
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu,  9 Oct 2008 09:54:48 -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 65840-04 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Thu,  9 Oct 2008 09:54:41 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.238])
	by postgresql.org (Postfix) with ESMTP id 8D6F437BD49
	for <pgsql-performance@postgresql.org>;
	Thu,  9 Oct 2008 09:54:41 -0300 (ADT)
Received: by wx-out-0506.google.com with SMTP id h28so754wxd.28
	for <pgsql-performance@postgresql.org>;
	Thu, 09 Oct 2008 05:54:38 -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=uMFhzZdA3nRqf76FJEtXrSI+BNsA6On4qP/E8AIHigo=;
	b=nDJHOzBG13hvtGP3KSdkZouQJ7KBR6j6zZQYNUhhZGPqAeII97Vdm4YbvyoefeXWXm
	/SsaJMdMA7yWIciaYn0MBTY9c39N3iMGqnZ6npsSTRmRZVSckyS+FTXjGXPDwEK/nQvT
	m6rTNQvkjBZFS/kUDAivV4xi8ohcQqCc2crWc=
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=v6A2tYXQo+LSYIp/APtm3Bjcl95f8Yt3iSkTJH3PfRYxPa41tdSmIUgjJ075zEHGFM
	fTSEgOvuyPwKP/swKCNMezKvdg8PAsm9E/axX0tCXR1AHB7J83yJXJQWK7CXzTcwQQni
	dHzEvE2EvaEuDmFneZ16wyeS8wV+DuGSJWEZk=
Received: by 10.64.185.18 with SMTP id i18mr81026qbf.65.1223556878064;
	Thu, 09 Oct 2008 05:54:38 -0700 (PDT)
Received: by 10.65.51.18 with HTTP; Thu, 9 Oct 2008 05:54:38 -0700 (PDT)
Message-ID: <331e40660810090554n4633de19gc2ee5fd4aa85457c@mail.gmail.com>
Date: Thu, 9 Oct 2008 15:54:38 +0300
From: "=?ISO-8859-5?B?svbi0Nv22SDC2Nzn2OjY3Q==?=" <tivv00@gmail.com>
Subject: Re: Delete performance again
Cc: pgsql-performance@postgresql.org
In-Reply-To: 
 <CA896D7906BF224F8A6D74A1B7E54AB3039E8BEF@JENMAIL01.ad.intershop.net>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_87239_19675150.1223556878132"
References: <331e40660810020242u2f0f5de2qe146312e448a7df4@mail.gmail.com>
	<23315.1222949660@sss.pgh.pa.us>
	<331e40660810020821w4c4f212br3943df435589f731@mail.gmail.com>
	<CA896D7906BF224F8A6D74A1B7E54AB3039E8BEF@JENMAIL01.ad.intershop.net>
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/43
X-Sequence-Number: 31624

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

OK, I did try you proposal and correlated subselect.
I have a database ~900000 companies.
First try was to remove randomly selected 1000 companies
Uncorrelated subselect: 65899ms
Correlated subselect: 97467ms
using: 9605ms
my way: 104979ms. (without constraints recreate)
My is the worst because it is oriented on massive delete.
So I thought USING would perform better, so I did try 10000 companies
my way: 190527ms. (without constraints recreate)
using: 694144ms
I was a little shocked, but I did check plans and found out that it did
switch from Nested Loop to Hash Join.
I did disable Hash Join, it not show Merge Join. This was also disabled....
and I've got 747253ms.
Then I've tried combinations: Without hash join it was the best result of
402629ms, without merge join it was 1096116ms.

My conclusion: Until optimizer would take into account additional actions
needed (like constraints check/cascade deletes/triggers), it can not make
good plan.

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

<div dir="ltr">OK, I did try you proposal and correlated subselect.<br>I have a database ~900000 companies.<br>First try was to remove randomly selected 1000 companies<br>Uncorrelated subselect: 65899ms<br>Correlated subselect: 97467ms<br>
using: 9605ms<br>my way: 104979ms. (without constraints recreate)<br>My is the worst because it is oriented on massive delete. <br>So I thought USING would perform better, so I did try 10000 companies<br>my way: 190527ms. (without constraints recreate)<br>
using: 694144ms<br>I was a little shocked, but I did check plans and found out that it did switch from Nested Loop to Hash Join.<br>I did disable Hash Join, it not show Merge Join. This was also disabled.... <br>and I&#39;ve got 747253ms. <br>
Then I&#39;ve tried combinations: Without hash join it was the best result of 402629ms, without merge join it was 1096116ms.<br><br>My conclusion: Until optimizer would take into account additional actions needed (like constraints check/cascade deletes/triggers), it can not make good plan.<br>
<span style="display: none; visibility: hidden;" id="wikEdSetupFlag"></span><span style="display: none; visibility: hidden;" id="wikEdDiffSetupFlag"></span></div>

------=_Part_87239_19675150.1223556878132--