Thread: Process Time X200

Process Time X200

From
"NbForYou"
Date:
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

Re: Process Time X200

From
Michael Fuhr
Date:
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

Re: Process Time X200

From
"NbForYou"
Date:
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
>

Re: Process Time X200

From
Guido Neitzer
Date:
On 10.03.2006, at 10:11 Uhr, NbForYou wrote:

> So the only solution is to ask my webhost to upgrade its postgresql?

Seems to be.

> The question is will he do that?

You are the customer. If they don't, go to another provider.

> After all a license fee is required for
> commercial use. And running a webhosting service is a commercial use.

No license fee is required for any use of PostgreSQL. Read the license:

"Permission to use, copy, modify, and distribute this software and
its documentation for any purpose, without fee, and without a written
agreement is hereby granted, provided that the above copyright notice
and this paragraph and the following two paragraphs appear in all
copies."

A commercial license is needed for MySQL, not for PostgreSQL.

cug


--
PharmaLine, Essen, GERMANY
Software and Database Development



Attachment

Re: Process Time X200

From
Ragnar
Date:
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



Re: Process Time X200

From
Richard Huxton
Date:
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

Re: Process Time X200

From
"NbForYou"
Date:
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
>

Re: Process Time X200

From
PFC
Date:

> 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.

Re: Process Time X200

From
"Matthew Nuzum"
Date:
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

Re: Process Time X200

From
Scott Marlowe
Date:
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.

Re: Process Time X200

From
"Jim C. Nasby"
Date:
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