Обсуждение: unnesting of array of different size explodes memory
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
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
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.6e6rows. 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