Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 8DAB89FA2FF for ; Fri, 2 Nov 2007 01:11:51 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 12039-08 for ; Fri, 2 Nov 2007 01:11:41 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from tomts16-srv.bellnexxia.net (tomts16.bellnexxia.net [209.226.175.4]) by postgresql.org (Postfix) with ESMTP id B03269FA303 for ; Fri, 2 Nov 2007 01:11:41 -0300 (ADT) Received: from toip6.srvr.bell.ca ([209.226.175.125]) by tomts16-srv.bellnexxia.net (InterMail vM.5.01.06.13 201-253-122-130-113-20050324) with ESMTP id <20071102041140.JGJR574.tomts16-srv.bellnexxia.net@toip6.srvr.bell.ca> for ; Fri, 2 Nov 2007 00:11:40 -0400 X-IronPort-Anti-Spam-Filtered: true X-IronPort-Anti-Spam-Result: Ao8CAG5AKkdGNEEJ/2dsb2JhbAA Received: from bas3-montreal45-1177829641.dsl.bell.ca (HELO serenity) ([70.52.65.9]) by toip6.srvr.bell.ca with ESMTP; 02 Nov 2007 00:09:41 -0400 From: "Carlo Stonebanks" To: "'Tom Lane'" Cc: References: <15853.1193953318@sss.pgh.pa.us> In-Reply-To: <15853.1193953318@sss.pgh.pa.us> Subject: Re: How to avoid hashjoin and mergejoin Date: Fri, 2 Nov 2007 00:12:29 -0400 Message-ID: <50744AFF4B714B23878E2BC2E9E697B9@serenity> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook 11 Thread-Index: Acgc0BYfeB7jRjWjTtm4XO/b08s/iQAMn2eQ X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6000.16545 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/29 X-Sequence-Number: 27444 <> In this particular example, this was done to "force" the query on the Linux box to use the same plan as on the Windows box to prove that - once the correct plan was chosen - the Linux box could at least MATCH the Windows box. That being said, I should mention this: we take certain "core" queries that we know are essential and embed them in a plpgsql SRF's that save the various settings, modify them as required for the query, then restore them after the rows are returned. Does this address the problem you mentioned? << What I'm wondering is whether the tables have been ANALYZEd recently,>> This is SUPPOSED to be done after a restore - but I will verify, thanks for the reminder. << and also whether there are any nondefault postgresql.conf settings in use on the other server.>> Definitely - this is what alerted me to the fact that there was something suspicious. We try to optimize our memory settings (based on various tuning docs, advice from here, and good old trial-and-error). Since the new config had barely any changes, I knew something was wrong. Carlo -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: November 1, 2007 5:42 PM To: Carlo Stonebanks Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] How to avoid hashjoin and mergejoin "Carlo Stonebanks" writes: > 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'; This is just about never the appropriate way to solve a performance problem, as it will inevitably create performance problems in other queries. What I'm wondering is whether the tables have been ANALYZEd recently, and also whether there are any nondefault postgresql.conf settings in use on the other server. regards, tom lane