Re: Different query plans on same servers

On 12/06/2011 09:17 PM, Kevin Grittner wrote:
>
> The hash join path must look more expensive on the first machine,
> for some reason.
>
> Mario, could you post the result of running this query from both
> servers?:
>
> http://wiki.postgresql.org/wiki/Server_Configuration

Sure. Here is from the prod server:

name |
current_setting
-----------------------------+--------------------------------------------------------------------------------------------------------
version | PostgreSQL 8.4.8 on x86_64-pc-linux-gnu,
compiled by GCC gcc-4.3.real (Debian 4.3.2-1.1) 4.3.2, 64-bit
checkpoint_segments | 64
default_statistics_target | 2000
effective_cache_size | 36GB
external_pid_file | /var/run/postgresql/8.4-main.pid
lc_collate | en_US.UTF-8
lc_ctype | en_US.UTF-8
listen_addresses | *
log_autovacuum_min_duration | 0
log_checkpoints | on
log_line_prefix | %t [%p]: [%l-1] [%d]
log_min_duration_statement | 1s
maintenance_work_mem | 256MB
max_connections | 1500
max_stack_depth | 3MB
port | 5432
server_encoding | UTF8
shared_buffers | 4GB
statement_timeout | 30min
temp_buffers | 4096
TimeZone | localtime
track_activity_query_size | 2048
unix_socket_directory | /var/run/postgresql
wal_buffers | 128MB
work_mem | 64MB

And here is from the test server:
name |
current_setting
----------------------------+------------------------------------------------------------------------------------------------------
version | PostgreSQL 8.4.9 on x86_64-pc-linux-gnu,
compiled by GCC gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit
checkpoint_segments | 64
default_statistics_target | 2000
effective_cache_size | 36GB
external_pid_file | /var/run/postgresql/8.4-main.pid
lc_collate | en_US.UTF-8
lc_ctype | en_US.UTF-8
listen_addresses | *
log_connections | on
log_disconnections | on
log_line_prefix | %t [%p]: [%l-1] [%d]
log_min_duration_statement | 0
maintenance_work_mem | 256MB
max_connections | 40
max_stack_depth | 3MB
port | 5432
server_encoding | UTF8
shared_buffers | 4GB
ssl | on
temp_buffers | 4096
TimeZone | localtime
unix_socket_directory | /var/run/postgresql
wal_buffers | 128MB
work_mem | 64MB
(24 rows)

At the time of doing 'explain analyze' on the prod server there were cca
80 connections on the server.

Mario