Getting Hibernate to generate indexes on foreign keys

Question

I am using Hibernate 3.3.2 in a fairly large project with PostgreSQL 8.4 using the PostgreSQLDialect.

The way we have our relationships set up, we end up doing a lot of searching on the Foreign Key attributes of our tables.

For performance reasons, I would like to have Hibernate add Indexes to the all of the foreign key columns when we are creating our tables using hbm2dll.auto.

MySQL would automatically add Indexes to these columns, but there doesn't seem to be a way to do this in Postgres.

Is there an option I can set somewhere, or something I can add to my hbm.xml files to make this happen?

Answer 1

Hibernate only enforces indexcreation for MySQL, not for PostgreSQL, Oracle, etc. MySQL doesn't allow foreign keys without an index, other databases don't have this restriction.

Sybase has an explanation why it's a good thing not to enforce indexes, the Hibernate forum also has a topic about it (Oracle related) including a workaround. But before you start creating a lot of indexes, start checking if you need all these indexes. In many cases the indexes can be combined with others to speed things up. It all depends!

Use EXPLAIN on your queries to see how the database executes them, what indexes are used, where indexes are missing, etc.

Answer 2

Try either of:

<many-to-one name="master" class="Master" column="master_id" index="my_index_name" />

<many-to-one name="master" class="Master">
    <column name="master_id" index="my_index_name" />
</many-to-one>

Answer 3

Here's a quick-and-dirty query that would generate the DDL for indexes on each defined foreign key in a schema:

SELECT 'CREATE INDEX fk_' || conname || '_idx ON ' 
       || relname || ' ' || 
       regexp_replace(
           regexp_replace(pg_get_constraintdef(pg_constraint.oid, true), 
           ' REFERENCES.*$','',''), 'FOREIGN KEY ','','') || ';'
FROM pg_constraint 
JOIN pg_class 
    ON (conrelid = pg_class.oid)
JOIN pg_namespace
    ON (relnamespace = pg_namespace.oid)
WHERE contype = 'f'
  AND nspname = 'public';

This has only been tested in PostgreSQL 8.4, but I think it should work in most 8.x versions.

Also note that this doesn't detect which ones are already covered by an index so you would probably have some duplication.