DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=mqSMcEpvgufClTj1in/InLglDGEvpHkVyZEgbhARK58HQN1kymtmzAZZEdM32gN5eq
	gjKdsUls2yLff8uGewoCJdAW1UBvsBa39jF7zTrS+aGDCdbLMZvDtFRc5KysvJaWw9aJ
	/aCJ4wsKvCtzoo7z/av1/Fw839yUhQdNExqRU=
MIME-Version: 1.0
In-Reply-To: <n2ybf6923ed1004211316ib844d82bn7a63bd3292ca80a0@mail.gmail.com>
References: <n2ybf6923ed1004211316ib844d82bn7a63bd3292ca80a0@mail.gmail.com>
Date: Thu, 22 Apr 2010 10:11:59 -0400
Message-ID: <n2sb42b73151004220711mc2135db6heccf53ec1fb0136e@mail.gmail.com>
Subject: Re: Replacing Cursors with Temporary Tables
From: Merlin Moncure <mmoncure@gmail.com>
To: Eliot Gable <egable+pgsql-performance@gmail.com>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1

On Wed, Apr 21, 2010 at 4:16 PM, Eliot Gable
<egable+pgsql-performance@gmail.com> wrote:
> I have previously discussed my very long PL/PGSQL stored procedure on this
> list. However, without getting into too many details, I have another
> performance-related question.

ok, here's a practical comparion:
-- test data
create table foo(foo_id int primary key);
insert into foo select generate_series(1, 1000) v;
create table bar(bar_id int, foo_id int references foo);
create index bar_foo_id_idx on bar(foo_id);
insert into bar select v, (v % 1000) + 1 from generate_series(1, 1000000) v;

-- arrays
create or replace function get_foobars(_foo_id int, _foo out foo,
_bars out bar[]) returns record as
$$
  begin
    select * from foo where foo_id = _foo_id into _foo;

    select array(select bar from bar where foo_id = _foo_id) into _bars;
  end;
$$ language plpgsql;

select (unnest(_bars)).* from get_foobars(6);  -- ~ 4ms on my box

-- temp table

create or replace function get_foobars(_foo_id int) returns void as
$$
  begin
    create temp table bars on commit drop as select * from bar where
foo_id = _foo_id;
  end;
$$ language plpgsql;

begin;
select get_foobars(6); -- ~ 3ms
select * from bars;  -- 1.6ms
commit; -- 1ms

The timings are similar, but the array returning case:
*)  runs in a single statement.  If this is executed from the client
that means less round trips
*) can be passed around as a variable between functions.  temp table
requires re-query
*) make some things easier/cheap such as counting the array -- you get
to call the basically free array_upper()
*) makes some things harder.  specifically dealing with arrays on the
client is a pain UNLESS you expand the array w/unnest() or use
libpqtypes
*) can nest. you can trivially nest complicated sets w/arrays
*) does not require explicit transaction mgmt

merlin