Re: Deletes and large tables

>This table has 3 foreign keys, but that should not matter during deletes.
>In addition, the tables being referred to are small, and should be in
>cache.
>
>
I'm talking about FK that point this table... Not FK defined for this
table that point to other table.  If Table A is referenced by 10 other
tables 10 referencial check are needed.


>There are no tables depending on it for references, so no dependent
>triggers should be running.
>
>Also, if this was a foreign key issue, I would expect I/O issues/bounds
>and not CPU.
>
>
Maybe... I'm honetly not sure.

Like I said in my previous mail... I got a similar problem (maybe not
the same).  It was taking 10 minutes to delete 10k line in a table.  I
turn on some log info in postgresql.conf and I saw that for each row
deleted 4 selects were issued to check FK.  I drop those FK and the
after the delete was taking less than a second.

 Hope it help
/David



>Regards!
>Ed
>
>
>On Fri, 10 Jun 2005, Richard Huxton wrote:
>
>
>
>>Edmund Dengler wrote:
>>
>>
>>>Greetings!
>>>
>>>We have a table with more than 250 million rows. I am trying to delete the
>>>first 100,000 rows (based on a bigint primary key), and I had to cancel
>>>after 4 hours of the system not actually finishing the delete. I wrote a
>>>script to delete individual rows 10,000 at a time using transactions, and
>>>am finding each individual delete takes on the order of 0.1 seconds to 2-3
>>>seconds. There are 4 indexes on the table, one of which is very "hashlike"
>>>(ie, distribution is throught the index for sequential rows).
>>>
>>>
>>I don't suppose it's off checking foreign-keys in a lot of tables is it?
>>
>>--
>>   Richard Huxton
>>   Archonet Ltd
>>
>>
>>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>      subscribe-nomail command to majordomo@postgresql.org so that your
>      message can get through to the mailing list cleanly
>
>
>