From frum@ar-sd.net Tue Oct  11 11:37:50 2005
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 75547D720B
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 11 Oct 2005 08:37:50 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 83374-09
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 11 Oct 2005 11:37:32 +0000 (GMT)
Received: from svr2.postgresql.org (svr2.postgresql.org [65.19.161.25])
	by svr1.postgresql.org (Postfix) with ESMTP id C4443D76DC
	for <pgsql-performance@postgresql.org>;
	Tue, 11 Oct 2005 08:35:58 -0300 (ADT)
Received: from ar-sd.net (unknown [82.77.155.72])
	by svr2.postgresql.org (Postfix) with ESMTP id 86BBEF11D2
	for <pgsql-performance@postgresql.org>;
	Tue, 11 Oct 2005 08:36:34 +0100 (BST)
Received: from localhost (localhost [127.0.0.1])
	by ar-sd.net (Postfix) with ESMTP id 9562F21396
	for <pgsql-performance@postgresql.org>;
	Tue, 11 Oct 2005 10:31:15 +0300 (EEST)
Received: from ar-sd.net ([127.0.0.1])
	by localhost (linz [127.0.0.1]) (amavisd-new, port 10024) with ESMTP
	id 07680-08 for <pgsql-performance@postgresql.org>;
	Tue, 11 Oct 2005 10:31:14 +0300 (EEST)
Received: from forge (unknown [192.168.0.11])
	by ar-sd.net (Postfix) with SMTP id 90DDCE791
	for <pgsql-performance@postgresql.org>;
	Tue, 11 Oct 2005 10:31:14 +0300 (EEST)
Message-ID: <00bd01c5ce37$f8a4cc00$0b00a8c0@forge>
From: "Andy" <frum@ar-sd.net>
To: <pgsql-performance@postgresql.org>
Subject: Massive delete performance
Date: Tue, 11 Oct 2005 10:47:03 +0300
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_00BA_01C5CE51.1D679E50"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-Virus-Scanned: by amavisd-new at ar-sd.net
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.254 required=5 tests=[AWL=0.176,
	FORGED_RCVD_HELO=0.05, HTML_60_70=0.027, HTML_MESSAGE=0.001]
X-Spam-Level: 
X-Archive-Number: 200510/185
X-Sequence-Number: 14953

This is a multi-part message in MIME format.

------=_NextPart_000_00BA_01C5CE51.1D679E50
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi to all,=20

I have the following problem: I have a client to which we send every =
night a "dump" with a the database in which there are only their data's. =
It is a stupid solution but I choose this solution because I couldn't =
find any better. The target machine is a windows 2003. =20

So, I have a replication only with the tables that I need to send, then =
I make a copy of this replication, and from this copy I delete all the =
data's that are not needed.=20

How can I increase this DELETE procedure because it is really slow???  =
There are of corse a lot of data's to be deleted.=20



Or is there any other solution for this?=20
DB -> (replication) RE_DB -> (copy) -> COPY_DB -> (Delete unnecesary =
data) -> CLIENT_DB -> (ISDN connection) -> Data's to the client.=20

Regards,=20
Andy.



------=_NextPart_000_00BA_01C5CE51.1D679E50
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2900.2722" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hi to all, </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I have the following problem: I have a =
client to=20
which we send every night a "dump" with a the database in which there =
are only=20
their data's. It is a stupid solution but&nbsp;I choose this solution =
because I=20
couldn't find any better. The target machine is a windows 2003.=20
&nbsp;</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>So, I have a replication only with the =
tables that=20
I need to send, then I make a copy of this replication, and from this =
copy I=20
delete all the data's that are not needed. </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>How can I increase this DELETE =
procedure because it=20
is really slow??? &nbsp;There are of corse a lot of data's to be =
deleted.=20
</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Or is there any other solution for =
this?=20
</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>DB -&gt; (replication) RE_DB -&gt; =
(copy) -&gt;=20
COPY_DB -&gt; (Delete unnecesary data) -&gt; CLIENT_DB -&gt; (ISDN =
connection)=20
-&gt; Data's to the client. </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Regards, </FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Andy.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3D"Times New Roman, Times, serif" size=3D2><FONT=20
color=3D#ffcc00></FONT></FONT>&nbsp;</DIV>
<DIV><FONT color=3D#ffcc00 size=3D2></FONT>&nbsp;</DIV></BODY></HTML>

------=_NextPart_000_00BA_01C5CE51.1D679E50--




From sdavis2@mail.nih.gov Tue Oct  11 11:54:42 2005
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 7EBFFD6FA8
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 11 Oct 2005 08:54:42 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 07655-09
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 11 Oct 2005 11:54:37 +0000 (GMT)
Received: from wimpy.net.nih.gov (wimpy.net.nih.gov [128.231.88.106])
	by svr1.postgresql.org (Postfix) with ESMTP id 89E57D6F96
	for <pgsql-performance@postgresql.org>;
	Tue, 11 Oct 2005 08:54:38 -0300 (ADT)
Received: from wimpy.net.nih.gov (localhost [127.0.0.1])
	by wimpy.net.nih.gov (8.12.11/8.11.7) with ESMTP id j9BBseLH009707
	for <pgsql-performance@postgresql.org>;
	Tue, 11 Oct 2005 07:54:42 -0400 (EDT)
Received: from [128.231.145.14] (holmes.nhgri.nih.gov [128.231.145.14])
	by wimpy.net.nih.gov (8.12.11/8.11.7) with ESMTP id j9BBsevU009698;
	Tue, 11 Oct 2005 07:54:40 -0400 (EDT)
User-Agent: Microsoft-Entourage/10.1.4.030702.0
Date: Tue, 11 Oct 2005 07:54:39 -0400
Subject: Re: Massive delete performance
From: Sean Davis <sdavis2@mail.nih.gov>
To: Andy <frum@ar-sd.net>, <pgsql-performance@postgresql.org>
Message-ID: <BF71203F.10300%sdavis2@mail.nih.gov>
In-Reply-To: <00bd01c5ce37$f8a4cc00$0b00a8c0@forge>
Mime-version: 1.0
Content-type: text/plain; charset="US-ASCII"
Content-transfer-encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.013 required=5 tests=[AWL=0.013]
X-Spam-Level: 
X-Archive-Number: 200510/187
X-Sequence-Number: 14955

On 10/11/05 3:47 AM, "Andy" <frum@ar-sd.net> wrote:

> Hi to all, 
> 
> I have the following problem: I have a client to which we send every night a
> "dump" with a the database in which there are only their data's. It is a
> stupid solution but I choose this solution because I couldn't find any better.
> The target machine is a windows 2003.
> 
> So, I have a replication only with the tables that I need to send, then I make
> a copy of this replication, and from this copy I delete all the data's that
> are not needed. 
> 
> How can I increase this DELETE procedure because it is really slow???  There
> are of corse a lot of data's to be deleted.

Do you have foreign key relationships that must be followed for cascade
delete?  If so, make sure that you have indices on them.  Are you running
any type of vacuum after the whole process?  What kind?

Sean




From frum@ar-sd.net Tue Oct  11 12:06:14 2005
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id DB322D6F08
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 11 Oct 2005 09:06:14 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 37658-07
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 11 Oct 2005 12:06:10 +0000 (GMT)
Received: from ar-sd.net (unknown [82.77.155.72])
	by svr1.postgresql.org (Postfix) with ESMTP id EDEFDD6D71
	for <pgsql-performance@postgresql.org>;
	Tue, 11 Oct 2005 09:06:11 -0300 (ADT)
Received: from localhost (localhost [127.0.0.1])
	by ar-sd.net (Postfix) with ESMTP id 39997215A9;
	Tue, 11 Oct 2005 14:50:06 +0300 (EEST)
Received: from ar-sd.net ([127.0.0.1])
	by localhost (linz [127.0.0.1]) (amavisd-new, port 10024) with ESMTP
	id 10841-04; Tue, 11 Oct 2005 14:50:04 +0300 (EEST)
Received: from forge (unknown [192.168.0.11])
	by ar-sd.net (Postfix) with SMTP id B9430215A7;
	Tue, 11 Oct 2005 14:50:04 +0300 (EEST)
Message-ID: <017601c5ce5c$219e1b10$0b00a8c0@forge>
From: "Andy" <frum@ar-sd.net>
To: "Sean Davis" <sdavis2@mail.nih.gov>,
	<pgsql-performance@postgresql.org>
References: <BF71203F.10300%sdavis2@mail.nih.gov>
Subject: Re: Massive delete performance
Date: Tue, 11 Oct 2005 15:05:52 +0300
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
	reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-Virus-Scanned: by amavisd-new at ar-sd.net
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.226 required=5 tests=[AWL=0.176,
	FORGED_RCVD_HELO=0.05]
X-Spam-Level: 
X-Archive-Number: 200510/188
X-Sequence-Number: 14956

> Do you have foreign key relationships that must be followed for cascade
> delete?  If so, make sure that you have indices on them.
Yes I have such things. Indexes are on these fields. >> To be onest this 
delete is taking the longest time, but it involves about 10 tables.

> Are you running
> any type of vacuum after the whole process?  What kind?
Full vacuum. (cmd: vacuumdb -f)

Is there any configuration parameter for delete speed up?


----- Original Message ----- 
From: "Sean Davis" <sdavis2@mail.nih.gov>
To: "Andy" <frum@ar-sd.net>; <pgsql-performance@postgresql.org>
Sent: Tuesday, October 11, 2005 2:54 PM
Subject: Re: [PERFORM] Massive delete performance


> On 10/11/05 3:47 AM, "Andy" <frum@ar-sd.net> wrote:
>
>> Hi to all,
>>
>> I have the following problem: I have a client to which we send every 
>> night a
>> "dump" with a the database in which there are only their data's. It is a
>> stupid solution but I choose this solution because I couldn't find any 
>> better.
>> The target machine is a windows 2003.
>>
>> So, I have a replication only with the tables that I need to send, then I 
>> make
>> a copy of this replication, and from this copy I delete all the data's 
>> that
>> are not needed.
>>
>> How can I increase this DELETE procedure because it is really slow??? 
>> There
>> are of corse a lot of data's to be deleted.
>
> Do you have foreign key relationships that must be followed for cascade
> delete?  If so, make sure that you have indices on them.  Are you running
> any type of vacuum after the whole process?  What kind?
>
> Sean
>
>
> 




From sdavis2@mail.nih.gov Tue Oct  11 12:12:11 2005
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 669C1D6F80
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 11 Oct 2005 09:12:11 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 49349-06
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 11 Oct 2005 12:12:07 +0000 (GMT)
Received: from wimpy.net.nih.gov (wimpy.net.nih.gov [128.231.88.106])
	by svr1.postgresql.org (Postfix) with ESMTP id 72467D6FEA
	for <pgsql-performance@postgresql.org>;
	Tue, 11 Oct 2005 09:12:09 -0300 (ADT)
Received: from wimpy.net.nih.gov (localhost [127.0.0.1])
	by wimpy.net.nih.gov (8.12.11/8.11.7) with ESMTP id j9BCCCPH016476
	for <pgsql-performance@postgresql.org>;
	Tue, 11 Oct 2005 08:12:13 -0400 (EDT)
Received: from [128.231.145.14] (holmes.nhgri.nih.gov [128.231.145.14])
	by wimpy.net.nih.gov (8.12.11/8.11.7) with ESMTP id j9BCCCbI016467;
	Tue, 11 Oct 2005 08:12:12 -0400 (EDT)
User-Agent: Microsoft-Entourage/10.1.4.030702.0
Date: Tue, 11 Oct 2005 08:12:11 -0400
Subject: Re: Massive delete performance
From: Sean Davis <sdavis2@mail.nih.gov>
To: Andy <frum@ar-sd.net>, <pgsql-performance@postgresql.org>
Message-ID: <BF71245B.10304%sdavis2@mail.nih.gov>
In-Reply-To: <017601c5ce5c$219e1b10$0b00a8c0@forge>
Mime-version: 1.0
Content-type: text/plain; charset="US-ASCII"
Content-transfer-encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.013 required=5 tests=[AWL=0.013]
X-Spam-Level: 
X-Archive-Number: 200510/189
X-Sequence-Number: 14957

On 10/11/05 8:05 AM, "Andy" <frum@ar-sd.net> wrote:

>> Do you have foreign key relationships that must be followed for cascade
>> delete?  If so, make sure that you have indices on them.
> Yes I have such things. Indexes are on these fields. >> To be onest this
> delete is taking the longest time, but it involves about 10 tables.

Can you post explain analyze output of the next delete?

Sean




From sgunderson@bigfoot.com Tue Oct  11 12:19:54 2005
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 68C1BD701B
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 11 Oct 2005 09:19:54 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 59218-10
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 11 Oct 2005 12:19:45 +0000 (GMT)
Received: from cassarossa.samfundet.no (cassarossa.samfundet.no
	[129.241.93.19])
	by svr1.postgresql.org (Postfix) with ESMTP id 6E3E0D7061
	for <pgsql-performance@postgresql.org>;
	Tue, 11 Oct 2005 09:19:44 -0300 (ADT)
Received: from trofast.sesse.net ([129.241.93.32])
	by cassarossa.samfundet.no with esmtp (Exim 4.50) id 1EPJ6g-0000Wq-DQ
	for pgsql-performance@postgresql.org; Tue, 11 Oct 2005 14:19:47 +0200
Received: from sesse by trofast.sesse.net with local (Exim 3.36 #1 (Debian))
	id 1EPJ6c-0006ln-00
	for <pgsql-performance@postgresql.org>; Tue, 11 Oct 2005 14:19:42 +0200
Date: Tue, 11 Oct 2005 14:19:42 +0200
From: "Steinar H. Gunderson" <sgunderson@bigfoot.com>
To: pgsql-performance@postgresql.org
Subject: Re: Massive delete performance
Message-ID: <20051011121942.GA25543@uio.no>
Mail-Followup-To: pgsql-performance@postgresql.org
References: <00bd01c5ce37$f8a4cc00$0b00a8c0@forge>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
In-Reply-To: <00bd01c5ce37$f8a4cc00$0b00a8c0@forge>
X-Operating-System: Linux 2.6.11.8 on a i686
X-Message-Flag: Outlook? --> http://www.mozilla.org/products/thunderbird/
User-Agent: Mutt/1.5.11
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.012 required=5 tests=[AWL=0.012]
X-Spam-Level: 
X-Archive-Number: 200510/190
X-Sequence-Number: 14958

On Tue, Oct 11, 2005 at 10:47:03AM +0300, Andy wrote:
> So, I have a replication only with the tables that I need to send, then I
> make a copy of this replication, and from this copy I delete all the data's
> that are not needed. 
> 
> How can I increase this DELETE procedure because it is really slow???
> There are of corse a lot of data's to be deleted. 

Instead of copying and then deleting, could you try just selecting out what
you wanted in the first place?

/* Steinar */
-- 
Homepage: http://www.sesse.net/



From frum@ar-sd.net Tue Oct  11 13:28:14 2005
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 2C9EFD7095
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 11 Oct 2005 10:28:14 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 22355-08
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 11 Oct 2005 13:28:07 +0000 (GMT)
Received: from ar-sd.net (unknown [82.77.155.72])
	by svr1.postgresql.org (Postfix) with ESMTP id 8E362D7047
	for <pgsql-performance@postgresql.org>;
	Tue, 11 Oct 2005 10:28:09 -0300 (ADT)
Received: from localhost (localhost [127.0.0.1])
	by ar-sd.net (Postfix) with ESMTP id 431F0215A3;
	Tue, 11 Oct 2005 16:12:04 +0300 (EEST)
Received: from ar-sd.net ([127.0.0.1])
	by localhost (linz [127.0.0.1]) (amavisd-new, port 10024) with ESMTP
	id 11529-02; Tue, 11 Oct 2005 16:12:00 +0300 (EEST)
Received: from forge (unknown [192.168.0.11])
	by ar-sd.net (Postfix) with SMTP id AC1201EE89;
	Tue, 11 Oct 2005 16:12:00 +0300 (EEST)
Message-ID: <01e401c5ce67$93d65de0$0b00a8c0@forge>
From: "Andy" <frum@ar-sd.net>
To: "Steinar H. Gunderson" <sgunderson@bigfoot.com>,
	<pgsql-performance@postgresql.org>
References: <00bd01c5ce37$f8a4cc00$0b00a8c0@forge>
	<20051011121942.GA25543@uio.no>
Subject: Re: Massive delete performance
Date: Tue, 11 Oct 2005 16:27:48 +0300
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="utf-8"; reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-Virus-Scanned: by amavisd-new at ar-sd.net
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.214 required=5 tests=[AWL=0.164,
	FORGED_RCVD_HELO=0.05]
X-Spam-Level: 
X-Archive-Number: 200510/191
X-Sequence-Number: 14959

We run the DB on a linux system. The client has a windows system. The
application is almost the same (so the database structure is 80% the same).
The difference is that the client does not need all the tables.

So, in the remaining tables there are a lot of extra data's that don't
belong to this client. We have to send every night a updated "info" to the
client database. Our (have to admin) "fast and not the best" solution was so
replicated the needed tables, and delete from these the info that is not
needed.

So, I send to this client a "dump" from the database.

I also find the ideea "not the best", but couldn't find in two days another
fast solution. And it works this way for 4 months.

Out database is not THAT big (500MB), the replication about (300MB)...
everything works fast enough except this delete....


How can I evidence the cascade deletes also on explain analyze?

The answer for Sean Davis <sdavis2@mail.nih.gov>:

EXPLAIN ANALYZE
DELETE FROM report WHERE id_order IN
(SELECT o.id FROM orders o WHERE o.id_ag NOT IN (SELECT cp.id_ag FROM users 
u INNER JOIN
contactpartner cp ON cp.id_user=u.id WHERE u.name in ('dc') ORDER BY 
cp.id_ag))

Hash IN Join  (cost=3532.83..8182.33 rows=32042 width=6) (actual 
time=923.456..2457.323 rows=59557 loops=1)
  Hash Cond: ("outer".id_order = "inner".id)
  ->  Seq Scan on report  (cost=0.00..2613.83 rows=64083 width=10) (actual 
time=33.269..1159.024 rows=64083 loops=1)
  ->  Hash  (cost=3323.31..3323.31 rows=32608 width=4) (actual 
time=890.021..890.021 rows=0 loops=1)
        ->  Seq Scan on orders o  (cost=21.12..3323.31 rows=32608 width=4) 
(actual time=58.428..825.306 rows=60596 loops=1)
              Filter: (NOT (hashed subplan))
              SubPlan
                ->  Sort  (cost=21.11..21.12 rows=3 width=4) (actual 
time=47.612..47.612 rows=1 loops=1)
                      Sort Key: cp.id_ag
                      ->  Nested Loop  (cost=0.00..21.08 rows=3 width=4) 
(actual time=47.506..47.516 rows=1 loops=1)
                            ->  Index Scan using users_name_idx on users u 
(cost=0.00..5.65 rows=1 width=4) (actual time=20.145..20.148 rows=1 loops=1)
                                  Index Cond: ((name)::text = 'dc'::text)
                            ->  Index Scan using contactpartner_id_user_idx 
on contactpartner cp  (cost=0.00..15.38 rows=4 width=8) (actual 
time=27.348..27.352 rows=1 loops=1)
                                  Index Cond: (cp.id_user = "outer".id)
Total runtime: 456718.658 ms




----- Original Message ----- 
From: "Steinar H. Gunderson" <sgunderson@bigfoot.com>
To: <pgsql-performance@postgresql.org>
Sent: Tuesday, October 11, 2005 3:19 PM
Subject: Re: [PERFORM] Massive delete performance


> On Tue, Oct 11, 2005 at 10:47:03AM +0300, Andy wrote:
>> So, I have a replication only with the tables that I need to send, then I
>> make a copy of this replication, and from this copy I delete all the
>> data's
>> that are not needed.
>>
>> How can I increase this DELETE procedure because it is really slow???
>> There are of corse a lot of data's to be deleted.
>
> Instead of copying and then deleting, could you try just selecting out
> what
> you wanted in the first place?
>
> /* Steinar */
> -- 
> Homepage: http://www.sesse.net/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>




From tgl@sss.pgh.pa.us Tue Oct  11 14:17:30 2005
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 063E3D7057
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 11 Oct 2005 11:17:30 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 48567-10
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 11 Oct 2005 14:17:21 +0000 (GMT)
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 54BC0D6E41
	for <pgsql-performance@postgresql.org>;
	Tue, 11 Oct 2005 11:17:23 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j9BEHCNv013906;
	Tue, 11 Oct 2005 10:17:12 -0400 (EDT)
To: "Andy" <frum@ar-sd.net>
Cc: "Steinar H. Gunderson" <sgunderson@bigfoot.com>,
	pgsql-performance@postgresql.org
Subject: Re: Massive delete performance 
In-reply-to: <01e401c5ce67$93d65de0$0b00a8c0@forge> 
References: <00bd01c5ce37$f8a4cc00$0b00a8c0@forge>
	<20051011121942.GA25543@uio.no>
	<01e401c5ce67$93d65de0$0b00a8c0@forge>
Comments: In-reply-to "Andy" <frum@ar-sd.net>
	message dated "Tue, 11 Oct 2005 16:27:48 +0300"
Date: Tue, 11 Oct 2005 10:17:12 -0400
Message-ID: <13905.1129040232@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.006 required=5 tests=[AWL=0.006]
X-Spam-Level: 
X-Archive-Number: 200510/193
X-Sequence-Number: 14961

"Andy" <frum@ar-sd.net> writes:
> EXPLAIN ANALYZE
> DELETE FROM report WHERE id_order IN
> ...

> Hash IN Join  (cost=3532.83..8182.33 rows=32042 width=6) (actual 
> time=923.456..2457.323 rows=59557 loops=1)
> ...
> Total runtime: 456718.658 ms

So the runtime is all in the delete triggers.  The usual conclusion from
this is that there is a foreign key column pointing at this table that
does not have an index, or is not the same datatype as the column it
references.  Either condition will force a fairly inefficient way of
handling the FK deletion check.

			regards, tom lane



From frum@ar-sd.net Tue Oct  11 14:27:44 2005
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 2E1E9D7129
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 11 Oct 2005 11:27:44 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 76328-10
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 11 Oct 2005 14:27:39 +0000 (GMT)
Received: from ar-sd.net (unknown [82.77.155.72])
	by svr1.postgresql.org (Postfix) with ESMTP id 1E2E6D710C
	for <pgsql-performance@postgresql.org>;
	Tue, 11 Oct 2005 11:27:42 -0300 (ADT)
Received: from localhost (localhost [127.0.0.1])
	by ar-sd.net (Postfix) with ESMTP id 110E62164D;
	Tue, 11 Oct 2005 17:11:37 +0300 (EEST)
Received: from ar-sd.net ([127.0.0.1])
	by localhost (linz [127.0.0.1]) (amavisd-new, port 10024) with ESMTP
	id 11529-08; Tue, 11 Oct 2005 17:11:33 +0300 (EEST)
Received: from forge (unknown [192.168.0.11])
	by ar-sd.net (Postfix) with SMTP id 56C5121649;
	Tue, 11 Oct 2005 17:11:33 +0300 (EEST)
Message-ID: <001101c5ce6f$f0f4e480$0b00a8c0@forge>
From: "Andy" <frum@ar-sd.net>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: "Steinar H. Gunderson" <sgunderson@bigfoot.com>,
	<pgsql-performance@postgresql.org>
References: <00bd01c5ce37$f8a4cc00$0b00a8c0@forge>
	<20051011121942.GA25543@uio.no>
	<01e401c5ce67$93d65de0$0b00a8c0@forge>
	<13905.1129040232@sss.pgh.pa.us>
Subject: Re: Massive delete performance 
Date: Tue, 11 Oct 2005 17:27:40 +0300
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
	reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-Virus-Scanned: by amavisd-new at ar-sd.net
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.203 required=5 tests=[AWL=0.153,
	FORGED_RCVD_HELO=0.05]
X-Spam-Level: 
X-Archive-Number: 200510/195
X-Sequence-Number: 14963

Ups folks,

Indeed there were 2 important indexes missing. Now it runs about 10 times 
faster. Sorry for the caused trouble :) and thanx for help.


Hash IN Join  (cost=3307.49..7689.47 rows=30250 width=6) (actual 
time=227.666..813.786 rows=56374 loops=1)
  Hash Cond: ("outer".id_order = "inner".id)
  ->  Seq Scan on report  (cost=0.00..2458.99 rows=60499 width=10) (actual 
time=0.035..269.422 rows=60499 loops=1)
  ->  Hash  (cost=3109.24..3109.24 rows=30901 width=4) (actual 
time=227.459..227.459 rows=0 loops=1)
        ->  Seq Scan on orders o  (cost=9.73..3109.24 rows=30901 width=4) 
(actual time=0.429..154.219 rows=57543 loops=1)
              Filter: (NOT (hashed subplan))
              SubPlan
                ->  Sort  (cost=9.71..9.72 rows=3 width=4) (actual 
time=0.329..0.330 rows=1 loops=1)
                      Sort Key: cp.id_ag
                      ->  Nested Loop  (cost=0.00..9.69 rows=3 width=4) 
(actual time=0.218..0.224 rows=1 loops=1)
                            ->  Index Scan using users_name_idx on users u 
(cost=0.00..5.61 rows=1 width=4) (actual time=0.082..0.084 rows=1 loops=1)
                                  Index Cond: ((name)::text = 'dc'::text)
                            ->  Index Scan using contactpartner_id_user_idx 
