Joe Conway <[EMAIL PROTECTED]> writes:
>> ... 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:
> regression=# explain analyze select locate('abc','b');
Er ... I'm not sure you're me
I said:
> [hmm, I wonder why plpgsql seems to have gotten slower in 7.4...]
False alarm --- or at least, it wasn't plpgsql's fault. I copied Joe's
function definition, which was
CREATE OR REPLACE FUNCTION public.locate(bpchar, bpchar) RETURNS int4 AS '
But since position() takes arguments of ty
Josh Berkus <[EMAIL PROTECTED]> writes:
> Hmmm ... this does still seem to show that plpgsql is 10% slower in
> 7.4.
Yeah, I just did some profiling to check on that, and it seems there is
extra overhead in plpgsql's exec_eval_simple_expr(), to the tune of
another palloc or two down inside CreateE
Tom,
> position()1 usec/call 1 usec/call
> SQL func 1 usec/call 90 usec/call
> plpgsql func 110 usec/call 100 usec/call
Hmmm ... this does still seem to show that plpgsql is 10% slower in 7.4. Any
idea why?
--
Josh Berkus
Aglio Database Solutions
San Francisco
---
Tom Lane wrote:
Josh Berkus <[EMAIL PROTECTED]> 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 wou
Josh Berkus <[EMAIL PROTECTED]> 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
inli
Richard,
> The goal is to have
>
> locate( stra, strb) = position(strb in stra)
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;
--
Josh Berkus
Aglio Database Solutions
Sa
Richard,
>--- 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);
You're missi
Richard Sydney-Smith wrote:
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); ' LANGUAGE 'plpgsql'
VOLATILE;
You are missing the "end"
As I am converting from Sybase I wanted to create a
function which would replicate the behaviour of the sybase "Locate"
command.
The goal is to have
locate( stra, strb) = position(strb in
stra)
where "position" is the standard postgres function
for the index position of string "A" in
10 matches
Mail list logo
