On Fri, Apr 23, 2010 at 10:31 PM, Eliot Gable <egable+pgsql-performance@xxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