on contactpartner cp  (cost=0.00..4.03 rows=3 width=8) (actual 
time=0.125..0.127 rows=1 loops=1)
                                  Index Cond: (cp.id_user = "outer".id)
Total runtime: 31952.811 ms



----- Original Message ----- 
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Andy" <frum@ar-sd.net>
Cc: "Steinar H. Gunderson" <sgunderson@bigfoot.com>; 
<pgsql-performance@postgresql.org>
Sent: Tuesday, October 11, 2005 5:17 PM
Subject: Re: [PERFORM] Massive delete performance


> "Andy" <frum@ar-sd.net> writes:
>> EXPLAIN ANALYZE
>> DELETE FROM report WHERE id_order IN
>> ...
>
>> Hash IN Join  (cost=3532.83..8182.33 rows=32042 width=6) (actual
>> time=923.456..2457.323 rows=59557 loops=1)
>> ...
>> Total runtime: 456718.658 ms
>
> So the runtime is all in the delete triggers.  The usual conclusion from
> this is that there is a foreign key column pointing at this table that
> does not have an index, or is not the same datatype as the column it
> references.  Either condition will force a fairly inefficient way of
> handling the FK deletion check.
>
> regards, tom lane
>
> 




From weigelt@metux.de Tue Oct  11 21:48:54 2005
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 599D0D77D2
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 11 Oct 2005 18:48:54 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 45545-02
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 11 Oct 2005 21:48:49 +0000 (GMT)
Received: from metux.de (seven.metux.de [193.16.1.1])
	by svr1.postgresql.org (Postfix) with ESMTP id 8F52DD72F6
	for <pgsql-performance@postgresql.org>;
	Tue, 11 Oct 2005 18:48:50 -0300 (ADT)
