Postgres has a problem with using an index in some cases, and I think it is causing a collection to be loaded slowly in one of my hibernate objects. The problem is described in the user comments at the bottom of the page here:
http://www.postgresql.org/docs/7.4/interactive/indexes.html

Has anyone else experienced a problem with this, and is there a workaround or fix? I'm currently using Hibernate 3.0.5 and also tried the most recent release candidate.

Yes, there are many way to tune PostgreSQL. Have you tried to view query plan (generated query with EXPLAIN prefix) ?

Is your performance issue only applied for the collection HQLs? Or is it a general issue?

If I manually type a query into psql it is as would be expected as described in the 'User Comments' section of the page

So something like
SELECT record WHERE record.an_id=11111
returns a query plan that doesn't use the index on 'an_id', whereas:
SELECT record WHERE record.an_id='11111'
returns a query that does use an index

The hibernate object I am having problems with has a collection that would generate a query something like those queries and is taking about 10 seconds. The query time for the slow version when I type it into psql is also around 10 seconds and the fast version is a fraction of a second. I don't believe it is a tuning problem, but rather a bug with postgres. What I'm wondering is if there is a workaround to get hibernate to query in the way that will use the faster version. Or perhaps hibernate has no control over it and that would be handled in the jdbc driver. I'm not sure.

Thanks,
Jason

The problem is only showing up in one particular collection, but that collections records come from a table that has over 500,000 records. So it might be that is the only place that it is noticable, since most of the other tables are smaller.

It looks like you are using "old" driver. If this is a "new" driver then wrapp it and implement PreparedStatement.setLong() this way :

void setLong(int index,long value){

delegate.setString(index,Long.toString(value));

}