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?

score 3 · Answer 1 · answered Jun 04 '10 at 17:57

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.

answered Jun 04 '10 at 17:57

Matthew Wood

16,017
5
46
35

Once the database is set up, this is helpful, but for this question, I was really more interested in getting hibernate to do it for me, when it creates the tables. This is a great answer for my other, related question though: http://stackoverflow.com/questions/2970050/postgresql-how-to-index-all-foreign-keys – biggusjimmus Jun 04 '10 at 18:56
i think, it does not work on quoted named variables. – ravshansbox Feb 18 '15 at 18:29
@Ravshan: Ignoring for the moment that one shouldn't use quoted names in database table definitions, this appears to work for me in 9.3: select distinct regexp_replace(regexp_replace(pg_get_constraintdef(oid), '^.*REFERENCES', 'CREATE INDEX ON'), '\).*$', ');') from pg_constraint where contype = 'f'. – Matthew Wood Feb 19 '15 at 23:52
sometimes it's unavoidable using quoted identifiers (at least for legacy purposes). – ravshansbox Feb 21 '15 at 08:15

score 2 · Answer 2 · answered Jun 04 '10 at 06:51

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.

It makes good sense not to *force* foreign keys to have an index, but it seems like it would be a good option to allow. — biggusjimmus, Jun 04 '10 at 16:22

Roben · Answer 3 · 2012-06-21T13:52:33.760

1

In addition to Matthew's answer: Adding

AND NOT EXISTS (
  SELECT * FROM pg_class pgc
    JOIN pg_namespace pgn ON (pgc.relnamespace = pgn.oid)
  WHERE relkind='i'
    AND pgc.relname = ('fk_' || conname || '_idx') )

right before the semicolon only creates indexes if they don't exist. This allows you to repeatedly executing the statement without producing "relation fk_..." already exists" errors.

edited Jun 21 '12 at 13:52

answered Jun 21 '12 at 13:28

Roben

840
9
19

score 1 · Answer 4 · answered Jun 04 '10 at 07:01

1

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>

answered Jun 04 '10 at 07:01

Konrad Garus

53,145
43
157
230

I tried both of these, but searching the database for the indexes that should have been created yields no results =\ Not sure what I might be doing wrong, but will keep plugging at it. – biggusjimmus Jun 04 '10 at 17:48
What setting are you using it with? Maybe they aren't added with "update", but would be with "create" or "create-drop"? Another idea, can it be a bug like this one: http://opensource.atlassian.com/projects/hibernate/browse/HHH-1012 – Konrad Garus Jun 05 '10 at 08:24

Getting Hibernate to generate indexes on foreign keys

4 Answers4