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

Re: Replacing Cursors with Temporary Tables

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: Eliot Gable <egable+pgsql-performance(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Replacing Cursors with Temporary Tables
Date: 2010-04-22 14:11:59
Message-ID: n2sb42b73151004220711mc2135db6heccf53ec1fb0136e@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Merlin MoncureDate: 2010-04-22 14:42:42
Subject: Re: Replacing Cursors with Temporary Tables
Previous:From: Greg SmithDate: 2010-04-22 12:37:32
Subject: Re: Optimization idea

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