Re: bulk DELETE speed

bulk DELETE speedIf you can delete all records, try truncating the table:

truncate table your_table;

This is more efficient and also has the affect of resetting the table.
-----Original Message-----
From: pgsql-admin-owner(at)postgresql(dot)org
[mailto:pgsql-admin-owner(at)postgresql(dot)org]On Behalf Of Kuhn, Dylan K
(4520500D)
Sent: Friday, May 16, 2003 12:15 PM
To: pgsql-admin(at)postgresql(dot)org
Subject: [ADMIN] bulk DELETE speed

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?

Thanks,
Dylan Kuhn