Re: Replacing Cursors with Temporary Tables

On Wed, Apr 21, 2010 at 4:16 PM, Eliot Gable
<egable+pgsql-performance(at)gmail(dot)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