Subject: join of large tables

From: Peter van Oosterom <[email-masked]>
Date: Thu, 18 Jun 92 15:47:55 MET DST

Dear Postgres group,

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?

Kind regards, Peter van Oosterom.

---

From: postarch (Postgres Mailing Archive)
Date: Thu, 18 Jun 92 15:29:28 PDT

> 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
[email-masked]