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@xxxxxxxx>
To: "NbForYou" <nbforyou@xxxxxxxxxxx>
Cc: <pgsql-performance@xxxxxxxxxxxxxx>
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@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
