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

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@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;