From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andreas Gaab <A.Gaab@scanlab.de>
Cc: pgsql sql <pgsql-sql@postgresql.org>
Cc: Hans Joachim Münzer <H.Muenzer@scanlab.de>
Subject: Re: unnesting of array of different size explodes memory
Date: Wed, 13 Apr 2011 10:52:32 -0400
Message-ID: <4785.1302706352@sss.pgh.pa.us> (raw)
In-Reply-To: <48DA836F3865C54B8FBF424A3B775AF667239FFDEF@Exchange-Server>
References: <48DA836F3865C54B8FBF424A3B775AF667239FFDEF@Exchange-Server>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, A.Gaab@scanlab.de, H.Muenzer@scanlab.de
Subject: Re: unnesting of array of different size explodes memory
In-Reply-To: <4785.1302706352@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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