According to Craig Ringer:
While it's usually a good idea to create an index on (or including) your referencing-side foreign key columns, it isn't required. Each index you add slows DML operations down slightly, so you pay a performance cost on every
DELETE. If the index is rarely used it may not be worth having.
How do you determine if the benefit of adding an index surpasses its cost?
Do you profile unit tests before/after adding an index and check for an overall performance gain? Or is there a better way?