long deletes :( Pls help - Mailing list pgsql-novice

Hi all,

I'm trying to delete from a table which has 42Mill rows, using a foreign
key, which is index.
The delete has been going three days now :( and is really frustrating.

The scheme is as follows:

region
-------
id
// other fields

region_db_comparison
-----------------------
id
// other fields

alignment // (42Mil) from which I'm trying to delete from
----------
id
region_db_comparison // foreign key to region_db_comparison.id and
indexed (relation: 1(reg_db_cmp) -> N (alignment))
subject_region // foreign key to region.id and indexed (relation:
1(region -> N(alignments))
// other fields

gap //(129Mil entries)
---
id
alignment // foreign key to alignment.id and indexed (relation
N(alignment) -> N(gap))
// other fields

repeat_blastp_block
---------------------
id
alignment // foreign key to alignment.id and indexed (relation
N(alignment) -> N(gap))

I'm doing a delete of a region_db_comparison, in java.
The steps in the code are:
Collect alignments for the region_db_comparison -> For each alignment
delete any gaps associated with it (through the gap.alignment field)
then delete the alignments via the alignment.region_db_comparison field
(WHERE the code takes the most time e.g.. three days)-> delete the
region_db_comparison through id.

When the tables were created we specified NO rules/triggers for DELETE
(such as cascade etc...) only stated which fields were foreign keys and
which tables & fields the referenced to.

Any help / suggestions speeding the delete would be really appreciated.
A colleague suggested disabling any triggers that were automatically
created when the tables were, would this help?
I've looked at pg_triggers but can't make sense of the fields tgtype or
tgargs. What do the values match to? What is the order of tgargs?
For example:
 tgrelid       |             tgname                             |
tgfoid   | tgtype | tgenabled | tgisconstraint | tgconstrname|
17863718 | RI_ConstraintTrigger_35330293  |   1654 |      9    |
t              | t                    | <unnamed>  |

 tgconstrrelid    | tgdeferrable | tginitdeferred | tgnargs | tgattr |
tgargs
      17863723 | f                  | f                    |       6
|
|<unnamed>\000gap\000alignment\000UNSPECIFIED\000alignment\000id\000

Am I understanding the tgargs fields correct; this is a trigger for the
table gap on the feild alignment to the table alignment for the feild id?

--
Noel Faux
Department of Biochemistry and Molecular Biology
Monash University
Clayton 3168
Victoria
Australia