From: "Carlo Stonebanks" <stonec.register@sympatico.ca>
Subject: How to avoid hashjoin and mergejoin
Date: Thu, 1 Nov 2007 16:57:34 -0400
Organization: Hub.Org Networking Services
Lines: 28
Message-ID: <fgdeia$763$1@news.hub.org>
Mime-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
	reply-type=original
Content-Transfer-Encoding: 7bit
To: pgsql-performance@postgresql.org

I am comparing the same query on two different PG 8.2 servers, one Linux 
(8GB RAM) and one Windows (32GB RAM). Both have similar drives and CPU's.

The Windows posgrestsql.config is pretty well tuned but it looks like 
someone had wiped out the Linux config so the default one was re-installed. 
All performance-related memory allocation values seem to be set to the 
defaults, but mods have been made: max_connections = 100 and shared_buffers 
= 32MB.

The performance for this query is terrible on the Linux server, and good on 
the Windows server - presumably because the original Linux PG config has 
been lost. This query requires: that "set enable_seqscan to 'off';"

Still, the Linux server did not create the same, fast plan as the Windows 
server. In order to get the same plan we had to:

set enable_hashjoin to 'off';
set enable_mergejoin to 'off';

The plans were now similar, using nested loops and bitmapped heap scans. Now 
the Linux query outperformed the Windows query.

Question: Can anyone tell me which config values would have made PG select 
hash join and merge joins when the nested loop/bitmap heap scan combination 
was faster?

Carlo