Mix up of join order in criteria

Hibernate ORM
1

I have the problem that with Hibernate 5.1.17 + PostgreSQL JDBC driver 42.2.5, Hibernate seems to mix up the order of joins. The right order is important for performance.

The code:

    import org.hibernate.Criteria
...
    Criteria crit = session.createCriteria(EMSDataSet.class);

    crit.createAlias("recordInformation", "recInf", JoinType.LEFT_OUTER_JOIN);
    crit.createAlias("agencyInformation", "agencyInf", JoinType.LEFT_OUTER_JOIN);
... (lots of Joins)
    crit.add(SQLTools.getDependedDateCriterion("recInf.created", getFrom(), getTo()));
    ProjectionList proList = getQueryResultProjection(crit);
    crit.setProjection(proList);
    crit.setResultTransformer(Transformers.aliasToBean(QueryResult.class));
    List result = crit.list();

jields to the query

select this_.db_id as y0_, this_.protocolStatus as y1_, recinf1_.created as y2_, recinf1_.id as y3_, (lots of other columns)
from EMSDataSet this_
left outer join AgencyInformationType agencyinf2_ on 
this_.agencyInformation_db_id=agencyinf2_.db_id
... (lots of other joins)
left outer join RecordInformationType recinf1_ on this_.recordInformation_db_id=recinf1_.db_id
... (lots of other joins)
where (recinf1_.created>='2024-04-04' and recinf1_.created<'2024-04-14')
order by recinf1_.created desc, this_.db_id desc limit 100;

which is very slow (because it joins about 10 tables before the filter applies) - 3 seconds

In this case, the following query is indented:

select this_.db_id as y0_, this_.protocolStatus as y1_, recinf1_.created as y2_, recinf1_.id as y3_, (lots of other columns)
from EMSDataSet this_
left outer join RecordInformationType recinf1_ on this_.recordInformation_db_id=recinf1_.db_id
left outer join AgencyInformationType agencyinf2_ on this_.agencyInformation_db_id=agencyinf2_.db_id
... (lots of other joins)
where (recinf1_.created>='2024-04-04' and recinf1_.created<'2024-04-14')
order by recinf1_.created desc, this_.db_id desc limit 100;

In this case, PostgreSQL joins RecordInformationType at first, then filter, then joins the rest, which is much faster (200 ms).

Both results were verified with explain analyze from PostgreSQL.

Now the question: How can I ensure the order?

2

I’m guessing the additional joins are created to fetch EAGER associations in your entity mappings. To solve this, you can change the associations to LAZY and only fetch them when needed, or use explicit join fetches / EntityGraphs to specify when to load them.

3

All joins are made via createAlias explicitly. The columns are specified as a projection (being recordInformation there the first as well :wink: )

Additionally, even in the EMSDataSet class, the reference to the RecordInformation is the first.

Also, we need to be EAGER here, as this is part of an export function, so we need all information. LAZY will make way too many little queries I’m afraid…

4

I just noticed you’re using the old org.hibernate.Criteria, which is no longer supported, in favor of JPA’s Criteria APIs. What version of Hibernate are you using?

5

As written in the first post, Hibernate 5.1.17 (+ Java 8) is used atm.
Sadly, I can’t upgrade, because the application uses Tapestry 5.6.4, which is incompatible to Hibernate 5.2+. And upgrading Tapestry is not possible for now :frowning:

6

I’m sorry but Hibernate is a community project, and we cannot help you with issues regarding unsupported versions. Hibernate 5.1.17 has been released almost 6 years ago, and I’m not familiar with the internals of the project at that point, so I won’t be able to help you further.

The only recommendation I can make is to evaluate an upgrade path as soon as possible to one of the latest versions.