From: Merlin Moncure <mmoncure@gmail.com>
To: Eliot Gable <egable+pgsql-performance@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Replacing Cursors with Temporary Tables
Date: Thu, 22 Apr 2010 10:11:59 -0400
Message-ID: <n2sb42b73151004220711mc2135db6heccf53ec1fb0136e@mail.gmail.com> (raw)
In-Reply-To: <n2ybf6923ed1004211316ib844d82bn7a63bd3292ca80a0@mail.gmail.com>
References: <n2ybf6923ed1004211316ib844d82bn7a63bd3292ca80a0@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mmoncure@gmail.com, egable+pgsql-performance@gmail.com
Subject: Re: Replacing Cursors with Temporary Tables
In-Reply-To: <n2sb42b73151004220711mc2135db6heccf53ec1fb0136e@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox