Re: [SQL] unnesting of array of different size explodes memory

From: Charlie <scorpdaddy(at)hotmail(dot)com>
To: Andreas Gaab <A(dot)Gaab(at)scanlab(dot)de>, pgsql sql <pgsql-sql(at)postgresql(dot)org>
Cc: Hans Joachim Münzer <H(dot)Muenzer(at)scanlab(dot)de>
Subject: Re: [SQL] unnesting of array of different size explodes memory
Date: 2011-04-13 16:21:27
Message-ID: BLU0-SMTP1470BAFE5339F1ACE4A0DFFCEAA0@phx.gbl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Another approach to consider is using strings. E.g. The function assembles the underlying query as a string and returns the results of executing the string. Passing the groups in a string may be straightforward in your application, or not.

There was a thread in the novice forum last year about the relative speed of functions that assemble and execute strings vs functions that encode the underlying query. The string execution is faster. If I recall the explanation, the executed string is remembered by the planner and speeds up. But the planner has no knowledge of the query structure encoded in the function, and so starts from scratch each time - very slow.

I got my function times from 10 secs to 300 ms by assembling query strings in the function.

Charlie

----- Reply message -----
From: "Andreas Gaab" <A(dot)Gaab(at)scanlab(dot)de>
Date: Wed, Apr 13, 2011 6:15 am
Subject: [SQL] unnesting of array of different size explodes memory
To: "pgsql sql" <pgsql-sql(at)postgresql(dot)org>
Cc: "Hans Joachim Münzer" <H(dot)Muenzer(at)scanlab(dot)de>

Hi,

I need to sum up the elements of two arrays. Most arrays were of the same size, thus I just unnested the arrays in one table and grouped the results in a loop for every row. When two arrays of different size (1200 and 1300) were processed, the memory usage exploded and the query was killed by the kernel.

As I now understand, the following query leads to 12 results, not just 4 (or 3...):

SELECT unnest(ARRAY[1,2,3]), unnest(ARRAY[4,5,6,7]);

Why could postgres use as much memory till the kernel complained when unnesting 1200 and 1300 elements resulting in 1.6e6 rows. Are there settings to prevent this such as "work_mem"?

Regards,
Andreas

___________________________________________________________________________

SCANLAB AG
Dr. Andreas Simon Gaab
Entwicklung * R & D

Siemensstr. 2a * 82178 Puchheim * Germany
Tel. +49 (89) 800 746-513 * Fax +49 (89) 800 746-199
mailto:a(dot)gaab(at)scanlab(dot)de * www.scanlab.de<http://www.scanlab.de>

Amtsgericht München: HRB 124707 * USt-IdNr.: DE 129 456 351
Vorstand: Georg Hofner (Sprecher), Christian Huttenloher, Norbert Petschik
Aufsichtsrat (Vorsitz): Dr. Hans J. Langer
___________________________________________________________________________

Besuchen Sie uns auf der / Meet us at
LASER World of PHOTONICS 2011
Munich, Germany
May 23 - 26, 2011
Hall C2, Booth 461

Browse pgsql-sql by date

  From Date Subject
Next Message Anish Kejariwal 2011-04-13 19:03:51 Re: pass in array to function for use by where clause? how optimize?
Previous Message Tom Lane 2011-04-13 16:15:33 Re: pass in array to function for use by where clause? how optimize?