You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
I have two, similar queries. In one of case have additional dummy where conditions (1=1, 0=0, true):
SELECT t1.*
FROM table1 t1
JOIN table2 t2 ON t2.fk_t1 = t1.id
JOIN table3 t3 ON t3.id = t1.fk_t3
WHERE
0 = 0 AND /* with this in 1st case, without this line in 2nd case */
t3.field = 6
AND EXISTS (SELECT 1 FROM table2 x WHERE x.fk2_t2 = t2.id)
All necessary fields are indexed.
For each case, Firebird works different, and statistics of reads see like this:
And plans (last line): PLAN JOIN (T2 NATURAL, T1 INDEX (T1_ID_IDX), T3 INDEX (T3_ID_IDX))
vs PLAN JOIN (T1 NATURAL, T3 INDEX (T3_ID_IDX1), T2 INDEX (T2_FK_T1_IDX))
Why different?
Affected version 2.1 to 3.0.
The text was updated successfully, but these errors were encountered:
Not exactly. There is about not optimize with always-false condition. Here I writing about change rules of execution after add always-true condition (nothing to change).
dyemanov
changed the title
Dummy conditions vs optimizer
Dummy (always true) conditions may change the join order
Sep 17, 2022
I have two, similar queries. In one of case have additional dummy where conditions (1=1, 0=0, true):
All necessary fields are indexed.
For each case, Firebird works different, and statistics of reads see like this:
1st case (with 0=0):
And in 2nd case (without dummy condition):
And plans (last line):
PLAN JOIN (T2 NATURAL, T1 INDEX (T1_ID_IDX), T3 INDEX (T3_ID_IDX))
vs
PLAN JOIN (T1 NATURAL, T3 INDEX (T3_ID_IDX1), T2 INDEX (T2_FK_T1_IDX))
Why different?
Affected version 2.1 to 3.0.
The text was updated successfully, but these errors were encountered: