Re: HOW does 8.3 CREATE REPLACe .. FUNCTION ..COST work

From: "Obe, Regina" <robe(dot)dnd(at)cityofboston(dot)gov>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-general(at)postgresql(dot)org>
Subject: Re: HOW does 8.3 CREATE REPLACe .. FUNCTION ..COST work
Date: 2008-02-25 16:28:46
Message-ID: 53F9CF533E1AA14EA1F8C5C08ABC08D2033D2CB3@ZDND.DND.boston.cob
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


> > --Test 2: This works as I would expect - shows that none of the
> > functions are run presumably its going straight for 5 > 2
> > --becuase it recognizes its the cheapest route
> > TRUNCATE TABLE log_call;
> > SELECT foo.value
> > FROM (SELECT (fn_pg_costlyfunction() > 2 OR fn_pg_cheapfunction() >
2 OR
> > 5 > 2 ) as value) as foo

> That's just constant-folding: x OR TRUE is TRUE. It has exactly
> zero to do with the cost of anything.

> Offhand I think the behavior you are looking for of choosing to run
more
> expensive subexpressions later only occurs for top-level WHERE clauses
>that are combined with AND.

> regards, tom lane

Tom thanks for the clarification - based on your comment I verified with
these

-- fn_pg_cheapfunction() is the only one run as you predicted
TRUNCATE TABLE log_call;
SELECT true as value
WHERE (fn_pg_costlyfunction() > 2 AND fn_pg_cheapfunction() > 5 );

-- fn_pg_costlyfunction() is the only one run - again as predicted by
your statement
TRUNCATE TABLE log_call;
SELECT true as value
WHERE (fn_pg_costlyfunction() > 2 OR fn_pg_cheapfunction() > 2 );

It would be really nice if this worked with OR as well. Is it just much
harder to deal with the
OR case in the planner or was there some other reason why the OR case
was left out?

Thanks,
Regina
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Luca Ferrari 2008-02-25 16:38:57 process pool
Previous Message Tom Lane 2008-02-25 16:08:57 Re: HOW does 8.3 CREATE REPLACe .. FUNCTION ..COST work