How to increase row deletion efficiency? - Mailing list pgsql-sql

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.

Before executing the delete statement from document I tried setting all 
constraints as deferred within a transaction, but this does not seem to 
have helped.

I can't work out whether the indexes on these tables are a help or a 
hindrance. Presumably, any involving the foreign keys should help as 
long as PostgreSQL will actually use them, but given that large numbers 
of records are being deleted the query planner may decide just to do a 
sequence scan. An EXPLAIN doesn't show me what it does past the delete 
from document, i.e. if indexes are used when cascading. The downside of 
the indexes is that they have to be maintained which could be a lot of 
work in large scale deletions.

What I fear is that for every row that is deleted from the document 
table, the database is visiting all subsidiary tables to delete all data 
related to that one row before returning to document to delete another 
row. this would mean that all tables are being visited many times. If 
this is the way it is working, then the large tables are going to be a 
real problem. The most efficient way to do it would be to delete all 
document records, then with that list of documents in mind go on to the 
next table and delete all related records so that each table is only 
visited once to delete all the relevant records. I was hoping that 
setting constraints deferred would achieve this.

Can anyone advise me on how PostgreSQL (v8.0.3 on MacOS X 10.3) works in 
a delete statement and what strategy it uses to remove the data?
Can I specify "Unrecoverable" so that it doesn't write redo?
Are they any indicators I can use to tell me what part of the delete is 
taking so much time?
Also can anyone suggest anything else I can do to speed things up?

Or perhaps it simply is a lot of work and there is no way round it. My 
fallback option is to SELECT data that I do need rather than DELETE the 
data that I don't, but this route means I cannot make use of the foreign 
keys.

Regards,
Alex Stanier.