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-24 13:23:40
Message-ID: s2lb42b73151004240623h78726052h4f30fade77f8b572@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Fri, Apr 23, 2010 at 10:31 PM, Eliot Gable
<egable+pgsql-performance(at)gmail(dot)com> wrote:
> In each case, the results are available outside the stored procedure by
> either fetching from the cursor or selecting from the temporary table.
> Clearly, the temporary table takes a performance hit compared using arrays.
> Building an array with array append is horrendously inefficient. Unnesting
> an array is surprisingly efficient. As can be seen from Test3 and Test4,
> cursors have no detectable overhead for opening the cursor (at least in this
> example with 1000 result rows). It is unclear whether there is any
> difference at all from Test3 and Test4 for retrieving the data as I have no
> easy way right now to measure that accurately. However, since arrays+cursors
> are more efficient than anything having to do with temp tables, that is the
> way I will go. With the number of rows I am dealing with (which should
> always be less than 1,000 in the final returned result set), unnesting an
> array is much faster than building a temp table and selecting from it.
> If anyone thinks I may have missed some important item in this testing,
> please let me know.

Well, you missed the most important part: not using cursors at all.
Instead of declaring a cursor and looping it to build the array, build
it with array().  That's what I've been saying: arrays can completely
displace both temp tables _and_ cursors when passing small sets around
functions.

merlin

In response to

Responses

pgsql-performance by date

Next:From: Grzegorz JaśkiewiczDate: 2010-04-24 14:38:02
Subject: Re: Replacing Cursors with Temporary Tables
Previous:From: Pierre CDate: 2010-04-24 09:26:58
Subject: Re: Replacing Cursors with Temporary Tables

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