In Hibernate 5, two “cascading“ joins are generated for many-to-many relationships. In Hibernate 6, a join with a nested join is generated instead. I observed much longer response times for some queries generated by Hibernate 6. In Postgres, the execution plans are different. Here is an example that illustrates this
@Entity
@Table(name = "entity_1")
public class Entity1 {
@Id
private Long id;
@ManyToMany
@JoinTable(name = "join_table",
joinColumns = @JoinColumn(name = "entity_1_id", referencedColumnName = "id"),
inverseJoinColumns = @JoinColumn(name = "entity_2_id", referencedColumnName = "id"))
private Set<Entity2> relatedEntities2;
}
@Entity
@Table(name = "entity_2")
public class Entity2 {
@Id
private Long id;
private String naturalId;
}
When executing
@Query("select entity1" +
" from Entity1 entity1" +
" inner join entity1.relatedEntities2 as relatedEntities2" +
" where relatedEntities2.naturalId = :naturalId")
List<Entity1> findAllByEntity2NaturalId(String naturalId);
Hibernate 5 generates
select
entity1x0_.id as id1_0_
from
entity_1 entity1x0_
inner join
join_table relatedent1_
on entity1x0_.id=relatedent1_.entity_1_id
inner join
entity_2 entity2x2_
on relatedent1_.entity_2_id=entity2x2_.id
where
entity2x2_.natural_id=?
Hibernate 6 generates
select
e1_0.id
from
entity_1 e1_0
join
(join_table r1_0
join
entity_2 r1_1
on r1_1.id=r1_0.entity_2_id)
on e1_0.id=r1_0.entity_1_id
where
r1_1.natural_id=?
In dataset that work with, the Hibernate 6 version of the queries results often in full table scans while Hibernate 5 versions use indices. The queries on that dataset are much more complex though than this simple example. Questions
- Was it a conscious decision to use different structure of the join clauses or a side effect of introducing SQM?
- If conscious decision, is it possible to use a hint with Hibernate 6 that results in a query with two “cascading“ joins?
|
