Received: from flateric.x9media.com ([62.104.116.67]) by hub.org (8.9.3/8.9.3) with ESMTP id NAA68148 for ; Mon, 6 Dec 1999 13:19:38 -0500 (EST) (envelope-from frank@x9media.com) Received: (from frank@localhost) by flateric.x9media.com (8.8.7/8.8.7) id TAA18807 for pgsql-sql@postgreSQL.org; Mon, 6 Dec 1999 19:20:32 +0100 Date: Mon, 6 Dec 1999 19:20:32 +0100 From: Frank Joerdens To: pgsql-sql@postgreSQL.org Subject: Re: [SQL] How to avoid "Out of memory" using aggregate functions? Message-ID: <19991206192032.C12528@flateric.x9media.com> References: <19991206135652.B12528@flateric.x9media.com> <18807.944497407@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii User-Agent: Mutt/1.1i In-Reply-To: <18807.944497407@sss.pgh.pa.us>; from tgl@sss.pgh.pa.us on Mon, Dec 06, 1999 at 11:23:27AM -0500 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 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 > > ************