X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 3DDDF9DC9F2
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 22 Dec 2005 13:15:53 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 18553-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 22 Dec 2005 13:15:49 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from jupiter.egsgroup.com (jupiter.egsgroup.com [217.199.182.198])
	by postgresql.org (Postfix) with ESMTP id 82B2C9DC815
	for <pgsql-sql@postgresql.org>; Thu, 22 Dec 2005 13:15:48 -0400 (AST)
Received: by jupiter.egsgroup.com (Postfix, from userid 65)
	id F288174; Thu, 22 Dec 2005 17:15:52 +0000 (GMT)
X-Scanned-By: AMaViS-ng at egsgroup.com
Received: from [10.20.0.88] (unknown [217.150.127.117])
	by jupiter.egsgroup.com (Postfix) with ESMTP
	id 18D4472; Thu, 22 Dec 2005 17:15:50 +0000 (GMT)
Message-ID: <43AADECF.8070702@egsgroup.com>
Date: Thu, 22 Dec 2005 17:13:51 +0000
From: Alexander Stanier <alexander.stanier@egsgroup.com>
User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: How to increase row deletion efficiency?
References: <4396F360.6080000@egsgroup.com> <29270.1133968316@sss.pgh.pa.us>
In-Reply-To: <29270.1133968316@sss.pgh.pa.us>
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.001 required=5 tests=[AWL=-0.001,
	HTML_MESSAGE=0.001, MIME_HTML_ONLY=0.001]
X-Spam-Score: 0.001
X-Spam-Level: 
X-Archive-Number: 200512/201
X-Sequence-Number: 23743

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Have only just had a chance to try this. There were several missing
indexes on the referencing columns including one on a table of circa
150K records. It now completes in a couple of minutes. Also it appears
to be quicker if one doesn't defer the constraints. Thanks for the
advice.<br>
<br>
Regards, Alex.<br>
<br>
Tom Lane wrote:
<blockquote cite="mid29270.1133968316@sss.pgh.pa.us" type="cite">
  <pre wrap="">Alexander Stanier <a class="moz-txt-link-rfc2396E" href="mailto:alexander.stanier@egsgroup.com">&lt;alexander.stanier@egsgroup.com&gt;</a> writes:
  </pre>
  <blockquote type="cite">
    <pre wrap="">I am currently trying to separate two environments contained in one 
database. Essentially I need to break that one database down into two 
with a portion of the data going to each new database. I am intending to 
achieve this by duplicating the database and then stripping out the data 
that is not required in each database. I have started by trying to 
delete data from a set of 28 related tables, however the performance 
appears to be terrible. I am deleting from a table called document which 
cascades down to 27 tables underneath it linked by various cascading 
foreign key constraints. Some of these subsidiary tables have as many as 
a couple of million records.
    </pre>
  </blockquote>
  <pre wrap=""><!---->
Do you have indexes on the referencing columns?  PG enforces an index on
the referenced column, but not on the other end, and DELETE is where it
will hurt if you haven't got one.

			regards, tom lane

  </pre>
</blockquote>
</body>
</html>