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