X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 4925B11F6081
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 25 Apr 2006 21:15:06 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 62091-04
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 25 Apr 2006 21:14:57 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from pproxy.gmail.com (pproxy.gmail.com [64.233.166.181])
	by postgresql.org (Postfix) with ESMTP id B904011F6026
	for <pgsql-performance@postgresql.org>;
	Tue, 25 Apr 2006 21:14:55 -0300 (ADT)
Received: by pproxy.gmail.com with SMTP id x66so1513275pye
	for <pgsql-performance@postgresql.org>;
	Tue, 25 Apr 2006 17:14:56 -0700 (PDT)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references;
	b=eseQMjxnGvdT4lG1lrRIFxXIU7PE3OXA30nbXWD5lQw0DgNqr7Ill9G7LGdUhGx7y4YP0qP/S0vdUa8DokV8AL54FBkN3ooRkLIC0aU/PnxoScCrPgcbBKFcsDbWw9WCAU2TQjOyitPO3BuHOFse+9rQc7WeIezlq5maALZrrOU=
Received: by 10.35.22.17 with SMTP id z17mr311983pyi;
	Tue, 25 Apr 2006 17:14:56 -0700 (PDT)
Received: by 10.35.82.19 with HTTP; Tue, 25 Apr 2006 17:14:56 -0700 (PDT)
Message-ID: <8d04ce990604251714g1f5d99bbn172ec554a49c8374@mail.gmail.com>
Date: Tue, 25 Apr 2006 17:14:56 -0700
From: "Junaili Lie" <junaili@gmail.com>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Subject: Re: slow deletes on pgsql 7.4
Cc: pgsql-performance@postgresql.org
In-Reply-To: <9147.1146006558@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_9449_16353100.1146010496029"
References: <8d04ce990604251441ya85c42evb47946272536cabe@mail.gmail.com>
	<9147.1146006558@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200604/585
X-Sequence-Number: 18669

------=_Part_9449_16353100.1146010496029
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

hi,
Thanks for the answer.
I have double checked that all the foreign key that are referencing "id" on
scenario are indexed.
I have even vacuum analyze scenario table and all the tables that reference=
d
this table.
Something that is interesting is that: it only happens for a certain values=
.

ie. delete from scenario where id=3D'1023' is very fast, but delete from
scenario where id=3D'1099' is running forever.

Any ideas?

J




On 4/25/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "Junaili Lie" <junaili@gmail.com> writes:
> > we encounter issues when deleting from a table based on id (primary
> key). O=3D
> > n
> > certain 'id', it took forever to delete and the i/o is 100% busy.
>
> Almost always, if delete is slow when selecting the same rows is fast,
> it's because you've got a trigger performance problem --- most commonly,
> there are foreign keys referencing this table from other tables and you
> don't have the referencing columns indexed.
>
>                         regards, tom lane
>

------=_Part_9449_16353100.1146010496029
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

hi,<br>
Thanks for the answer.<br>
I have double checked that all the foreign key that are referencing &quot;i=
d&quot; on scenario are indexed.<br>
I have even vacuum analyze scenario table and all the tables that reference=
d this table.<br>
Something that is interesting is that: it only happens for a certain values=
. <br>
ie. delete from scenario where id=3D'1023' is very fast, but delete from sc=
enario where id=3D'1099' is running forever.<br>
<br>
Any ideas?<br>
<br>
J<br>
<br>
<br>
<br><br><div><span class=3D"gmail_quote">On 4/25/06, <b class=3D"gmail_send=
ername">Tom Lane</b> &lt;<a href=3D"mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.p=
a.us</a>&gt; wrote:</span><blockquote class=3D"gmail_quote" style=3D"border=
-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-lef=
t: 1ex;">
&quot;Junaili Lie&quot; &lt;<a href=3D"mailto:junaili@gmail.com">junaili@gm=
ail.com</a>&gt; writes:<br>&gt; we encounter issues when deleting from a ta=
ble based on id (primary key). O=3D<br>&gt; n<br>&gt; certain 'id', it took=
 forever to delete and the i/o is 100% busy.
<br><br>Almost always, if delete is slow when selecting the same rows is fa=
st,<br>it's because you've got a trigger performance problem --- most commo=
nly,<br>there are foreign keys referencing this table from other tables and=
 you
<br>don't have the referencing columns indexed.<br><br>&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;regards,
tom lane<br></blockquote></div><br>

------=_Part_9449_16353100.1146010496029--