I'm not deleting all the records, but I could maybe copy the ones I want to keep into a temporary table. It looks like I have to somehow disable the foreign key constraints that reference the table before truncating it. Is this possible?
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.
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