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?

  • pg_stat_user_indexes helps guide you with this, showing information about index usage. To estimate costs of maintenance you can look at your table write activity in pg_stat_user_tables but because of HOT, not all updates will necessarily have to touch the index so you might overestimate a little. – Craig Ringer Nov 25 '14 at 6:43
  • @CraigRinger, that's very helpful. Thank you! – Gili Nov 25 '14 at 7:34
  • I rely on pgBadger almost exclusively. And my gut. – Colin 't Hart Nov 25 '14 at 9:49

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

  • 4
    Rule of thumbs are nice, but too general in this case. I am looking for a concrete tool/measurement that will suggest adding/removing indexes based on actual runtime behavior of my application. – Gili Nov 25 '14 at 6:00
  • Maybe this is the tool you are looking for: enterprisedb.com/products-services-training/products/… – Mladen Uzelac Nov 25 '14 at 6:41

Pick rules of thumb and follow them. For example, I always create an index on the referencing side of a foreign key.

Make sure you performance test your product against performance criteria based on user needs. For example, the system needs to handle an incoming load of 100 POST request type X's per minute.

If you cannot meet your performance requirements then refine your model, possibly breaking your rules of thumb in that process.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.