Обсуждение: long deletes :( Pls help

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

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

Noel <noel.faux@med.monash.edu.au> writes:
> 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.

It sounds like you're getting a really bad plan for the queries
triggered by the foreign key constraints.  One thing to make sure
of is that the datatype of the referenced and referencing columns
are the same in each foreign key relationship.  Also check that the
planner statistics are reasonably up to date (pg_class.relpages and
pg_class.reltuples should be in the vicinity of reality, at least).

            regards, tom lane

I said:
> It sounds like you're getting a really bad plan for the queries
> triggered by the foreign key constraints.  One thing to make sure
> of is that the datatype of the referenced and referencing columns
> are the same in each foreign key relationship.  Also check that the
> planner statistics are reasonably up to date (pg_class.relpages and
> pg_class.reltuples should be in the vicinity of reality, at least).

Oh, I almost forgot the most important thing: you need an index on the
referencing column in each FK relationship.  The system requires you to
have an index on the referenced column, but not the other one.
Unfortunately, deletes in the referenced table are gonna be really slow
if you do not have such an index ...

            regards, tom lane

Hi guys,

   for the past 2  hours or so, i´ve been trying to execute pg_dump remotly,
let me explain better;

I´ve got postgresql 7.4.1 with cygwin and ipc-daemon2, running on winXP,
i´ve created the database, got postmaster running, everything seems fine.
Please correct me if i´m wrong, but to do a backup of a database i only need
the the permission of an OS user and need to execute a command line like
pg_dump.

problem: I have an apllication on one machine running a windows server OS,
and  the database is on a linux, RH8.0, how can i execute a command to
perform a backup operation, from the windows aplication machine, both OS
user are disticts, but have admin privileges.

any help, would be apreciated,

thx,

Iandé

=?iso-8859-1?Q?Iand=E9_Coutinho?= <iande@br.inter.net> writes:
>    for the past 2  hours or so, i�ve been trying to execute pg_dump remotly,
> let me explain better;

It would help if you'd shown exactly what you tried and what errors
you got.

This should work as long as (a) you specify "-h other-machine" in
pg_dump's command line, and (b) the other machine is set up to allow
connections from your machine --- that means a suitable entry in
pg_hba.conf and TCP connections enabled in postgresql.conf.  There's
not enough info in your message to guess exactly where you went wrong,
though.

            regards, tom lane