Is postgres COALESCE lazy?

Question

If I have a query like this:

SELECT COALESCE(
  (SELECT value FROM precomputed WHERE ...),
  alwaysComputeValue(...)
);

Will the second expression be evaluated? Also can this depends on execution planner or it is independent?

Answer 1

Conceptually it is lazy:

Like a CASE expression, COALESCE only evaluates the arguments that are needed to determine the result; that is, arguments to the right of the first non-null argument are not evaluated.

https://www.postgresql.org/docs/9.6/static/functions-conditional.html

However, if the expression to the right isn't volatile then it should make no difference whether it was lazy or not, so in such a case it would be allowable for the query planner eagerly evaluate the right-hand argument if it was stable or immutable, if this seemed a sensible optimisation.

An obvious case is that with SELECT COALESCE(a, b) FROM table it will likely retrieve the a and b fields of all the rows rather than retrieving a and then retrieving b if necessary.

About the only way to have any observable effect here is if you wrote a volatile function and deliberately mis-labelled it as stable or immutable. Then it would be possible for it to be evaluated if on the right-hand of a coalesce where the left-hand wasn't null. (It would be possible for a function that really was stable as well of course, but if it was stable it would have no side-effect, and if it had no side-effect whether it happened or not wouldn't be observable).

Given:

CREATE OR REPLACE FUNCTION immutable_func(arg integer)
RETURNS integer
AS $BODY$
BEGIN
    RAISE NOTICE 'Immutable function called with %', arg;
    RETURN arg;
END;
$BODY$ LANGUAGE plpgsql IMMUTABLE;

WITH data AS
(
    SELECT 10 AS num
    UNION ALL SELECT 5
    UNION ALL SELECT 20
)
select coalesce(num, immutable_func(2))
from data

The planner knows that it will have the same result for immutable_func(2) for every row and calls it a single time for the whole query, giving us the message Immutable function called with 2. So it has indeed been evaluated even though it is not within the rule of "arguments to the right of the first non-null argument are not evaluated". The pay-off is that in the (reasonable to expect) case of multiple null num it will still only have run that one time.

That this is against the letter of the documented behaviour is fine, because we've told it that such an optimisation is valid. If this caused a problem the bug would be in having the function marked as IMMUTABLE not in the eager evaluation.

It can also be part-way. With SELECT COALESCE(a, Some_Func(b)) FROM table it won't evaluated Some_Func(b) eagerly, but it will have retrieved b to be able to do so.

Any time that it actually impacts on (non-cheating) observable behaviour, the rule is followed.

Answer 2

From the documentation:

Like a CASE expression, COALESCE only evaluates the arguments that are needed to determine the result; that is, arguments to the right of the first non-null argument are not evaluated.