Received: (from weigelt@localhost)
	by metux.de (8.12.10/8.12.10) id j9BLmoWc004153
	for pgsql-performance@postgresql.org; Tue, 11 Oct 2005 23:48:50 +0200
Date: Tue, 11 Oct 2005 23:48:50 +0200
From: Enrico Weigelt <weigelt@metux.de>
To: pgsql-performance@postgresql.org
Subject: Re: Massive delete performance
Message-ID: <20051011214849.GA23769@nibiru.local>
Reply-To: weigelt@metux.de
Mail-Followup-To: pgsql-performance@postgresql.org
References: <00bd01c5ce37$f8a4cc00$0b00a8c0@forge>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <00bd01c5ce37$f8a4cc00$0b00a8c0@forge>
User-Agent: Mutt/1.4.1i
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.005 required=5 tests=[AWL=0.005]
X-Spam-Level: 
X-Archive-Number: 200510/204
X-Sequence-Number: 14972

* Andy <frum@ar-sd.net> wrote:

<snip>
>    I have the following problem: I have a client to which we send every
>    night a "dump" with a the database in which there are only their
>    data's. It is a stupid solution but I choose this solution because I
>    couldn't find any better. The target machine is a windows 2003.
> 
>    So, I have a replication only with the tables that I need to send,
>    then I make a copy of this replication, and from this copy I delete
>    all the data's that are not needed.

Why not filtering out as much unnecessary stuff as possible on copying ?

<snip>

>    How can I increase this DELETE procedure because it is really slow???
>     There are of corse a lot of data's to be deleted.

Have you set up the right indices ?


cu
-- 
---------------------------------------------------------------------
 Enrico Weigelt    ==   metux IT service
  phone:     +49 36207 519931         www:       http://www.metux.de/
  fax:       +49 36207 519932         email:     contact@metux.de
---------------------------------------------------------------------
  Realtime Forex/Stock Exchange trading powered by postgreSQL :))
                                            http://www.fxignal.net/
---------------------------------------------------------------------