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
