Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 44B4D9FB610 for ; Thu, 15 Feb 2007 10:28:44 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 97537-05 for ; Thu, 15 Feb 2007 10:28:14 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from grassmarket.ucs.ed.ac.uk (grassmarket.ucs.ed.ac.uk [129.215.166.64]) by postgresql.org (Postfix) with ESMTP id 7F33D9FB5D2 for ; Thu, 15 Feb 2007 10:28:13 -0400 (AST) Received: from [129.215.2.130] (dhcp-2-130.inf.ed.ac.uk [129.215.2.130]) by grassmarket.ucs.ed.ac.uk (8.13.8/8.12.10) with ESMTP id l1FESAMg016240; Thu, 15 Feb 2007 14:28:10 GMT Subject: Re: Problem with joining queries. From: Konstantinos Krikellas To: Richard Huxton Cc: pgsql-performance@postgresql.org, Stratis Viglas In-Reply-To: <45D467C6.5090501@archonet.com> References: <1171544313.31678.22.camel@kkrik-desktop> <45D467C6.5090501@archonet.com> Content-Type: multipart/alternative; boundary="=-uarCVZLts1ZnJbMoM8Vm" Date: Thu, 15 Feb 2007 14:28:27 +0000 Message-Id: <1171549707.31678.29.camel@kkrik-desktop> Mime-Version: 1.0 X-Mailer: Evolution 2.8.1 X-Edinburgh-Scanned: at grassmarket.ucs.ed.ac.uk with MIMEDefang 2.33, Sophie, Sophos Anti-Virus X-Scanned-By: MIMEDefang 2.33 (www . roaringpenguin . com / mimedefang) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.792 tagged_above=0 required=5 tests=AWL, BAYES_50, HTML_40_50, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200702/168 X-Sequence-Number: 23222 --=-uarCVZLts1ZnJbMoM8Vm Content-Type: text/plain Content-Transfer-Encoding: 7bit > Not happening here (8.2.x, output redirected using "\o /dev/null") - are > you sure it's not psql (or whatever client) that's using up your memory, > as it tries to build the entire result set before sending it to > /dev/null? Don't forget, you've got 5 copies of the columns so that > would be ~ 700MB. > > If it is the backend, you'll need to give some of the tuning parameters > you're using, since it works here on my much smaller dev server (1GB RAM > and plenty of other stuff using it). You are absolutely right about the result size. I tried the '\o /dev/null' and worked, I had not realised the client is buffering the final result. Instead, I used to execute the command 'psql < query.sql > /dev/null', so the psql process consumed all the available memory. Thank you very much for your immediate and felicitous response. Kind regards, Konstantinos Krikellas PhD student, Database Group University of Edinburgh Email: K.Krikellas@sms.ed.ac.uk Pnone number: +44 (0) 131 651 3769 --=-uarCVZLts1ZnJbMoM8Vm Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 7bit
Not happening here (8.2.x, output redirected using "\o /dev/null") - are 
you sure it's not psql (or whatever client) that's using up your memory, 
as it tries to build the entire result set before sending it to 
/dev/null? Don't forget, you've got 5 copies of the columns so that 
would be ~ 700MB.

If it is the backend, you'll need to give some of the tuning parameters 
you're using, since it works here on my much smaller dev server (1GB RAM 
and plenty of other stuff using it).

You are absolutely right about the result size.

I tried the '\o /dev/null' and worked, I had not realised the client is buffering the final result.
Instead, I used to execute the command 'psql < query.sql > /dev/null', so the psql process consumed all the available memory.

Thank you very much for your immediate and felicitous response.

Kind regards, 
Konstantinos Krikellas
PhD student, Database Group
University of Edinburgh
Email: K.Krikellas@sms.ed.ac.uk
Pnone number: +44 (0) 131 651 3769 
--=-uarCVZLts1ZnJbMoM8Vm--