Lubos Mikusiak ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) *created* an issue Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiOTk1OWM4MWQz... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-16595?atlOrigin=eyJpIjoiOTk1OW... ) HHH-16595 ( https://hibernate.atlassian.net/browse/HHH-16595?atlOrigin=eyJpIjoiOTk1OW... ) Different SQL queries for @ManyToMany relatinships in Hibernate 5 and 6 ( https://hibernate.atlassian.net/browse/HHH-16595?atlOrigin=eyJpIjoiOTk1OW... ) Issue Type: Bug Affects Versions: 6.1.7 Assignee: Unassigned Components: query-sql Created: 12/May/2023 02:55 AM Priority: Major Reporter: Lubos Mikusiak ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) 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? ( https://hibernate.atlassian.net/browse/HHH-16595#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-16595#add-comment?atlOrigin=ey... ) Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=Em... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100225- sha1:e4eb869 )