Poorly rendered SQL resulting in extremely slow MySQL 8.0 queries

[rjjb]

Hello,

Apologies for the wall of text, but I am hoping someone can help with this issue and, ideally, please release a fix for Hibernate 6.6 as soon as possible. Providing as much relevant information as I can.

Upgrading from 5.6 to 6.6 has resulted in some extremely slow running queries despite perfectly reasonable HQL or even simply fetching a class from the session by ID.

The problem is of this pattern, which occurs across many of our entities as a result of several different but even trivial HQL and entity patterns that all rendered efficient SQL in 5.6:

from ClassB cb1_0
join (select * 
      from AbstractClass t 
      where t.DTYPE in (‘AA’, ‘AB’, ‘AC’)) t1_0 
  on t1_0.id=cb1_0.abstractClass_id
join …

In some cases, it’s a OneToOne relationship and I was able to work around the issue by moving the table with a discriminator column into the from clause instead of the join. For example, this produces good SQL:

from AbstractClass ac
join ac.oneToOneClass otoc

Whereas this produces the subselect:

from OneToOneClass otoc
join otoc.abstractClass

In some cases, I was able to work around the issue by setting a OneToOne or ManyToOne relationship to FetchType=LAZY, introducing otherwise unnecessary and undesirable n+1 problems that are nevertheless still 95-99.9% faster because the subselect is just that bad.

In some cases, I was able to change

select classC
from ClassA classA 
join classA.classB.classC classC 
where ...

to

select classA.classB.classC
from ClassA classA
where ...

and this removed the subselect.

In some cases, I think I have no choice but to rewrite numerous queries as native SQL where these subselects are being rendered from joins that I cannot move them from, such as where I’m retrieving a DTO projection, which feels awful and defeats the purpose of the otherwise simple projection.

---

Somewhat obfuscated explains below showing the difference in performance for a result set of <150 rows is well over a minute vs ~40 milliseconds. This is bad even on tables with 40k rows in the derived table where it actually selects a key because it’s such that it will get loaded many many times and the relatively minor (to the 15 mil table) overhead adds up to be very significant.

Explain with the subselect:

+----+-------------+------------+------------+--------+------------------------------------------------------------------------------------+------------------------------+---------+--------------------+----------+----------+------------------------------+
| id | select_type | table      | partitions | type   | possible_keys                                                                      | key                          | key_len | ref                | rows     | filtered | Extra                        |
+----+-------------+------------+------------+--------+------------------------------------------------------------------------------------+------------------------------+---------+--------------------+----------+----------+------------------------------+
|  1 | PRIMARY     | p1_0       | NULL       | ref    | PRIMARY,UK_asdfasdfasdfasdfasdfasdfa                                               | UK_asdfasdfasdfasdfasdfasdfa | 4       | const              |        1 |   100.00 | Using index; Using temporary |
|  1 | PRIMARY     | ba1_0      | NULL       | ref    | PRIMARY,FK_1234aasdfasdfasdfasdfasdf                                               | FK_1234aasdfasdfasdfasdfasdf | 4       | db.p1_0.id         |        6 |   100.00 | Using index                  |
|  1 | PRIMARY     | tt1_0      | NULL       | ref    | UK_87asdfasdfasdfasdfasdfff,IX_ClassA_field,FK_ClassA_field                        | FK_ClassA_field              | 4       | db.ba1_0.id        |     5678 |    23.32 | Using where                  |
|  1 | PRIMARY     | <derived2> | NULL       | ref    | <auto_key1>                                                                        | <auto_key1>                  | 4       | db.tt1_0.classc_id |       15 |   100.00 | NULL                         |
|  1 | PRIMARY     | s1_0       | NULL       | eq_ref | PRIMARY                                                                            | PRIMARY                      | 4       | t1_0.classd_id     |        1 |   100.00 | NULL                         |
|  2 | DERIVED     | t          | NULL       | ALL    | IX_ClassC_field,ClassC_DTYPE_index                                                 | NULL                         | NULL    | NULL               | 15420167 |    34.29 | Using where                  |
+----+-------------+------------+------------+--------+------------------------------------------------------------------------------------+------------------------------+---------+--------------------+----------+----------+------------------------------+

Explain with the same query but a logically simplified join in place of the subselect:

