Re: Mystery function error - Mailing list pgsql-sql

Tom Lane wrote:
> Josh Berkus <josh@agliodbs.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