Dear Gurus,
I don't know if it's intentional or not, but I can't really think of a good
argument about this being a 'feature', not a 'bug'.
I use PostgreSQL 7.2.1 and have a function call that has a 16 second
runtime. Timings I write at the end of my mail are the best I measured and
ratio reflects average times too, but not max due to fluctuating server
load. It seems that COALESCE(myfunc(),0) has almost twice the time of
calling myfunc() alone! However, using two COALESCE's in a CASE does not
double the time again.
--------------------
My questions are:
QUESTION #1: Is the way COALESCE works, just converting it into a CASE?
(CASE WHEN myfunc() IS NULL THEN 0 ELSE myfunc() END)
- It seems, see the test with next question.
QUESTION #2: What if myfunc() has side effects or its run can't be freely
repeated? e.g. it returns a random row or something?
- I tried and it seems it's true: a function that inserts a row and returns
a notnull value.
COALESCE (test_ins()) inserted 2 rows
COALESCE (test_ins(),0) inserted 2 rows
COALESCE (NULL, test_ins(),0) inserted 2 rows
COALESCE (test_ins(), NULL, 0) inserted 2 rows
COALESCE (1, test_ins(),0) inserted 0 rows
QUESTION #3: Does 7.3.1 have any improvements over 7.2.1 in this area?
- The test above yielded the same result in 7.3.1.
CONCLUSION: I think both the optimizer, both the side effects would be
happier if all of COALESCE's expressions were only calculated once.
G.
--
while (!asleep()) sheep++;
---------------------------- cut here ------------------------------
-- myfunc(int4) returns int4 that may be NULL.
SELECT myfunc(0) -- 16 sec.
SELECT myfunc(1) -- 17 sec.
SELECT COALESCE(myfunc(0),0) -- 28 sec.
SELECT COALESCE(myfunc(1),0) -- 29 sec.
SELECT CASE WHEN myfunc(0) IS NULL THEN 0 ELSE myfunc(0); -- 29 sec.
SELECT CASE WHEN 'a'='b' THEN COALESCE(myfunc(1),0)
ELSE COALESCE(myfunc(0),0); -- 29 sec.
---------------------------- cut here ------------------------------