+----+-------------+-------+------------+--------+------------------------------------------------------------------------------------+------------------------------+---------+----------------------+------+----------+------------------------------+
| id | select_type | table | partitions | type   | possible_keys                                                                      | key                          | key_len | ref                  | rows | filtered | Extra                        |
+----+-------------+-------+------------+--------+------------------------------------------------------------------------------------+------------------------------+---------+----------------------+------+----------+------------------------------+
|  1 | SIMPLE      | p1_0  | NULL       | ref    | PRIMARY,UK_asdfasdfasdfasdfasdfasdfa                                               | UK_asdfasdfasdfasdfasdfasdfa | 4       | const                |    1 |   100.00 | Using index; Using temporary |
|  1 | SIMPLE      | ba1_0 | NULL       | ref    | PRIMARY,FK_1234aasdfasdfasdfasdfasdf                                               | FK_1234aasdfasdfasdfasdfasdf | 4       | db.p1_0.id           |    6 |   100.00 | Using index                  |
|  1 | SIMPLE      | tt1_0 | NULL       | ref    | UK_87asdfasdfasdfasdfasdfff,IX_ClassA_field,FK_ClassA_field                        | FK_ClassA_field              | 4       | db.ba1_0.id          | 5678 |    23.32 | Using where                  |
|  1 | SIMPLE      | t1_0  | NULL       | eq_ref | PRIMARY,IX_ClassC_field,FK_21yasdfakjsdfgasdhfghsajd                               | PRIMARY                      | 4       | db.tt1_0.classc_id   |    1 |    50.00 | Using where                  |
|  1 | SIMPLE      | s1_0  | NULL       | eq_ref | PRIMARY                                                                            | PRIMARY                      | 4       | db.t1_0.classd_id    |    1 |   100.00 | NULL                         |
+----+-------------+-------+------------+--------+------------------------------------------------------------------------------------+------------------------------+---------+----------------------+------+----------+------------------------------+

---

Ref/related:
Another subselect instead of join issue

the subselect is needed to ensure correct join semantics when using discriminator-based inheritance
– mbladel

The subselect simplifies logically, regardless of semantics that Hibernate would like to maintain, to:

join AbstractClass t1_0
  On t1_0.id=cb1_0.abstractClass_id

Explicitly restricting by DTYPE only to then restrict that resulting derived table by ID makes absolutely zero sense to me. Hibernate has all the information it needs to render the correct and simplified form of SQL at the time it sends it to the database and is going out of its way to generate poor performing SQL. What it receives back is guaranteed to be of the correct DTYPE in both cases because it’s restricted by a unique primary/foreign key pair supplied by the joined entities. MySQL doesn’t care about Hibernate’s underlying/internal semantics.

… we need actual evidence that this makes a difference. From a pure relational perspective it does not make any sense that the performance is different, because filters can be pushed down into table scans across inner joins. Every database leverages this optimization, otherwise the performance of many queries, including ones that rely on SQL views would suffer dramatically.
– beikov

I do not believe it is appropriate to assume that a database will optimize this unnecessary (and explicitly requested) derived table on Hibernate’s behalf. In fact, MySQL 8.0.41 (at least in the environment that I’m testing against) is not doing so and the difference in performance is quite literally staggering. I need to upgrade in stages, so even if this is fixed in Mysql 8.4/9, Hibernate 7, or whatever, that won’t help quite yet.

I don’t know if there are MySQL/Hibernate settings I can apply to help, or have applied historically that are causing this lack of down-pushing filters mentioned. But this should be irrelevant given that Hibernate is intentionally sending poorly rendered SQL to the database, explicitly requesting an unnecessary derived table, and should just stop doing that.

---

Workarounds aside, my general expectation of Hibernate is that it produces SQL in its most simplified/optimized form possible and not rely on a database to fix redundant/non-performant SQL. It has been generally great until this most unexpected migration blocker.

Is there something I’m missing, Hibernate fix aside, that could help get performance back as it was (and better, in some of the few cases I have already fixed by “tricking” Hibernate as above)?

Any help with this would be greatly appreciated, thank you.

[rjjb]

I did find a legacy derived_merge=off setting blindly carried over from MySQL 5.7 so this may no longer be a blocker if it tests well after removing that setting and reverting some of the query changes I detailed above.

Though I’d still like to see Hibernate render and pass clean SQL to the database. I should have the option to explore database-side optimizations for a particular code base without being forced into a setting unnecessarily.

[beikov]

@rjjb

Explicitly restricting by DTYPE only to then restrict that resulting derived table by ID makes absolutely zero sense to me.

@rjjb

