bad performance of postgres query if on-clause of inner-join is positioned after following left-joins

64 Views Asked by At

Background: Hibernate 6 creates some generated queries different than hibernate 5.

In hibernate 5 a query looked like this (simplified):

select *
from document
inner join row on document.id = document_id
inner join region on row.id = row_id
inner join element on region.id = region_id
left join element_01 on element.id = element_01.id
left join element_02 on element.id = element_02.id
left join element_03 on element.id = element_03.id
left join element_04 on element.id = element_04.id
left join element_05 on element.id = element_05.id
left join element_06 on element.id = element_06.id
left join element_07 on element.id = element_07.id
left join element_08 on element.id = element_08.id
left join element_09 on element.id = element_09.id
left join element_10 on element.id = element_10.id
where document.id = $1;

In hibernate 6 it looks like this (simplified):

select *
from document
join row on document.id = document_id
join region on row.id = row_id
join (element
left join element_01 on element.id = element_01.id
left join element_02 on element.id = element_02.id
left join element_03 on element.id = element_03.id
left join element_04 on element.id = element_04.id
left join element_05 on element.id = element_05.id
left join element_06 on element.id = element_06.id
left join element_07 on element.id = element_07.id
left join element_08 on element.id = element_08.id
left join element_09 on element.id = element_09.id
left join element_10 on element.id = element_10.id
) on region.id = region_id
where document.id = $1;

In the hibernate 6 query, the on-clause of the last inner-join is positioned after the following left-joins. The results are the same, but the query-plans look very different.

The real query uses a lot more element-tables than the simplified query. All used tables have been analyzed before query execution.

Hibernate 5: https://explain.depesz.com/s/PrWV https://explain.dalibo.com/plan/98295ae6h4dhb815

Hibernate 6: https://explain.depesz.com/s/DqrU https://explain.dalibo.com/plan/fa448caafde141e9

I would have thought that the planner should be able to optimize the second query, to have the same performance as the first one, but it is not.

Currently there is no option to force hibernate 6 to use the old behaviour.

Is there anything i can do to force the planner to evaluate the last inner-join on-clause before the left-joins without changing the query?

We are working with Postgres 11, but i tried Postgres 14 and had the same results.

0

There are 0 best solutions below