Hello,

I could need some help.

I have a Postgresql database

When i do a query on my homeserver the result is given back fast but when i do the same query on my webhost server the query is useless because of the processtime (200 times slower (56366.20 / 281.000 = 200.59) ). My Pc is just a simple pc in reference to the high quality systems my webhost uses.

I have included the query plan and the table

Query:

explain analyze SELECT B.gegevensnaam AS boss, E.gegevensnaam
FROM nieuw_gegevens AS E
LEFT OUTER JOIN
nieuw_gegevens AS B
ON B.lft
= (SELECT MAX(lft)
FROM nieuw_gegevens AS S
WHERE E.lft > S.lft
AND E.lft < S.rgt) order by boss, gegevensnaam

On the WEBHOST:

QUERY PLAN
Sort (cost=1654870.86..1654871.87 rows=403 width=38) (actual time=56365.13..56365.41 rows=403 loops=1)
Sort Key: b.gegevensnaam, e.gegevensnaam
-> Nested Loop (cost=0.00..1654853.42 rows=403 width=38) (actual time=92.76..56360.79 rows=403 loops=1)
Join Filter: ("inner".lft = (subplan))
-> Seq Scan on nieuw_gegevens e (cost=0.00..8.03 rows=403 width=19) (actual time=0.03..1.07 rows=403 loops=1)
-> Seq Scan on nieuw_gegevens b (cost=0.00..8.03 rows=403 width=19) (actual time=0.00..0.79 rows=403 loops=403)
SubPlan
-> Aggregate (cost=10.16..10.16 rows=1 width=4) (actual time=0.34..0.34 rows=1 loops=162409)
-> Seq Scan on nieuw_gegevens s (cost=0.00..10.04 rows=45 width=4) (actual time=0.20..0.33 rows=2 loops=162409)
Filter: (($0 > lft) AND ($0 < rgt))
Total runtime: 56366.20 msec

11 row(s)

Total runtime: 56,370.345 ms


On my HOMESERVER:

QUERY PLAN
Sort (cost=12459.00..12461.04 rows=813 width=290) (actual time=281.000..281.000 rows=403 loops=1)
Sort Key: b.gegevensnaam, e.gegevensnaam
-> Merge Left Join (cost=50.94..12419.71 rows=813 width=290) (actual time=281.000..281.000 rows=403 loops=1)
Merge Cond: ("outer"."?column3?" = "inner".lft)
-> Sort (cost=25.47..26.48 rows=403 width=149) (actual time=281.000..281.000 rows=403 loops=1)
Sort Key: (subplan)
-> Seq Scan on nieuw_gegevens e (cost=0.00..8.03 rows=403 width=149) (actual time=0.000..281.000 rows=403 loops=1)
SubPlan
-> Aggregate (cost=10.16..10.16 rows=1 width=4) (actual time=0.697..0.697 rows=1 loops=403)
-> Seq Scan on nieuw_gegevens s (cost=0.00..10.05 rows=45 width=4) (actual time=0.308..0.658 rows=2 loops=403)
Filter: (($0 > lft) AND ($0 < rgt))
-> Sort (cost=25.47..26.48 rows=403 width=149) (actual time=0.000..0.000 rows=770 loops=1)
Sort Key: b.lft
-> Seq Scan on nieuw_gegevens b (cost=0.00..8.03 rows=403 width=149) (actual time=0.000..0.000 rows=403 loops=1)
Total runtime: 281.000 ms

15 row(s)

Total runtime: 287.273 ms


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!

Table

CREATE TABLE nieuw_gegevens
(
gegevensid int4 NOT NULL DEFAULT nextval('nieuw_gegevens_gegevensid_seq'::text),
gegevensnaam varchar(255) NOT NULL,
lft int4 NOT NULL,
rgt int4 NOT NULL,
keyword text,
CONSTRAINT nieuw_gegevens_pkey PRIMARY KEY (gegevensid),
CONSTRAINT nieuw_gegevens_gegevensnaam_key UNIQUE (gegevensnaam)
)
WITH OIDS;


