Hibernate indexing for like expression

Question

I am using Hibernate 3.3.1 and PostgresQL 9.2.2 server. The tables for my application are generated by hibernate automatically and now i would like to do an optimization for a very often used "like" expression in a table wich looks that wy: "where path like 'RootFolder_FirstSubfolder%'"

by default hibernate only creates an index for the "id" column i defined via annotation.

Are there any recommendations how i could speedup my "like" expression using more indexes?
Thanks very much in advance for helping me

Kind regards

Shannon

Answer 1

Hibernate can use the Index annotation to automatically creating an additional index:

@org.hibernate.annotations.Index(name = "IDX_PATH")
private String path;

BUT it won't help since the created index is not suitable for like clauses.

Read the most upvoted answer here for a better solution. Unfortunately, it requires custom sql and AFAIK there is no easy way to integrate custom sql in script generated by hibernate schema update tool.

As an alternative to hibernate auto update: you can use a tool like liquibase to manage schema update. It requires more setup, but it gives you full control of schema update scripts.