Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id E763437BFE0
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu,  2 Oct 2008 12:22:09 -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 28660-01 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Thu,  2 Oct 2008 12:22:01 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from wr-out-0506.google.com (wr-out-0506.google.com
 [64.233.184.236])
	by postgresql.org (Postfix) with ESMTP id ADFF437BFA4
	for <pgsql-performance@postgresql.org>;
	Thu,  2 Oct 2008 12:22:00 -0300 (ADT)
Received: by wr-out-0506.google.com with SMTP id c49so487657wra.19
	for <pgsql-performance@postgresql.org>;
	Thu, 02 Oct 2008 08:21:59 -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=CDH13YWBDWeDID3GD1b7wig2Nu1KFDp/jhrhEr1VRSE=;
	b=iobqHdLGxwzwg3LdSrzG/er6ZrJeyZjDukKW2pxwII3tybdCRaYMqc6vmP03WPLUrQ
	8rQMqs2ixM+xngKqFLv9YcNkt9qqcJxvFO4TY4kXdTP22vtQHSePL6anNO4gGIRVCltN
	13qTg14fWEdMJ02dongzCKoYXnRWqi+O30TBo=
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=QfygI1NQjFeR6XdWZIrgXvpfz+10An79OTITYa+PpTnvTZ+86h5wAMZQ3y44fD59ba
	jr3/F1SV5UBX4yqCz6qJ0J3zSCb1meNv2vjjhYfM2WQuvGdFhqFuzMvoLKnfj01N0e1K
	NR+naQe1Rvl8Izh/HiM2DSQ1dJKzo64F0xXRk=
Received: by 10.100.154.19 with SMTP id b19mr9189833ane.98.1222960919527;
	Thu, 02 Oct 2008 08:21:59 -0700 (PDT)
Received: by 10.65.51.18 with HTTP; Thu, 2 Oct 2008 08:21:59 -0700 (PDT)
Message-ID: <331e40660810020821w4c4f212br3943df435589f731@mail.gmail.com>
Date: Thu, 2 Oct 2008 18:21:59 +0300
From: "=?ISO-8859-5?B?svbi0Nv22SDC2Nzn2OjY3Q==?=" <tivv00@gmail.com>
Subject: Re: Delete performance again
Cc: pgsql-performance@postgresql.org
In-Reply-To: <23315.1222949660@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_3730_24582921.1222960919493"
References: <331e40660810020242u2f0f5de2qe146312e448a7df4@mail.gmail.com>
	<23315.1222949660@sss.pgh.pa.us>
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/19
X-Sequence-Number: 31600

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

2008/10/2 Tom Lane <tgl@sss.pgh.pa.us>

> "=?ISO-8859-5?B?svbi0Nv22SDC2Nzn2OjY3Q==?=" <tivv00@gmail.com> writes:
> > delete from company where id not in (select company_id from
> company_descr);
> > I've tried to analyze command, but unlike to other RDBM I've used it did
> not
> > include cascade deletes/checks into query plan. That is first problem.
> > It was SLOW.
>
> Usually the reason for that is having forgotten to make an index on the
> referencing column(s) ?
>

Not at all. As you can see below in original message, simply "extending" the
query to what should have been done by optimizer helps. I'd say optimizer
always uses fixed plan not taking into account that this is massive update
and id doing index lookup of children records for each parent record, while
it would be much more effective to perform removal of all children records
in single table scan.

It's like trigger "for each record" instead of "for each statement".

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

<div dir="ltr"><br><br><div class="gmail_quote">2008/10/2 Tom Lane <span dir="ltr">&lt;<a href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt;</span><br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div class="Ih2E3d">&quot;=?ISO-8859-5?B?svbi0Nv22SDC2Nzn2OjY3Q==?=&quot; &lt;<a href="mailto:tivv00@gmail.com">tivv00@gmail.com</a>&gt; writes:<br>
&gt; delete from company where id not in (select company_id from company_descr);<br>
&gt; I&#39;ve tried to analyze command, but unlike to other RDBM I&#39;ve used it did not<br>
&gt; include cascade deletes/checks into query plan. That is first problem.<br>
&gt; It was SLOW.<br>
<br>
</div>Usually the reason for that is having forgotten to make an index on the<br>
referencing column(s) ?<br>
</blockquote><div><br>Not at all. As you can see below in original message, simply &quot;extending&quot; the query to what should have been done by optimizer helps. I&#39;d say optimizer always uses fixed plan not taking into account that this is massive update and id doing index lookup of children records for each parent record, while it would be much more effective to perform removal of all children records in single table scan.<br>
<br>It&#39;s like trigger &quot;for each record&quot; instead of &quot;for each statement&quot;.<br></div></div><span style="display: none; visibility: hidden;" id="wikEdSetupFlag"></span><span style="display: none; visibility: hidden;" id="wikEdDiffSetupFlag"></span></div>

------=_Part_3730_24582921.1222960919493--