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

"Obe, Regina" <robe.dnd@cityofboston.gov> writes:
> --Test 1: This shows that fn_pg_costlyfunction() is the only function
> that is run -
> -- unexpected to me shouldn't no function be evaluated or the cheap one?
> --What's the difference between Test 1 and Test 2 that makes Test 2 do
> the RIGHT thing?
> TRUNCATE TABLE log_call;
> SELECT (fn_pg_costlyfunction() > 2 OR fn_pg_cheapfunction() > 2 OR 5 >
> 2);

In a SELECT with no FROM we don't run the optimizer at all; the
assumption is that when the expression will only be evaluated once,
it's not worth trying to do expression simplification on it first.

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