May I share my frustration. GORM (and Grails..) seems to have a VERY limited documentation regarding database indices. I haven't found any help from anywhere on how to create an index for a domain class when I want the index to be something more than just what has been documented here: http://grails.org/doc/latest/guide/GORM.html.
Here's in SQL what I would like to achieve the Grails' way:
CREATE INDEX very_fast_index
ON slow_table(date DESC NULLS LAST)
WHERE is_latest = true;
Seems like I could tell GORM to create an index for the date column. But looks like there's ZERO options for adding the other criteria.
As I hate when simple things are made extremely complicated, I've created these indices manually in the PostgreSQL CLI. Not from Grails, which would be more portable. I do not want to write any HQL, as I don't like that idea either.
Any ideas? I've got none other than the manual way.
HQL is a Data manipulation language non a Data definition language so is not useful for your needs. If you want to use database vendor specific features you have to bypass hibernate and use the lower level jdbc connection to do SQL querys. In grails you can use dataSource bean to make query the groovy way. Of course this tight you to a specific database (in your case Postgres).