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 8D1DCD834F for ; Tue, 6 Sep 2005 12:04:28 -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 87790-08 for ; Tue, 6 Sep 2005 15:04:25 +0000 (GMT) Received: from EXCHANGE.sf.nimblefish.com (unknown [66.54.159.162]) by svr1.postgresql.org (Postfix) with ESMTP id 03BF2D81FE for ; Tue, 6 Sep 2005 12:04:24 -0300 (ADT) X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C5B2F4.43A1015E" Subject: Poor performance of delete by primary key Date: Tue, 6 Sep 2005 08:04:22 -0700 Message-ID: <2F9239F5E316C54688FDA9814FF045D6EADACC@EXCHANGE.sf.nimblefish.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Poor performance of delete by primary key thread-index: AcWy9EOBO5xF/a4BSuuMRTGGqM18Hg== From: "Brian Choate" To: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.505 required=5 tests=[HTML_20_30=0.504, HTML_MESSAGE=0.001] X-Spam-Level: X-Archive-Number: 200509/90 X-Sequence-Number: 14398 This is a multi-part message in MIME format. ------_=_NextPart_001_01C5B2F4.43A1015E Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello, We are seeing a very strange behavior from postgres. For one of our very = common tasks we have to delete records from a table of around 500,000 = rows. The delete is by id which is the primary key. It seems to be = consistently taking around 10 minutes to preform. This is totally out of = line with the rest of the performance of the database. This table does have children through foreign keys, but I am pretty sure = that all foreign key constraints in the schema have indexes on their = children.=20 Sometimes if we do a vacuum right before running the process the delete = will go much faster. But then the next time we run the task, even just a = few minutes later, the delete takes a long time to run. We deploy the same application also on Oracle. The schemas are pretty = much identical. On similar hardware with actually about 4 to 5 times the = data, Oracle does not seem to have the same problem. Not that that = really means anything since the internals of Oracle and PostgreSQL are = so different, but an interesting fact anyway. Any ideas on what might be going on? Thanks, B. ------_=_NextPart_001_01C5B2F4.43A1015E Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Poor performance of delete by primary key

Hello,

We are seeing a very strange behavior from postgres. For one of our very = common tasks we have to delete records from a table of around 500,000 = rows. The delete is by id which is the primary key. It seems to be = consistently taking around 10 minutes to preform. This is totally out of = line with the rest of the performance of the database.

This table does have children through foreign keys, but I am pretty sure = that all foreign key constraints in the schema have indexes on their = children.

Sometimes if we do a vacuum right before running the process the delete = will go much faster. But then the next time we run the task, even just a = few minutes later, the delete takes a long time to run.

We deploy the same application also on Oracle. The schemas are pretty = much identical. On similar hardware with actually about 4 to 5 times the = data, Oracle does not seem to have the same problem. Not that that = really means anything since the internals of Oracle and PostgreSQL are = so different, but an interesting fact anyway.

Any ideas on what might be going on?

Thanks,
B.

------_=_NextPart_001_01C5B2F4.43A1015E--