Thread: Process Time X200
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 youOn 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
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 >
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
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
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
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 >
> 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.
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
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.
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