Don't forget to index your PostgreSQL foreign keys

One thing that surprised me with PostgreSQL after spending a lot of time with MySQL is that setting foreign key constraints do not automatically generate indexes on those columns. PostgreSQL will implicitly generate indexes for primary keys, but not foreign keys. Since those foreign key columns are precisely the ones you tend to use for joins (either explicitly or implicitly by ORMs like Hibernate), omitting indexes could result in a serious performance hit. Creating indexes is easy enough, whether using the usual SQL command or a Hibernate @Index annotation, but where do you start? Well, the whole point of this blog entry is to point you to another blog entry. It provides a handy query to look for foreign keys that could use indexes, ranked by the ones most likely to need an index. Take a look:

Postgresql: Indexes on Foreign Keys