Re: Problem Deleting Referenced records

Bruno,
I am not sure why but the whole delete proces with the where not exists
method took 3hrs, rather long I would say.

Table A had 2.5mil records
Table B had about 30k records

In addition  Table C with about 2 mil records referenced a  referenced A
(same key as B)
In both A and B about 150k records where deleted... but the process took
more than 3 hrs.

Its pretty long I would say. I noticed in the past that if I had
multiple foreign keys, referencing different tables like TableA <--
TableB <-- TableC then deletes are really slow... sometimes in the area
of one delete per second. Never really figured out why. (And yes I did
run a Vacuum or Vacuum analyze on the DB
 or Tables).

Alex



Bruno Wolff III wrote:

>On Mon, Nov 10, 2003 at 16:20:21 +0900,
>  Alex <alex@meerkatsoft.com> wrote:
>
>
>>Bruno,
>>thanks. I actually did it that way but having to join two tables each
>>1-2 million records makes this process rather time consuming.
>>I was hoping that the ON DELETE options in the constraint could handle
>>that.
>>
>>
>
>If only a small number of the 1-2 million records have old dates, than the
>where not exists method might be faster. An index scan could be used
>to find the records with old dates and then for each record an index
>lookup could be done in table B to see if it should really be deleted.
>
>
>
>>It seems to be a bit odd that if I want to delete 100 records that are
>>not related to each other, and one record deletion fails that then the
>>entire delete process fails.
>>
>>
>
>You can delete each record in its own transaction if you want that
>behavior.
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
>
>