DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:mime-version:content-type;
	b=NCVBef3lNQ+0TK6jUjZMqo1K/LnVDoNtE9zz+uulWzwOn13H9eCBV3ftuf+r1+lohW
	Rwlb29l+c1boK8/sSOUrdi6eNvOmteUbNX6nbbotjtdQxG6MejEDVNpRVbFfEFbRp0Ow
	pjrhYhAyYZ+mG5EsRpnsrlcVC/xJH5m4fM9mc=
Message-ID: <331e40660810020242u2f0f5de2qe146312e448a7df4@mail.gmail.com>
Date: Thu, 2 Oct 2008 12:42:15 +0300
From: "=?ISO-8859-5?B?svbi0Nv22SDC2Nzn2OjY3Q==?=" <tivv00@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Delete performance again
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_909_27631440.1222940536035"

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

Hello.

I have a database with company table that have a bunch of related
(delete=cascade) tables.
Also it has 1<->M relation to company_descr table.
Once we've found that ~half of our companies do not have any description and
we would like to remove them.
First this I've tried was
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. To make it faster I've done next thing:

create temporary table comprm(id) as select id from company;
delete from comprm where id in (select company_id from company_descr);
delete from company where id in (select id from comprm);

That was much better. So the question is why postgresql can't do such a
thing.
But it was better only until "removing" dataset was small (~5% of all
table).
As soon as I've tried to remove 50% I've got speed problems. I've ensured I
have all indexes for both ends of foreign key.
I've tried to remove all cascaded entries by myself, e.g.:

create temporary table comprm(id) as select id from company;
delete from comprm where id in (select company_id from company_descr);
delete from company_alias where company_id in (select id from comprm);
...
delete from company where id in (select id from comprm);

It did not help until I drop all constraints before and recreate all
constraints after.
Now I have it work for 15minutes, while previously it could not do in a day.

Is it OK? I'd say, some (if not all) of the optimizations could be done by
postgresql optimizer.

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

<div dir="ltr">Hello.<br><br>I have a database with company table that have a bunch of related (delete=cascade) tables.<br>Also it has 1&lt;-&gt;M relation to company_descr table.<br>Once we&#39;ve found that ~half of our companies do not have any description and we would like to remove them.<br>
First this I&#39;ve tried was<br>delete from company where id not in (select company_id from company_descr);<br>I&#39;ve tried to analyze command, but unlike to other RDBM I&#39;ve used it did not include cascade deletes/checks into query plan. That is first problem.<br>
It was SLOW. To make it faster I&#39;ve done next thing:<br><br>create temporary table comprm(id) as select id from company;<br>delete from comprm where id in (select company_id from company_descr);<br>delete from company where id in (select id from comprm);<br>
<br>That was much better. So the question is why postgresql can&#39;t do such a thing.<br>But it was better only until &quot;removing&quot; dataset was small (~5% of all table). <br>As soon as I&#39;ve tried to remove 50% I&#39;ve got speed problems. I&#39;ve ensured I have all indexes for both ends of foreign key.<br>
I&#39;ve tried to remove all cascaded entries by myself, e.g.:<br><br>create temporary table comprm(id) as select id from company;<br>
delete from comprm where id in (select company_id from company_descr);<br>delete from company_alias where company_id in (select id from comprm);<br>...<br>delete from company where id in (select id from comprm);<br><br>
It did not help until I drop all constraints before and recreate all constraints after.<br>Now I have it work for 15minutes, while previously it could not do in a day.<br><br>Is it OK? I&#39;d say, some (if not all) of the optimizations could be done by postgresql optimizer.<br>
<br><span style="display: none; visibility: hidden;" id="wikEdSetupFlag"></span><span style="display: none; visibility: hidden;" id="wikEdDiffSetupFlag"></span></div>

------=_Part_909_27631440.1222940536035--