From: Carlo Stonebanks <stonec.register@sympatico.ca>
To: pgsql-performance@postgresql.org
Subject: How to avoid hashjoin and mergejoin
Date: Thu, 1 Nov 2007 16:57:34 -0400
Message-ID: <fgdeia$763$1@news.hub.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: stonec.register@sympatico.ca
Subject: Re: How to avoid hashjoin and mergejoin
In-Reply-To: <fgdeia$763$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox