Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id ACD8719713C9 for ; Tue, 6 Dec 2011 20:29:25 -0400 (AST) Received: from out1.mail.amis.hr ([188.129.0.4]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1RY5Nz-0006Sh-9E for pgsql-performance@postgresql.org; Wed, 07 Dec 2011 00:29:24 +0000 Received: from out.fsec.sm.srv.amis.hr (out.fsec.sm.srv.amis.hr [192.168.106.22]) by out1.mail.amis.hr (Postfix) with ESMTP id ECF291FC98 for ; Wed, 7 Dec 2011 01:29:10 +0100 (CET) Received: from pps.filterd (out [127.0.0.1]) by out.fsec.sm.srv.amis.hr (8.14.3/8.14.3) with SMTP id pB70TAtQ001649 for ; Wed, 7 Dec 2011 01:29:10 +0100 Received: from smtp.amis.hr (smtp.sm.srv.amis.hr [192.168.106.39]) by out.fsec.sm.srv.amis.hr with ESMTP id 11hmqcr5bd-1 for ; Wed, 07 Dec 2011 01:29:10 +0100 A-Original-Client-UID: 188.129.104.49 splivalo Received: from [192.168.10.1] (cpe-188-129-104-49.dynamic.amis.hr [188.129.104.49]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) (Authenticated sender: splivalo) by smtp.amis.hr (Postfix) with ESMTPSA id 2D4A9398 for ; Wed, 7 Dec 2011 01:29:10 +0100 (CET) Message-ID: <4EDEB2E3.4000704@megafon.hr> Date: Wed, 07 Dec 2011 01:27:15 +0100 From: Mario Splivalo User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.2.23) Gecko/20110922 Thunderbird/3.1.15 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Different query plans on same servers References: <4EDE717A.9050604@megafon.hr><4EDE717A.9050604@megafon.hr> <14289.1323201649@sss.pgh.pa.us> <4EDE23FA020000250004393F@gw.wicourts.gov> In-Reply-To: <4EDE23FA020000250004393F@gw.wicourts.gov> Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Proofpoint-Virus-Version: vendor=fsecure engine=2.50.10432:5.5.7110, 1.0.211, 0.0.0000 definitions=2011-12-06_05:2011-12-06, 2011-12-06, 1970-01-01 signatures=0 X-Proofpoint-Spam-Details: rule=notspam policy=default score=0 spamscore=0 ipscore=0 suspectscore=1 phishscore=0 bulkscore=0 adultscore=0 classifier=spam adjust=0 reason=mlx engine=6.0.2-1012030000 definitions=main-1112060284 X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201112/51 X-Sequence-Number: 45850 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