7.2.1: coalesce double-calls function?
От | SZŰCS Gábor |
---|---|
Тема | 7.2.1: coalesce double-calls function? |
Дата | |
Msg-id | 013101c2c386$c031d340$0a03a8c0@fejleszt2 обсуждение исходный текст |
Ответы |
Re: 7.2.1: coalesce double-calls function?
Re: 7.2.1: coalesce double-calls function? |
Список | pgsql-general |
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 ------------------------------
В списке pgsql-general по дате отправления: