Re: Different query plans on same servers - Mailing list pgsql-performance

From Mario Splivalo
Subject Re: Different query plans on same servers
Date
Msg-id 4EDEB2E3.4000704@megafon.hr
Whole thread Raw
In response to Re: Different query plans on same servers  ("Kevin Grittner" <Kevin.Grittner@wicourts.gov>)
List pgsql-performance
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

pgsql-performance by date:

Previous
From: Mario Splivalo
Date:
Subject: Re: Different query plans on same servers
Next
From: Mario Splivalo
Date:
Subject: Re: Different query plans on same servers