Re: delete is extremely slow

Scott Marlowe wrote:

>On Fri, 2006-05-26 at 09:51, Emi Lu wrote:
>
>
>>Emi Lu wrote:
>>
>>
>>
>>>>>Tried to run:
>>>>>
>>>>>delete from tableA where id <600000;
>>>>>
>>>>>It is extremely slow.
>>>>>
>>>>>id is integer and also the primary key. tableA has around 90000
>>>>>records.
>>>>>
>>>>>(I do not want to run truncate since I have some foreign key constraint
>>>>>setup based on tableA. Besides not all data I want to delete; only part
>>>>>of them).
>>>>>
>>>>>
>>>>>
>>>>>
>>>>Since you have some FK constrains on A, so delete each line of A will
>>>>confirm that it is not depended, which involves a lookup on the
>>>>dependent
>>>>table. Do you have indexes built on these dependents?
>>>>
>>>>
>>>>
>>>No, I do not have indexes setup for those dependents.
>>>
>>>If I setup index for those columns, it will speed up the deletion, right?
>>>
>>>Thank you.
>>>
>>>
>>>
>>>
>>http://archives.postgresql.org/pgsql-general/2003-02/msg01615.php
>>
>>If deletioin is 80% data, will index helps?
>>
>>
>
>Yep. If your master table has 10,000 rows, and the dependent table has
>100,000 rows, and you're deleting half of the master table (5,000 rows)
>then you'll have 5,000 sequential scans of the 100,000 row dependent
>table.
>
>
What about the reverse, 90,000 in the master table , while 500 records
in each dependant tables?