Hey Michael, you sure know your stuff!
Versions:
PostgreSQL 7.3.9-RH running on the webhost.
PostgreSQL 8.0.3 running on my homeserver.
So the only solution is to ask my webhost to upgrade its postgresql?
The question is will he do that? After all a license fee is required for
commercial use. And running a webhosting service is a commercial use.
thanks for replying and going through the effort of creating the database
and populating it.
Nick
----- Original Message -----
From: "Michael Fuhr" <mike@fuhr.org>
To: "NbForYou" <nbforyou@hotmail.com>
Cc: <pgsql-performance@postgresql.org>
Sent: Friday, March 10, 2006 9:59 AM
Subject: Re: [PERFORM] Process Time X200
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>