Nuno,
It's the foreign keys that point to this table are the problem, not the
foreign keys of the table itself. In other words, the child tables for
which the table you delete from is the parent... do you have indexes on
the foreign keys of the child tables which point to this table ? Those
indexes will help... on the other hand, I do have here a few tables
which are at the top of the FK chains, and deleting from them takes ages
even if all the child tables are indexed for the foreign keys... it all
depends on the size of the child tables, the number of them, if the
deletion cascades or not and if yes how many rows of the child tables
are deleted as well... My top table causes cascaded deletes in as much
as 30 other tables, and it actually causes deletion of lots of rows in
there, so I do expect it to work slow... maybe that's what you see too.
Here we actually do delete first from the child tables and then delete
from the parent, so the whole thing can be done in smaller transactions.
Cheers,
Csaba.