Re: bulk DELETE speed

-----Original Message-----
From: Marie G. Tuite [mailto:marie.tuite@edisonaffiliates.com]
Sent: Friday, May 16, 2003 11:16
To: Kuhn, Dylan K (4520500D); pgsql-admin@postgresql.org
Subject: RE: [ADMIN] bulk DELETE speed

If 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@postgresql.org [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Kuhn, Dylan K (4520500D)
Sent: Friday, May 16, 2003 12:15 PM
To: pgsql-admin@postgresql.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