b1 OR b2 <-> ( CASE WHEN b1 THE true ELSE b2 END ): performance bottleneck on logical OR
От | Cédric Dufour |
---|---|
Тема | b1 OR b2 <-> ( CASE WHEN b1 THE true ELSE b2 END ): performance bottleneck on logical OR |
Дата | |
Msg-id | NDBBIFNBODNADCAOFDOAIEJBCDAA.cedric.dufour@freesurf.ch обсуждение исходный текст |
Ответы |
Re: b1 OR b2 <-> ( CASE WHEN b1 THE true ELSE b2 END ): performance bottleneck on logical OR
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: