X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 7E4B69DCA84 for ; Fri, 10 Mar 2006 04:59:51 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 18183-03 for ; Fri, 10 Mar 2006 04:59:52 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by postgresql.org (Postfix) with ESMTP id 493109DC9AC for ; Fri, 10 Mar 2006 04:59:49 -0400 (AST) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.3/8.13.3) with ESMTP id k2A8xiJ7090100 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Fri, 10 Mar 2006 01:59:48 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.13.4/8.13.4) with ESMTP id k2A8xi34008986; Fri, 10 Mar 2006 01:59:44 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.13.4/8.13.4/Submit) id k2A8xiAe008985; Fri, 10 Mar 2006 01:59:44 -0700 (MST) (envelope-from mfuhr) Date: Fri, 10 Mar 2006 01:59:43 -0700 From: Michael Fuhr To: NbForYou Cc: pgsql-performance@postgresql.org Subject: Re: Process Time X200 Message-ID: <20060310085943.GA8913@winnie.fuhr.org> References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.121 required=5 tests=[AWL=0.121] X-Spam-Score: 0.121 X-Spam-Level: X-Archive-Number: 200603/107 X-Sequence-Number: 17544 On Fri, Mar 10, 2006 at 08:11:44AM +0100, NbForYou wrote: > As you can see the query isn't useful anymore because of the > processtime. Please Also notice that both systems use a different > query plan. > Also on the webhost we have a loop of 162409 (403 rows * 403 rows). > Both systems also use a different postgresql version. But I cannot > believe that the performance difference between 1 version could be > this big regarding self outer join queries! What versions are both servers? I'd guess that the webhost is using 7.3 or earlier and you're using 7.4 or later. I created a table like yours, populated it with test data, and ran your query on several versions of PostgreSQL. I saw the same horrible plan on 7.3 and the same good plan on later versions. The 7.4 Release Notes do mention improvements in query planning; apparently one of those improvements is making the difference. -- Michael Fuhr