Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 421501337B68 for ; Wed, 13 Apr 2011 06:26:19 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 24534-09 for ; Wed, 13 Apr 2011 09:25:41 +0000 (UTC) X-Greylist: delayed 00:09:58.856868 by SQLgrey-1.7.6 Received: from mail.scanlab.de (mail.scanlab.de [62.138.55.241]) by mail.postgresql.org (Postfix) with ESMTP id 1E2811337B83 for ; Wed, 13 Apr 2011 06:25:39 -0300 (ADT) Received: from unknown (HELO Exchange-Server.scanlab-intern.de) ([172.16.20.15]) by mail.scanlab-intern.de with ESMTP; 13 Apr 2011 11:15:39 +0200 Received: from Exchange-Server.scanlab-intern.de ([127.0.0.1]) by Exchange-Server ([127.0.0.1]) with mapi; Wed, 13 Apr 2011 11:15:39 +0200 From: Andreas Gaab To: pgsql sql CC: =?iso-8859-1?Q?Hans_Joachim_M=FCnzer?= Date: Wed, 13 Apr 2011 11:15:38 +0200 Subject: unnesting of array of different size explodes memory Thread-Topic: unnesting of array of different size explodes memory Thread-Index: Acv5u1r7QBhrzDL6ShWUbpGxcEyEcQ== Message-ID: <48DA836F3865C54B8FBF424A3B775AF667239FFDEF@Exchange-Server> Accept-Language: de-DE Content-Language: de-DE X-MS-Has-Attach: X-MS-TNEF-Correlator: acceptlanguage: de-DE Content-Type: multipart/alternative; boundary="_000_48DA836F3865C54B8FBF424A3B775AF667239FFDEFExchangeServe_" MIME-Version: 1.0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.909 tagged_above=-5 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201104/9 X-Sequence-Number: 35256 --_000_48DA836F3865C54B8FBF424A3B775AF667239FFDEFExchangeServe_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, I need to sum up the elements of two arrays. Most arrays were of the same s= ize, thus I just unnested the arrays in one table and grouped the results i= n a loop for every row. When two arrays of different size (1200 and 1300) w= ere processed, the memory usage exploded and the query was killed by the ke= rnel. As I now understand, the following query leads to 12 results, not just 4 (o= r 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 unnes= ting 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.gaab@scanlab.de * www.scanlab.de Amtsgericht M=FCnchen: 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 --_000_48DA836F3865C54B8FBF424A3B775AF667239FFDEFExchangeServe_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hi,<= /o:p>

<= p class=3DMsoNormal>I need to sum up the elements of two arrays. M= ost arrays were of the same size, thus I just unnested the arrays in one ta= ble and grouped the results in a loop for every row. When two arrays of dif= ferent size (1200 and 1300) were processed, the memory usage exploded and t= he query was killed by the kernel.

As I n= ow understand, the following query leads to 12 results, not just 4 (or 3= 230;):

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

<= o:p>

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,<= /o:p>

Andreas

=

______________________________________= _____________________________________

SCANLAB AG

Dr. Andr= eas Simon Gaab

Entwicklung • R & D=

= ;

Sie= mensstr. 2a • 82178 Puchheim • Germany

Tel. +49&= nbsp;(89) 800 746-513 • Fax +49 (89) 800&nbs= p;746-199

mailto:a.gaab@scanlab.de= • www.scanlab.de

Amtsge= richt M=FCnchen: HRB 124707 • USt-IdNr.: DE 129 4= 56 351

Vorstand: Georg Hofner (Sprecher), Christian Hut= tenloher, Norbert Petschik

<= span style=3D'font-size:10.0pt'>Aufsichtsrat (Vorsitz): Dr. Hans&= nbsp;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

--_000_48DA836F3865C54B8FBF424A3B775AF667239FFDEFExchangeServe_--