Re: delete is extremely slow

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.