Does anyone now how to resolve this problem? Could it be that the configuration of the webhost postgresql could me wrong?

thank you

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

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
>

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On fös, 2006-03-10 at 10:11 +0100, NbForYou wrote:
> 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.

A licence fee for what? Certainly not for postgresql.

gnari



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

NbForYou wrote:
> 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.

No, you're thinking of MySQL - PostgreSQL is free for anyone, for any
purpose. You can even distribute your own changes without giving them
back to the community if you want to complicate your life.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Ok, Everybody keeps saying that Postgresql is free...

So I contacted my webhost and their respons was they have to pay a license
fee.

But because they use PLESK as a service I think they are refering to a fee
PLESK charges them
for the use combination PLESK - POSTGRESQL

I do not know however that this information is accurate...

I thank everybody who have responded so far. Great feedback!


----- Original Message -----
From: "Richard Huxton" <dev@archonet.com>
To: "NbForYou" <nbforyou@hotmail.com>
Cc: "Michael Fuhr" <mike@fuhr.org>; <pgsql-performance@postgresql.org>
Sent: Friday, March 10, 2006 10:40 AM
Subject: Re: [PERFORM] Process Time X200


> NbForYou wrote:
>> 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.
>
> No, you're thinking of MySQL - PostgreSQL is free for anyone, for any
> purpose. You can even distribute your own changes without giving them back
> to the community if you want to complicate your life.
>
> --
> Richard Huxton
> Archonet Ltd
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

> Ok, Everybody keeps saying that Postgresql is free...
>
> So I contacted my webhost and their respons was they have to pay a
> license fee.
>
> But because they use PLESK as a service I think they are refering to a
> fee PLESK charges them
> for the use combination PLESK - POSTGRESQL

Probably.
Although in my humble opinion, proposing postgres 7.3 in 2006 is a bit
disrespectful to the considerable work that has been done by the postgres
team since that release.

If you don't find a host to your liking, and you have a large website, as
you say, consider a dedicated server. Prices are quite accessible now, you
can install the latest version of Postgres. Going from 7.3 to 8.1, and
having your own server with all its resources dedicated to running your
site, will probably enhance your performance. Consider lighttpd which is a
speed demon and uses very little resources.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On 3/10/06, NbForYou <nbforyou@hotmail.com> wrote:
> 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
>

You can look at the explain analyze output of the query from pg 7.3,
figure out why the plan is bad and tweak your query to get optimum
performance.

Yes, I agree with the other statements that say, "upgrade to 7.4 or
8.x if you can" but if you can't, then you can still work on it.

--
Matthew Nuzum
www.bearfruit.org

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Fri, 2006-03-10 at 04:45, NbForYou wrote:
> Ok, Everybody keeps saying that Postgresql is free...
>
> So I contacted my webhost and their respons was they have to pay a license
> fee.
>
> But because they use PLESK as a service I think they are refering to a fee
> PLESK charges them
> for the use combination PLESK - POSTGRESQL
>
> I do not know however that this information is accurate...
>
> I thank everybody who have responded so far. Great feedback!

I think it's time to get a new hosting provider.

If they're still running PostgreSQL 7.3.9 (the latest 7.3 is 7.3.14, and
8.1.3 is amazingly faster than 7.3.anything...) then they're likely not
updating other vital components either, and therefore it's only a matter
of time before your machine gets hacked.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On Fri, Mar 10, 2006 at 10:46:56AM -0600, Scott Marlowe wrote:
> I think it's time to get a new hosting provider.
>
> If they're still running PostgreSQL 7.3.9 (the latest 7.3 is 7.3.14, and
> 8.1.3 is amazingly faster than 7.3.anything...) then they're likely not
> updating other vital components either, and therefore it's only a matter
> of time before your machine gets hacked.

Or you lose data. IIRC there have been some data-loss bugs fixed between
7.3.9 and 7.3.14.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend