Good point, I should have specified 8.3.7.
Just one more reason to anxiously anticipate upgrading to 8.4.
On 30/05/2009, at 2:56 AM, Tom Lane wrote:
Adam Ruth <adamruth@xxxxxxx> writes:
Always test your performance assumptions. The plpgsql function is
faster than the sql function, a lot faster on smaller arrays.
And, of course, it also pays to be precise about what you're testing
and on what. Set-returning SQL functions got a lot faster in 8.4.
Using CVS HEAD on a not-very-fast machine, I get these timings for
the attached script (10000 loop iterations in all cases)
10 elements 100 elements 1000 elements
built-in unnest 2.44 6.52 47.96
SQL function 2.52 6.50 46.71
plpgsql function 3.63 12.47 101.68
So at least in this specific test condition, there's not much
perceptible difference between the SQL function and the builtin,
while plpgsql lags behind.
regards, tom lane
create or replace function testit(n int, l int) returns float8 as $$
declare arr int[];
st timestamptz;
et timestamptz;
begin
arr := '{}';
for i in 1 .. n loop
arr[i] = i;
end loop;
st := clock_timestamp();
for i in 1 .. l loop
perform count(*) from unnest(arr); -- or unnest_sql or
unnest_plpgsql
end loop;
et := clock_timestamp();
return extract(epoch from et - st);
end $$ language plpgsql;
CREATE or replace FUNCTION unnest_sql(anyarray) RETURNS SETOF
anyelement AS
$_$
SELECT ($1)[i] FROM
generate_series(array_lower($1,1),array_upper($1,1)) i;
$_$
LANGUAGE sql IMMUTABLE;
create or replace function unnest_plpgsql(_a anyarray) returns setof
anyelement as $$
begin
for i in array_lower(_a,1) .. array_upper(_a,1) loop
return next _a[i];
end loop;
return;
end;
$$ language plpgsql strict immutable;
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
