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