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: (view raw, whole thread or download thread mbox)
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;
  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;

create or replace function unnest_plpgsql(_a anyarray) returns setof  
anyelement as $$
	for i in array_lower(_a,1) .. array_upper(_a,1) loop
		return next _a[i];
	end loop;
$$ language plpgsql strict immutable;

In response to


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-2017 The PostgreSQL Global Development Group