Re: [SQL] How to avoid "Out of memory" using aggregate functions?

Well, not silly at all . . . regrouping
the order of the WHERE clause does the trick!

Cheers,

Frank


On Mon, Dec 06, 1999 at 11:23:27AM -0500, Tom Lane wrote:
> Frank Joerdens <frank@x9media.com> writes:
> > [ complex query ]
> > which stopped working after the table grew over the 1 million mark
> > (approximately) - I get the error
> 
> > FATAL 1:  Memory exhausted in AllocSetAlloc()
> 
> > This sounds to me like the problem described below . . . my question is:
> > Would the changes you already made to the current sources fix it for me?
> 
> Afraid not.  I think what's killing you is all those date_part() and
> date_trunc() operations --- the resultant datetime or float8 value from
> each one occupies memory that won't get reclaimed till end of statement
> :-(.
> 
> Silly as it sounds, you might be able to put off the problem by
> rearranging the order of the WHERE clauses, remembering that AND stops
> evaluating its subclauses as soon as it finds a FALSE.  Presumably the
> day check eliminates many more rows than the time-of-day checks, so
> 
> WHERE
> domains.internet = stunde_test.destination 
> AND
> date_trunc('day', stunde_test.date) = 'yesterday' 
> AND
> date_part('hour', stunde_test.date) > 6 
> AND 
> date_part('hour', stunde_test.date) < 23 
> 
> would probably about halve the number of date_part+date_trunc
> calculations done.
> 
> Of course the real fix is to recycle temporary memory for all
> expressions intra-query, but I do not know if that will get done for
> 7.0.  It will get done eventually.
> 
>             regards, tom lane
> 
> ************