When creating the Postgresql driver for AdMentor.net I couldn't help running an extra performance test with regards to language for my functions. Despite understanding that all functions are indeed compiled to a plan regardless of their source language I just could help myself. I mean plase compare these:
Stored function #1:
CREATE OR REPLACE FUNCTION ipgetcountry(in_ip bigint, in_ipverno integer)
RETURNS SETOF a_iprecord AS
$BODY$
DECLARE ret_row RECORD;
BEGIN
FOR ret_row IN SELECT * FROM a_iprecord
WHERE IPFROM<=in_ip AND IPTO>=in_ip and ipverno=in_ipverno LOOP
RETURN NEXT ret_row;
END LOOP;
RETURN;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Stored function #2:
CREATE OR REPLACE FUNCTION ipgetcountry2(in_ip bigint, in_ipverno integer)
RETURNS SETOF a_iprecord AS
'SELECT * FROM a_iprecord WHERE IPFROM<=$1 AND IPTO>=$1 and ipverno=$2'
LANGUAGE 'sql' VOLATILE;
Don't you get the feeling that #2 should be a lot faster - I mean in no 1 we need to "manually" loop through the records and add them to the return set.
However, my tests showed they both perform virtually the same. Under the hood they are most certainly compiled to pretty much the same execution plan.
