any benefit to preparing a sql function?

[culley harrelson]

Is there any benefit to preparing a call to a pre-defined sql function
or is the sql function already optimized?

create temp table foo(col1 integer);
insert into foo(col1) values(1);
insert into foo(col1) values(2);
insert into foo(col1) values(3);
insert into foo(col1) values(4);
create or replace function testfunc(integer) returns foo as 'select *
from foo where col1 = $1;' language sql;
prepare myplan(integer) as select * from testfunc($1);
execute myplan(2);

They look exactly the same:

testdb=# explain execute myplan(2);
QUERY PLAN
-----------------------------------------------------------------
Function Scan on testfunc (cost=0.00..12.50 rows=1000 width=4)
(1 row)
testdb=# explain select * from testfunc(2);
QUERY PLAN
-----------------------------------------------------------------
Function Scan on testfunc (cost=0.00..12.50 rows=1000 width=4)

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Holger Klawitter]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thursday 22 July 2004 23:58, culley harrelson wrote:

Is there any benefit to preparing a call to a pre-defined sql function
or is the sql function already optimized?

create temp table foo(col1 integer);
insert into foo(col1) values(1);
insert into foo(col1) values(2);
insert into foo(col1) values(3);
insert into foo(col1) values(4);
create or replace function testfunc(integer) returns foo as 'select *
from foo where col1 = $1;' language sql;
prepare myplan(integer) as select * from testfunc($1);
execute myplan(2);

All you get is the same result from the planner in shorter time.

- From the doc:

PREPARE creates a prepared query. A prepared query is a server-side object that
can be used to optimize performance. When the PREPARE statement is executed,
the specified query is parsed, rewritten, and planned. When a subsequent EXECUTE
statement is issued, the prepared query need only be executed. Thus, the parsing,
rewriting, and planning stages are only performed once, instead of every time the
query is executed.
Mit freundlichem Gruß / With kind regards
Holger Klawitter
- --
lists <at> klawitter <dot> de
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iD8DBQFBALWg1Xdt0HKSwgYRAg7/AJ0ecUJ2uHqY1vFzUNhi0Gy7UpaPaQCgpAF7
AvbysHawgwmUB5TGiOzi3sE=
=A0Gw
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)