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
Andreas Gaab 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
Anish Kejariwal writes:
> (select store_id, avg(sales) sales
> from store
> where group_id in(select $1[i] from generate_subscripts($1, 1) g(i))
Seems like a pretty brute-force way to deal with the array. Try
where group_id = any($1)
Not sure if it'll be a great deal faster, but it's at least
On 04/13/2011 09:09 AM, Tom Lane wrote:
Anish Kejariwal writes:
(select store_id, avg(sales) sales
from store
where group_id in(select $1[i] from generate_subscripts($1, 1) g(i))
Seems like a pretty brute-force way to deal with the array. Try
where group_id = any($1)
Not sure if it'll be
Rob Sargent writes:
> On 04/13/2011 09:09 AM, Tom Lane wrote:
>> Anish Kejariwal writes:
>>> (select store_id, avg(sales) sales
>>> from store
>>> where group_id in(select $1[i] from generate_subscripts($1, 1) g(i))
>> Seems like a pretty brute-force way to deal with the array. Try
>> where gro
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 abou
Thanks Tom! I've never used the any function before. It totally fixed the
issue. It's easier to code, makes more sense, and it fixed my performance
issue.
My guess is that the optimizer does not how to deal with
generate_subscripts??
Thanks!!
Anish
On Wed, Apr 13, 2011 at 9:15 AM, Tom Lane
I did encounter a quite strange behavior during update which I did not
understand:
The following query updated table parcelle with only one entry for all rows:
update cadastre.parcelle
set code_unique = t2.code_unique from
cadastre.parcelle t1, cadastre.propriete t2
where t1.uoid =
