Обсуждение: unnesting of array of different size explodes memory

<div class="WordSection1"><p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Times New
Roman","serif"">Hi,</span><pclass="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Times New
Roman","serif""> </span><pclass="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Times New
Roman","serif"">Ineed to sum up the elements of two arrays. Most arrays were of the same size, thus I just unnested the
arraysin one table and grouped the results in a loop for every row. When two arrays of different size (1200 and 1300)
wereprocessed, the memory usage exploded and the query was killed by the kernel.</span><p class="MsoNormal"><span
lang="EN-US"style="font-size:11.0pt;font-family:"Times New Roman","serif""> </span><p class="MsoNormal"><span
lang="EN-US"style="font-size:11.0pt;font-family:"Times New Roman","serif"">As I now understand, the following query
leadsto 12 results, not just 4 (or 3…):</span><p class="MsoNormal"><span lang="EN-US"
style="font-size:11.0pt;font-family:"TimesNew Roman","serif""> </span><p class="MsoNormal"><span lang="EN-US"
style="font-size:11.0pt;font-family:"TimesNew Roman","serif"">SELECT unnest(ARRAY[1,2,3]),
unnest(ARRAY[4,5,6,7]);</span><pclass="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Times New
Roman","serif""> </span><pclass="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Times New
Roman","serif"">Whycould postgres use as much memory till the kernel complained when unnesting 1200 and 1300 elements
resultingin 1.6e6 rows. Are there settings to prevent this such as “work_mem”?</span><p class="MsoNormal"><span
lang="EN-US"style="font-size:11.0pt;font-family:"Times New Roman","serif""> </span><p class="MsoNormal"><span
lang="EN-US"style="font-size:11.0pt;font-family:"Times New Roman","serif"">Regards,</span><p class="MsoNormal"><span
lang="EN-US"style="font-size:11.0pt;font-family:"Times New Roman","serif"">Andreas</span><p class="MsoNormal"><span
lang="EN-US"style="font-size:11.0pt;font-family:"Times New Roman","serif""> </span><p class="MsoNormal"><span
style="font-size:10.0pt">___________________________________________________________________________</span><p
class="MsoNormal"><spanstyle="font-size:10.0pt"> </span><p class="MsoNormal"><span
style="font-size:10.0pt">SCANLAB AG</span><pclass="MsoNormal"><span
style="font-size:10.0pt">Dr. Andreas Simon Gaab</span><pclass="MsoNormal"><span style="font-size:10.0pt">Entwicklung •
R & D</span><pclass="MsoNormal"><span style="font-size:10.0pt"> </span><p class="MsoNormal"><span
style="font-size:10.0pt">Siemensstr. 2a• 82178 Puchheim • Germany</span><p class="MsoNormal"><span
style="font-size:10.0pt">Tel. +49 (89) 800 746-513• Fax +49 (89) 800 746-199</span><p class="MsoNormal"><span
style="font-size:10.0pt"><ahref="mailto:a.gaab@scanlab.de">mailto:a.gaab@scanlab.de</a> • <a
href="http://www.scanlab.de">www.scanlab.de</a></span><pclass="MsoNormal"><span style="font-size:10.0pt"> </span><p
class="MsoNormal"><spanstyle="font-size:10.0pt">Amtsgericht München: HRB 124707 • USt-IdNr.: DE 129 456 351</span><p
class="MsoNormal"><spanstyle="font-size:10.0pt">Vorstand: Georg Hofner (Sprecher), Christian Huttenloher,
Norbert Petschik</span><pclass="MsoNormal"><span style="font-size:10.0pt">Aufsichtsrat (Vorsitz):
Dr. Hans J. Langer</span><pclass="MsoNormal"><span
style="font-size:10.0pt">___________________________________________________________________________</span><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif""></span><pclass="MsoNormal"> </div><pre>Besuchen Sie uns auf
der/ Meet us at 
LASER World of PHOTONICS 2011
Munich, Germany
May 23 - 26, 2011
Hall C2, Booth 461
</pre>

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