Skip site navigation (1) Skip section navigation (2)

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Adam Ruth <adamruth(at)mac(dot)com>
Cc: Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)com>, Postgres User <postgres(dot)developer(at)gmail(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: Converting each item in array to a query result row
Date: 2009-05-29 16:56:48
Message-ID: 20444.1243616208@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-general
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;

In response to

Responses

pgsql-general by date

Next:From: Scott MarloweDate: 2009-05-29 17:02:08
Subject: Re: Transaction settings: nowait
Previous:From: Scott MarloweDate: 2009-05-29 16:22:28
Subject: Re: Transaction settings: nowait

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group