Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.


Help make the earth a greener place. If at all possible resist printing this email and join us in saving paper.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux