Re: Process Time X200

From: Michael Fuhr
Subject: Re: Process Time X200
Date: ,
Msg-id: 20060310085943.GA8913@winnie.fuhr.org
(view: Whole thread, Raw)
In response to: Process Time X200  ("NbForYou")
List: pgsql-performance

Tree view

Process Time X200  ("NbForYou", )
 Re: Process Time X200  (Michael Fuhr, )
 Re: Process Time X200  ("NbForYou", )
  Re: Process Time X200  (Guido Neitzer, )
  Re: Process Time X200  (Ragnar, )
  Re: Process Time X200  (Richard Huxton, )
  Re: Process Time X200  ("Matthew Nuzum", )
 Re: Process Time X200  ("NbForYou", )
  Re: Process Time X200  (PFC, )
  Re: Process Time X200  (Scott Marlowe, )
   Re: Process Time X200  ("Jim C. Nasby", )

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


pgsql-performance by date:

From: Jan de Visser
Date:
Subject: Re: Hanging queries on dual CPU windows
From: "Magnus Hagander"
Date:
Subject: Re: Hanging queries on dual CPU windows