And something I had to learn the hard way and then teach quite a few people is that hard deletes don’t just turn your tables into Swiss cheese, they also can cause table scans.
When you delete a row, every inbound foreign key constraint has to be checked to look for any rows that refer to the deleted row, and most likely you didn’t set up an index for the foreign key, so now you have a table scan. Possibly several.
It’s not that much more work these days to set up a partial index on the table instead and add another WHERE clause, you have smaller problems with people accidentally deleting the wrong thing, and you’ve started down the path to audit trails.
It's been standard practice at all the companies I've worked at to index all foreign key fields, no matter what they're used for, I've yet to run into a situation where it's been more harmful than helpful, but these companies all had <10TB data in SQL so idk if it's good general advice.
When you delete a row, every inbound foreign key constraint has to be checked to look for any rows that refer to the deleted row, and most likely you didn’t set up an index for the foreign key, so now you have a table scan. Possibly several.
It’s not that much more work these days to set up a partial index on the table instead and add another WHERE clause, you have smaller problems with people accidentally deleting the wrong thing, and you’ve started down the path to audit trails.