Testing and optimizing queries on large tables (1mio rows), I used two
different ways to obtain a logical OR expression:
1. exp1 OR exp2
2. ( CASE WHEN exp1 THE true ELSE exp2 END )
And 2. proved to be twice quicker as 1. in the ideal case where exp1 is
always true !!!
This tends to prove that the normal OR expression evaluates both left and
right expression, though evaluating the right expression is useless provided
the left expression is true.
This also leads to some programming complication, as for example when
writing triggers:
IF ( ( TG_OP = 'INSERT' ) OR ( ( TG_OP = 'UPDATE' ) AND ( old.X !=
new.X ) ) ) THEN
-- Do actions depending on field X when inserted or when **changed** (thus
avoiding useless action if field X didn't change)
END IF;
According to high-level programming language, one would expect this IF-THEN
expression to work... but it doesn't, because if ( TG_OP = 'INSERT' ) is
true, the right part of the OR expression still gets evaluated and an error
is raised, since 'old' variable is not defined for INSERT action.
This sounds rather trivial, but shouldn't the query optimizer somehow avoid
this un-necessary evaluation (and behave just as C, Java or other
programming language do) ?
Cédric Dufour
