From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alexander Stanier <alexander.stanier@egsgroup.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: How to increase row deletion efficiency?
Date: Wed, 07 Dec 2005 10:11:56 -0500
Message-ID: <29270.1133968316@sss.pgh.pa.us> (raw)
In-Reply-To: <4396F360.6080000@egsgroup.com>
References: <4396F360.6080000@egsgroup.com>
Alexander Stanier <alexander.stanier@egsgroup.com> writes:
> 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.
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, alexander.stanier@egsgroup.com
Subject: Re: How to increase row deletion efficiency?
In-Reply-To: <29270.1133968316@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox