pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
unnesting of array of different size explodes memory 3+ messages / 3 participants [nested] [flat]
* unnesting of array of different size explodes memory @ 2011-04-13 09:15 Andreas Gaab <A.Gaab@scanlab.de> 2011-04-13 14:52 ` Re: unnesting of array of different size explodes memory Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 1 reply; 3+ messages in thread From: Andreas Gaab @ 2011-04-13 09:15 UTC (permalink / raw) To: pgsql-sql; +Cc: Hans Joachim Münzer <H.Muenzer@scanlab.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.gaab@scanlab.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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: unnesting of array of different size explodes memory 2011-04-13 09:15 unnesting of array of different size explodes memory Andreas Gaab <A.Gaab@scanlab.de> @ 2011-04-13 14:52 ` Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 3+ messages in thread From: Tom Lane @ 2011-04-13 14:52 UTC (permalink / raw) To: Andreas Gaab <A.Gaab@scanlab.de>; +Cc: pgsql-sql; Hans Joachim Münzer <H.Muenzer@scanlab.de> Andreas Gaab <A.Gaab@scanlab.de> writes: > 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"? Multiple SRFs in a targetlist are a good thing to avoid. The behavior is ... um ... peculiar, and the fact that we can't reclaim memory partway through is really the least of the problems with it. Try doing it like this instead: SELECT * from unnest(ARRAY[1,2,3]) a, unnest(ARRAY[4,5,6,7]) b; This has saner behavior and is less likely to leak memory. Not to mention less likely to be deprecated or de-implemented altogether in the far future. regards, tom lane ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] unnesting of array of different size explodes memory @ 2011-04-13 16:21 Charlie <scorpdaddy@hotmail.com> 0 siblings, 0 replies; 3+ messages in thread From: Charlie @ 2011-04-13 16:21 UTC (permalink / raw) To: Andreas Gaab <A.Gaab@scanlab.de>; pgsql-sql; +Cc: Hans Joachim Münzer <H.Muenzer@scanlab.de> 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.Gaab@scanlab.de> Date: Wed, Apr 13, 2011 6:15 am Subject: [SQL] unnesting of array of different size explodes memory To: "pgsql sql" <pgsql-sql@postgresql.org> Cc: "Hans Joachim Münzer" <H.Muenzer@scanlab.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.gaab@scanlab.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 ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-04-13 16:21 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2011-04-13 09:15 unnesting of array of different size explodes memory Andreas Gaab <A.Gaab@scanlab.de> 2011-04-13 14:52 ` Tom Lane <tgl@sss.pgh.pa.us> 2011-04-13 16:21 Re: [SQL] unnesting of array of different size explodes memory Charlie <scorpdaddy@hotmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox