To: Kirk Wythers <kwythers@umn.edu>
cc: "Steinar H. Gunderson" <sgunderson@bigfoot.com>,
	=?ISO-8859-1?Q?G=E1briel_=C1kos?= <akos.gabriel@i-logic.hu>,
	pgsql-performance@postgresql.org
Subject: Re: trouble with a join on OS X 
In-reply-to: <1189F1A6-7CB8-40C3-9CE0-24AE95EFB5F9@umn.edu> 
References: <8FD54F8B-792B-4333-8C73-AAFD2992C17F@umn.edu>
	<45C34E80.7010805@archonet.com> <45C34FC6.2020704@i-logic.hu>
	<21989.1170431213@sss.pgh.pa.us>
	<079F46AF-287E-4FC5-818E-B593C8C73D06@umn.edu>
	<20070202160903.GA21442@uio.no> <25286.1170432709@sss.pgh.pa.us>
	<1189F1A6-7CB8-40C3-9CE0-24AE95EFB5F9@umn.edu>
Comments: In-reply-to Kirk Wythers <kwythers@umn.edu>
	message dated "Fri, 02 Feb 2007 11:54:20 -0600"
Date: Fri, 02 Feb 2007 17:18:56 -0500
Message-ID: <24768.1170454736@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Kirk Wythers <kwythers@umn.edu> writes:
> However, setting ulimit to unlimited does not seem to solve the  
> issue.

After some experimentation I'm left wondering exactly what ulimit's -d
option is for on OS X, because it sure doesn't seem to be limiting
process data size.  (I should have been suspicious of a value as small
as 6 meg, anyway.)  I tried selecting a large unconstrained join on my own
OS X machine, and what I saw (watching with "top") was that the psql
process VSIZE went up to 1.75Gb before it failed with the same error as
Kirk got:

regression=# select * from tenk1 a , tenk1 b;
psql(16572) malloc: *** vm_allocate(size=8421376) failed (error code=3)
psql(16572) malloc: *** error: can't allocate region
psql(16572) malloc: *** set a breakpoint in szone_error to debug

Since this is just a bog-standard Mini with 512M memory, it was pretty
thoroughly on its knees by this point :-(.  I'm not sure how to find out
about allocated swap space in OS X, but my bet is that the above message
should be understood as "totally out of virtual memory".

My suggestion is to use a cursor to retrieve the data in more
manageably-sized chunks than 7M rows.  (If you don't want to mess with
managing a cursor explicitly, as of 8.2 there's a psql variable
FETCH_COUNT that can be set to make it happen behind the scenes.)

			regards, tom lane