Wow! That was almost instantaneous. I can't believe the difference. The only inconvenience is that I need to remove all the foreign key constraints before truncating, then put them back after. But I suppose it is a small price to pay for this incredible optimization.
In that case, your DELETE might have been slowed down by foreign key checks.
Suppose you have tables A and B, and table A has a column "b_id REFERENCES B(id)" When you delete from B postgres has to lookup in A which rows reference the deleted rows in order to do the ON DELETE action you specified in the constraint. If you do not have an index on b_id, this can be quite slow... so you should check if your foreign key relations that need indexes have them.
