Join ordering

[Mark Stobbe <ma...@gmail.com>]

Hi all,

We are running into an issue with the specific join ordering that Cayenne (
4.2.M3) does. We are using a ColumnSelect to select multiple columns across
different tables with a simple where statement.

SELECT * FROM my_table t0 LEFT JOIN my_detail1 t1 ON t0.fk_one = t1.id LEFT
JOIN my_detail2 t2 ON t0.fk_two = t2.id LEFT JOIN my_detail3 t3 ON
t0.fk_three = t3.id LEFT JOIN my_detail4 t4 ON t0.fk_four = t4.id
... JOIN my_children t12 ON t0.id = t12.fk_parent WHERE ( ((t12.c_from >=
'2023-05-16 10:00:00' AND t12.c_from <= '2023-05-17 10:00:00') OR (t12.c_to
>= '2023-05-16 10:00:00' AND t12.c_to <= '2023-05-17 10:00:00'))
)
...
LIMIT 25

This query takes approx. 30 seconds
where as if I manually change the ordering of the joins to:

SELECT * FROM my_table t0
JOIN my_children t12 ON t0.id = t12.fk_parent LEFT JOIN my_detail1 t1 ON
t0.fk_one = t1.id LEFT JOIN my_detail2 t2 ON t0.fk_two = t2.id LEFT JOIN
my_detail3 t3 ON t0.fk_three = t3.id LEFT JOIN my_detail4 t4 ON t0.fk_four
= t4.id
... WHERE ( ((t12.c_from >= '2023-05-16 10:00:00' AND t12.c_from <=
'2023-05-17 10:00:00') OR (t12.c_to >= '2023-05-16 10:00:00' AND t12.c_to
<= '2023-05-17 10:00:00'))
)
...
LIMIT 25

Then the query returns in a few milliseconds.
Can we somehow indicate the join ordering or at least prefer joins from the
where to go before the joins needed for the select ?

Mark

Re: Join ordering

[Andrus Adamchik <aa...@gmail.com>]

Hi Mark,

SQL translator in 4.2 is highly customizable via SQLTreeProcessor (returned from DbAdapter). So can you rearrange your joins with a custom adapter / tree processor.

Before you do that, please confirm whether this is still a problem with a newer version of Cayenne. Either with 4.2.RC2 (released) or the 4.2 final (upcoming, temporarily available at [1], and soon to be promoted to Central.

Andrus

[1] https://repository.apache.org/content/repositories/orgapachecayenne-1052/


> On May 21, 2023, at 1:19 AM, Mark Stobbe <ma...@gmail.com> wrote:
> 
> Hi all,
> 
> We are running into an issue with the specific join ordering that Cayenne (
> 4.2.M3) does. We are using a ColumnSelect to select multiple columns across
> different tables with a simple where statement.
> 
> SELECT * FROM my_table t0 LEFT JOIN my_detail1 t1 ON t0.fk_one = t1.id LEFT
> JOIN my_detail2 t2 ON t0.fk_two = t2.id LEFT JOIN my_detail3 t3 ON
> t0.fk_three = t3.id LEFT JOIN my_detail4 t4 ON t0.fk_four = t4.id
> ... JOIN my_children t12 ON t0.id = t12.fk_parent WHERE ( ((t12.c_from >=
> '2023-05-16 10:00:00' AND t12.c_from <= '2023-05-17 10:00:00') OR (t12.c_to
>> = '2023-05-16 10:00:00' AND t12.c_to <= '2023-05-17 10:00:00'))
> )
> ...
> LIMIT 25
> 
> This query takes approx. 30 seconds
> where as if I manually change the ordering of the joins to:
> 
> SELECT * FROM my_table t0
> JOIN my_children t12 ON t0.id = t12.fk_parent LEFT JOIN my_detail1 t1 ON
> t0.fk_one = t1.id LEFT JOIN my_detail2 t2 ON t0.fk_two = t2.id LEFT JOIN
> my_detail3 t3 ON t0.fk_three = t3.id LEFT JOIN my_detail4 t4 ON t0.fk_four
> = t4.id
> ... WHERE ( ((t12.c_from >= '2023-05-16 10:00:00' AND t12.c_from <=
> '2023-05-17 10:00:00') OR (t12.c_to >= '2023-05-16 10:00:00' AND t12.c_to
> <= '2023-05-17 10:00:00'))
> )
> ...
> LIMIT 25
> 
> Then the query returns in a few milliseconds.
> Can we somehow indicate the join ordering or at least prefer joins from the
> where to go before the joins needed for the select ?
> 
> Mark