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

Forgot to mention:
    Running psql (PostgreSQL) 7.3.1 and using jdbc driver pg73jdbc3.jar
Cheers
Noel

Noel wrote:

> 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?
>