Re: Mystery function error

Tom Lane wrote:
> Josh Berkus <josh(at)agliodbs(dot)com> writes:
>>Also, this will run faster if you do it as a SQL function:
>
>>CREATE FUNCTION locate ( text, text ) RETURNS INT AS '
>>SELECT POSITION($2, $1);
>>' LANGUAGE SQL IMMUTABLE STRICT;
>
> This is definitely the solution I'd recommend for 7.4 (because 7.4 would
> inline the SQL function definition, resulting in zero runtime overhead).
> In 7.3 I suspect the plpgsql version might be a tad faster, or anyway
> comparable. Has anyone done any head-to-head performance comparisons
> on such simple functions?
>

I did a quick check last night on 7.3.4 and found that plpgsql was faster:

CREATE OR REPLACE FUNCTION public.locate(bpchar, bpchar) RETURNS int4 AS '
-- search for the position of $2 in $1
declare
srcstr alias for $1;
searchstr alias for $2;
begin
return position(searchstr in srcstr);
end;
' LANGUAGE 'plpgsql' IMMUTABLE;

regression=# explain analyze select locate('abc','b');

QUERY PLAN
----------------------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.01..0.01
rows=1 loops=1)
Total runtime: 0.03 msec
(2 rows)

DROP FUNCTION public.locate(bpchar, bpchar);
CREATE OR REPLACE FUNCTION public.locate(bpchar, bpchar) RETURNS int4 AS '
select position($2 in $1)
' LANGUAGE 'sql';
regression=# explain analyze select locate('abc','b');
QUERY PLAN
----------------------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.24..0.24
rows=1 loops=1)
Total runtime: 0.26 msec
(2 rows)

On 7.4 (different hardware), I get this:

plpgsql
--------
regression=# explain analyze select locate('abc','b');
QUERY PLAN
----------------------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.00..0.00
rows=1 loops=1)
Total runtime: 0.05 msec
(2 rows)

sql
--------
regression=# explain analyze select locate('abc','b');
QUERY PLAN
----------------------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.01..0.01
rows=1 loops=1)
Total runtime: 0.03 msec
(2 rows)

Joe