Thanks for all the replies. I'm going to post the results of using
the recommended approach in another thread.
On Fri, May 29, 2009 at 1:18 PM, Adam Ruth <adamruth@mac.com> wrote:
> 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;
>
>