I don’t know if there are MySQL/Hibernate settings I can apply to help, or have applied historically that are causing this lack of down-pushing filters mentioned. But this should be irrelevant given that Hibernate is intentionally sending poorly rendered SQL to the database, explicitly requesting an unnecessary derived table, and should just stop doing that.

You can freely move out the DTYPE restriction in this particular case, because you seem to be using an inner join based on a unique key of the join-table. This is the kind of optimization that Hibernate ORM has to bake into it’s SQL rendering, because apparently, MySQL and maybe other databases are not capable to this sort of very basic relational algebra optimization.

I did find a legacy derived_merge=off setting blindly carried over from MySQL 5.7 so this may no longer be a blocker if it tests well after removing that setting and reverting some of the query changes I detailed above.

Are you telling me that you explicitly disabled this optimization in your environment and now it works fine after enabling it again?

@rjjb

Though I’d still like to see Hibernate render and pass clean SQL to the database. I should have the option to explore database-side optimizations for a particular code base without being forced into a setting unnecessarily.

I don’t disagree with you, it would indeed be nice if Hibernate ORM could produce cleaner SQL if possible. I hope you better understand now though, that some join conditions or join types would produce wrong results when using this “simplified rendering”.

Now, what can you do to help improve things?

1. Create test cases that exercise scenarios that should be optimized based on our template
2. Create an improvement ticket in our issue tracker and attach the tests
3. Try working on this optimization and open a PR against the Hibernate ORM repository

[rjjb]

Thanks much for the response. Working through my ignorance has probably clouded the issue somewhat but led to me understanding things much better. It also shines a light on the rendered SQL, which in my opinion is still somewhat of an issue.

@beikov

You can freely move out the DTYPE restriction in this particular case, because you seem to be using an inner join based on a unique key of the join-table. This is the kind of optimization that Hibernate ORM has to bake into it’s SQL rendering, because apparently, MySQL and maybe other databases are not capable to this sort of very basic relational algebra optimization.

I detailed above some ways I could move the DTYPE restriction out of the derived table pattern to the outer part of the query but this isn’t possible in all cases and they were still redundantly present (but performant). MySQL does optimize the SQL somewhat if not configured to the contrary, though the explains are not quite the same as if a simple inner join were passed to it.

@beikov

Are you telling me that you explicitly disabled this optimization in your environment and now it works fine after enabling it again?

Yes, the derived merge optimization was explicitly disabled quite some time ago and worked well on the older version of Hibernate. The same queries perform well on Hibernate 6.6 only after enabling it again, but this doesn’t fix the underlying issue so much as reveal it.

@beikov

I don’t disagree with you, it would indeed be nice if Hibernate ORM could produce cleaner SQL if possible. I hope you better understand now though, that some join conditions or join types would produce wrong results when using this “simplified rendering”.

I really don’t know of any such cases, actually, short of explicitly asking for a full set of that abstract type. Unless I missed some, everything I saw performing poorly in our context was restricting by DTYPE in an explicitly requested derived table, and then the result of that by ID provided by the object model’s implicit joins. The behaviour was entirely under the hood, in Hibernate’s control. I could only slightly tweak some HQL (but not others) such that Hibernate rendered the DTYPE restriction in a more performant location despite the same required joins.

The problem, as I see it, is not so much a coding bug/oversight as it is an intentional anti-feature or over-applied ideal.

• Hibernate should not rely on database optimizations to correct its rendered SQL
• Hibernate should keep its semantics contained within its own boundaries whenever possible
• Hibernate should render the same quality of SQL as would be written by hand, most especially in the simplest cases.

If I saw this in an interview response, I’d think I was being trolled or it was AI-generated:

from ClassB cb1_0
join (select * 
      from AbstractClass t 
      where t.DTYPE in (‘AA’, ‘AB’, ‘AC’)) t1_0 
  on t1_0.id=cb1_0.abstractClass_id
join …

So perhaps, if there are concrete cases where the derived table would matter, these cases can be detected and rendered as necessary? But the more typical cases can be rendered simply as:

from ClassB cb1_0
join AbstractClass t1_0
  on t1_0.id=cb1_0.abstractClass_id
join ...

All this from HQL as simple as:

from ClassB cb
join cb.abstractClass

or even, as I recall, where AbstractClass is eagerly fetched from within ClassB:

session.get(ClassB.class, id)

Anyways, I have pretty much moved on at this point. As this is all probably more in the realm of would be nice than necessity, perhaps it can just be kept in mind during any future renderer changes/discussions?

Thank you.