Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id LAA49255 for ; Mon, 6 Dec 1999 11:24:00 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id LAA18809; Mon, 6 Dec 1999 11:23:27 -0500 (EST) To: Frank Joerdens cc: pgsql-sql@postgresql.org Subject: Re: [SQL] How to avoid "Out of memory" using aggregate functions? In-reply-to: Your message of Mon, 6 Dec 1999 13:56:53 +0100 <19991206135652.B12528@flateric.x9media.com> Date: Mon, 06 Dec 1999 11:23:27 -0500 Message-ID: <18807.944497407@sss.pgh.pa.us> From: Tom Lane 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