Re: Converting each item in array to a query result row - Mailing list pgsql-general

Always test your performance assumptions. The plpgsql function is
faster than the sql function, a lot faster on smaller arrays.

unnest - 10 element array - 100,000 times: 6701.746 ms
unnest - 100 element array - 100,000 times: 11847.933 ms
unnest - 1000 element array - 100,000 times: 59472.691 ms

explode - 10 element array - 100,000 times: 1941.942 ms
explode - 100 element array - 100,000 times: 8521.289 ms
explode - 1000 element array - 100,000 times: 44980.048 ms

On 29/05/2009, at 8:55 PM, Grzegorz Jaśkiewicz wrote:

> why complicate so much ? this is a single sql query. It really shocks
> me, how people easily lean on plpgsql and for/loops - which are times
> slower than simple sql query:
>
> CREATE FUNCTION unnest(anyarray) RETURNS SETOF anyelement AS
> $_$
> SELECT ($1)[i] FROM
> generate_series(array_lower($1,1),array_upper($1,1)) i;
> $_$
>    LANGUAGE sql IMMUTABLE;