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  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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 по дате отправления:

Предыдущее
От: Doug McNaught
Дата:
Сообщение: Re: Selecting random row
Следующее
От: "C. Miller"
Дата:
Сообщение: Import from MS SQL Server?