> 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.