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