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

I think I am missing something about how the new CREATE OR REPLACE
FUNCTION ...COST works or I am missing some setting in postgresql conf.

I was hoping I could use it to control the function that is used in
cases where only one needs to be evaluated. Regardless of what I do it
seems to always evaluate the first function in the list. I'm running on

"PostgreSQL 8.3.0, compiled by Visual C++ build 1400"

Here is an example of my test: Functions and tables
CREATE TABLE log_call
(
fn_name character varying(100) NOT NULL,
fn_calltime timestamp with time zone NOT NULL DEFAULT now()
)
WITH (OIDS=FALSE);

CREATE OR REPLACE FUNCTION fn_pg_costlyfunction()
RETURNS integer AS
$$
BEGIN
INSERT INTO log_call(fn_name) VALUES('fn_pg_costlyfunction()');
RETURN 5;
END$$
LANGUAGE 'plpgsql' VOLATILE
COST 1000000;

CREATE OR REPLACE FUNCTION fn_pg_cheapfunction()
RETURNS integer AS
$$
BEGIN
INSERT INTO log_call(fn_name) VALUES('fn_pg_cheapfunction()');
RETURN 5;
END$$
LANGUAGE 'plpgsql' VOLATILE
COST 1;

--- Now for the test -
--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);

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

--Test 3: It always runs the first function even though the cost of the
first is higher than the second
(in this case log_call contains fn_pg_costlyfunction())
TRUNCATE TABLE log_call;
SELECT foo.value
FROM (SELECT (fn_pg_costlyfunction() > 2 OR fn_pg_cheapfunction() > 2)
as value) as foo;

TRUNCATE TABLE log_call;
SELECT (fn_pg_costlyfunction() > 2 OR fn_pg_cheapfunction() > 2) as
value;

--Test 4: It always runs the first function even though the cost of the
first is higher than the second
(in this case log_call contains fn_pg_cheapfunction())
TRUNCATE TABLE log_call;
SELECT foo.value
FROM (SELECT (fn_pg_cheapfunction() > 2 OR fn_pg_costlyfunction() > 2 )
as value) as foo;

TRUNCATE TABLE log_call;
SELECT (fn_pg_cheapfunction() > 2 OR fn_pg_costlyfunction() > 2 ) as
value;

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.

"Obe, Regina" <robe(dot)dnd(at)cityofboston(dot)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

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

"Obe, Regina" <robe(dot)dnd(at)cityofboston(dot)gov> writes:
> 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?

Nobody's really made a case why we should have the planner expend cycles
on that.

regards, tom lane