Re: delete is extremely slow

On Fri, 2006-05-26 at 09:57, Emi Lu wrote:
> 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?

basically, for every row you delete in the master table, you'll have to
sequentially scan every row in the dependent table(s) for each, if you
don't have an index on the dependent table.