X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 738A8474E5C for ; Tue, 11 Feb 2003 19:02:48 -0500 (EST) Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (Postfix) with ESMTP id EDBA4474E44 for ; Tue, 11 Feb 2003 19:02:47 -0500 (EST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 4850DD5FA; Tue, 11 Feb 2003 16:02:50 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 3E15F5C02; Tue, 11 Feb 2003 16:02:50 -0800 (PST) Date: Tue, 11 Feb 2003 16:02:50 -0800 (PST) From: Stephan Szabo To: Wilkinson Charlie E Cc: Subject: Re: Working with very large datasets In-Reply-To: <9DDDC002B736D711A7320004AC3699EB01C192C7@nct0010mb03.nc.no.irs.gov> Message-ID: <20030211160155.O19527-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/227 X-Sequence-Number: 11625 On Tue, 11 Feb 2003, Wilkinson Charlie E wrote: > Greetings, > Can anyone enlighten me or point me at resources concerning use of pgsql > with > very large datasets? > > My specific problem is this: > > I have two tables, one with about 100 million rows and one with about 22,000 > rows. My plan was to inner join the two tables on an integer key and output > the 4 significant columns, excluding the keys. (Those with a better > understanding > of pgsql internals, feel free to laugh.) The result was a big angry psql > that > grew to 800+MB before I had to kill it. Was it psql that grew to 800Mb or a backend? If the former, how many rows do you expect that to return? You probably want to look into using cursors rather than returning the entire result set at once.