How to determine cost/benefit of adding an index?

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 INSERT, UPDATE or 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?

Index usage depends on the structure of you data and the selectivity of you data. If you make queries that you don't use them they are expensive. If you use them effectively they are great tool. There is a query planner and query planner analytics(EXPLAIN) of just planning or planning and executuion of commands (EXPLAIN ANALYZE).

And there is a great online book: use the index luke about recommended usage of indexes

StackExchange Database Administrators Q#83541, answer score: 4