From: Joe Conway <mail@joeconway.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Josh Berkus <josh@agliodbs.com>
Cc: Richard Sydney-Smith <richard@ibisaustralia.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Mystery function error
Date: Sun, 28 Sep 2003 09:21:33 -0700
Message-ID: <3F770A8D.9030400@joeconway.com> (raw)
In-Reply-To: <9398.1064765182@sss.pgh.pa.us>
References: <000c01c38572$73c03c50$251a32d2@athlon2000>
<200309272229.50601.josh@agliodbs.com>
<9398.1064765182@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mail@joeconway.com, tgl@sss.pgh.pa.us, josh@agliodbs.com, richard@ibisaustralia.com
Subject: Re: Mystery function error
In-Reply-To: <3F770A8D.9030400@joeconway.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox