Re: bulk DELETE speed

* Kuhn, Dylan K (4520500D) <Dylan(dot)Kuhn(at)navy(dot)mil> [16.05.2003 20:31]:
>
> I'm trying to figure out why deletes are slow on a particular table.
>
> I have a few tables with millions of rows, and have noticed that deleting rows is many times slower on one of them. It does have PL/PGSQL before and after delete triggers. I've tried disabling the triggers in pg_trigger, dropping them, doing the deletes in transactions, with exclusive locks -- nothing seems to work. Maybe it isn't the triggers at all, and I need to do some sort of analysis on my table to figure out why it is slow?
>
> Does anyone have any other tricks for speeding up bulk deletes?

A couple of days ago I've found the same problem.

Check, if your table has a primary key (guess it does), thatis referenced by
any other tables. If so, for each row you're deleteing, backend first is
checking for existance of related ones in other tables.

In my case, those "othertables" wasn't indexed by the foreign key field.
That means - for each row to delete backend is performing seq. scan on some
other table. Even if it isn't so big, it still slows down the things.

Look around, may be this will help.

--

Victor Yegorov