Thread: 7.2.1: coalesce double-calls function?

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 ------------------------------

--- SZ�CS G�bor <surrano@mailbox.hu> wrote:
> 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.
>

Perhaps this is just avoiding the issue (if indeed
there is an issue), but why not just define myfunc()
to return zero instead of null?

__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

"=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?=" <surrano@mailbox.hu> writes:
> CONCLUSION: I think both the optimizer, both the side effects would be
> happier if all of COALESCE's expressions were only calculated once.

Yup, and NULLIF too.  Want to submit a patch?  Look for CaseExpr in the
sources, copy/paste/modify.

            regards, tom lane

HAR HAR HAR! :)

Thanks for your kindness, I'll have a look at it....

... but don't hold your breath!

HAR HAR...

G.
--
while (!asleep()) sheep++;

---------------------------- cut here ------------------------------
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
Sent: Friday, January 24, 2003 4:37 PM


> > CONCLUSION: I think both the optimizer, both the side effects would be
> > happier if all of COALESCE's expressions were only calculated once.
>
> Yup, and NULLIF too.  Want to submit a patch?  Look for CaseExpr in the
> sources, copy/paste/modify.

Kris Jurka <books@ejurka.com> writes:
> I have been looking at this and the NULLIF case seems complicatd.
> Essentially for NULLIF(left,right) I want to ExecEvalExpr(left,...)
> and construct a Const node with the result.  Then makeSimpleA_Expr for
> const left = right.

Uh, no; you are confusing parse-time and run-time work.  What you
probably want is a new node type that includes the OID of the
appropriate "=" operator but does different things with the result
than a normal "=" invocation would do.

Actually, thinking about it that way, it might just be a small variant
on the DistinctExpr node type that exists in current sources.  Certainly
DistinctExpr would be a good model to look at.

            regards, tom lane

On Fri, 24 Jan 2003, Tom Lane wrote:

> "=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?=" <surrano@mailbox.hu> writes:
> > CONCLUSION: I think both the optimizer, both the side effects would be
> > happier if all of COALESCE's expressions were only calculated once.
>
> Yup, and NULLIF too.  Want to submit a patch?  Look for CaseExpr in the
> sources, copy/paste/modify.
>

I have been looking at this and the NULLIF case seems complicatd.
Essentially for NULLIF(left,right) I want to ExecEvalExpr(left,...)
and construct a Const node with the result.  Then makeSimpleA_Expr for
const left = right.  Evaluate that and if not true return the already
evaluated left side otherwise NULL.

This seems to merge all phases from parse to execution into the execution
phase.  How can I evaluate an expression and then construct a new
expression with that result in a clean manner?

Kris Jurka