Re: Converting each item in array to a query result row

Adam Ruth <adamruth(at)mac(dot)com> 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;