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

Adam Ruth <adamruth@mac.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;