From: Tom Lane <tgl@sss.pgh.pa.us>
To: Frank Joerdens <frank@x9media.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] How to avoid "Out of memory" using aggregate functions?
Date: Mon, 06 Dec 1999 11:23:27 -0500
Message-ID: <18807.944497407@sss.pgh.pa.us> (raw)
In-Reply-To: <19991206135652.B12528@flateric.x9media.com>
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
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: tgl@sss.pgh.pa.us, frank@x9media.com
Subject: Re: [SQL] How to avoid "Out of memory" using aggregate functions?
In-Reply-To: <18807.944497407@sss.pgh.pa.us>
References: <19991206135652.B12528@flateric.x9media.com> <18807.944497407@sss.pgh.pa.us>
* 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