Return-Path: postarch
Received: by postgres.Berkeley.EDU (5.61/1.29)
	id AA17521; Thu, 18 Jun 92 15:29:59 -0700
Message-Id: <9206182229.AA17521@postgres.Berkeley.EDU>
From: postarch (Postgres Mailing Archive)
Subject: Re: join of large tables
To: postgres@postgres.berkeley.edu
Sender: pg_adm@postgres.berkeley.edu
Reply-To: mer@postgres.berkeley.edu
In-Reply-To: Your message of "Thu, 18 Jun 92 15:47:55 +0700."
             <9206181344.AA11781@postgres.Berkeley.EDU> 
Date: Thu, 18 Jun 92 15:29:28 PDT

you write:

> We often get the following message when joining 2 (or more) large tables:
> 
> WARN:Jun 18 15:04:30:not enough memory for hashjoin.
> 
> Is there a way in which we can avoid this problem? Do we have to enlarge
> the swap-space or is it an internal "Postgres" buffer that runs out of memory?

The problem is that postgres is planning hashjoins when it shouldn't.  We
have fixed the planner, and the fix will be available with version 4.  If
you *really* absolutely must have a workaround you can forbid postgres from
ever planning hashjoins with an option.... The only difficulty is that the
option needs to be set from within the postmaster so you would have to change
the code there and recompile:

line 816 reads:

  execl(execFile, execFile, "-p", debugBuf, portBuf, database, NULL);

change it to:

  execl(execFile, execFile, "-fhashjoin", "-p", debugBuf, portBuf, database, NULL);

line 820 reads:

  execl(execFile, execFile, "-p", debugBuf, portBuf, args, database, NULL);

change it to:

  execl(execFile, execFile, "-fhashjoin", "-p", debugBuf, portBuf, args, database, NULL);


Once you have these changes installed run:

	Make postmaster install

Kill your old postmaster and restart the new one.

Jeff Meredith
mer@postgres.berkeley.edu