pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Frank Joerdens <frank@x9media.com>
To: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] How to avoid "Out of memory" using aggregate functions?
Date: Mon, 6 Dec 1999 19:20:32 +0100
Message-ID: <19991206192032.C12528@flateric.x9media.com> (raw)
In-Reply-To: <18807.944497407@sss.pgh.pa.us>
References: <19991206135652.B12528@flateric.x9media.com>
	<18807.944497407@sss.pgh.pa.us>

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
> 
> ************



view thread (5+ messages)

Message-ID: <19991206192032.C12528@flateric.x9media.com>
Permalink:  ../19991206192032.C12528@flateric.x9media.com/
Also on:    postgresql.org/message-id/19991206192032.C12528@flateric.x9media.com

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: frank@x9media.com
  Subject: Re: [SQL] How to avoid "Out of memory" using aggregate functions?
  In-Reply-To: <19991206192032.C12528@flateric.x9media.com>
  References: <19991206135652.B12528@flateric.x9media.com> <18807.944497407@sss.pgh.pa.us> <19991206192032.C12528@flateric.x9media.com>

* 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