[PERFORM] Query 200x slower on server [PART 2]
See Query 200x slower on server [PART 1] before reading any
further
QUERY PLAN ON MY HOME SERVER
Sort (cost=1516.55..1516.59 rows=15 width=640) (actual
time=123.008..123.435 rows=1103 loops=1) Sort Key:
aanmaakdatum - Subquery Scan producttabel
(cost=1515.39..1516.26 rows=15 width=640) (actual time=112.890..119.067
rows=1103 loops=1) -
Unique (cost=1515.39..1516.11 rows=15 width=834) (actual
time=112.886..117.950 rows=1103
loops=1)
InitPlan
- Index Scan using geg_winkel_pkey on geg_winkel
(cost=0.00..5.44 rows=1 width=4) (actual time=0.022..0.023 rows=1
loops=1)
Index Cond: (winkelid =
0)
- Index Scan using geg_winkel_pkey on geg_winkel
(cost=0.00..5.44 rows=1 width=4) (actual time=0.004..0.005 rows=1
loops=1)
Index Cond: (winkelid =
0)
- Group (cost=1504.51..1505.18 rows=15 width=834) (actual
time=112.880..115.682 rows=1136
loops=1)
- Sort (cost=1504.51..1504.55 rows=15 width=834) (actual
time=112.874..113.255 rows=1137
loops=1)
Sort Key: p.productid, p.serienummer, p.artikelnaam, p.inkoopprijs,
p.vasteverkoopprijs, gegw.winkelid, gegw.winkelnaam, gegw.winkelnaamnl,
gegw.winkelnaamenkelvoud, gegw.winkelnaamenkelvoudnl, defg.genrenaam,
defg.genrenaamnl, p.
(..)
- Hash Join (cost=925.74..1504.22 rows=15 width=834) (actual
time=34.143..107.937 rows=1137
loops=1)
Hash Cond: ("outer".leverancierid =
"inner".leverancierid)
- Nested Loop (cost=924.29..1502.54 rows=15 width=829) (actual
time=34.041..105.706 rows=1137
loops=1)
- Hash Join (cost=924.29..1399.67 rows=20 width=829) (actual
time=32.698..71.780 rows=3852
loops=1)
Hash Cond: ("outer".winkelid =
"inner".winkelid)
- Hash Left Join (cost=918.33..1373.61 rows=3981 width=249)
(actual time=31.997..64.938 rows=3852
loops=1)
Hash Cond: ("outer".genreid =
"inner".genreid)
- Hash Left Join (cost=917.14..1312.71 rows=3981 width=117)
(actual time=31.946..60.961 rows=3852
loops=1)
Hash Cond: ("outer"..onderwerpid)
- Hash Left Join (cost=904.72..1240.57 rows=3981 width=117)
(actual time=31.104..56.264 rows=3852
loops=1)
Hash Cond: ("outer"..onderwerpid)
- Merge Right Join (cost=890.28..1166.42 rows=3981 width=101)
(actual time=29.938..50.406 rows=3852
loops=1)
Merge Cond: ("outer".productid =
"inner".productid)
- Index Scan using koppel_product_onderwerp_pkey on
koppel_product_onderwerp kpo (cost=0.00..216.34 rows=5983 width=8) (actual
time=0.011..8.537 rows=5965
loops=1)
- Sort (cost=890.28..900.23 rows=3981 width=97) (actual
time=29.918..31.509 rows=3852
loops=1)
Sort Key:
p.productid
- Seq Scan on product p (cost=0.00..652.24 rows=3981 width=97)
(actual time=0.012..18.012 rows=3819
loops=1)
Filter: (afdelingid =
1)
- Hash (cost=12.75..12.75 rows=675 width=20) (actual
time=1.119..1.119 rows=675
loops=1)
- Seq Scan on geg_onderwerp gego (cost=0.00..12.75 rows=675
width=20) (actual time=0.010..0.598 rows=675
loops=1)
- Hash (cost=10.74..10.74 rows=674 width=8) (actual
time=0.822..0.822 rows=674
loops=1)
- Seq Scan on koppel_onderwerp_genre kog (cost=0.00..10.74
rows=674 width=8) (actual time=0.010..0.423 rows=674
loops=1)
- Hash (cost=1.15..1.15 rows=15 width=140) (actual
time=0.033..0.033 rows=15
loops=1)
- Seq Scan on geg_genre defg (cost=0.00..1.15 rows=15 width=140)
(actual time=0.004..0.017 rows=15
loops=1)
- Hash (cost=5.96..5.96 rows=1 width=584) (actual
time=0.682..0.682 rows=197
loops=1)
- Seq Scan on geg_winkel gegw (cost=0.00..5.96 rows=1 width=584)
(actual time=0.042..0.390 rows=197
loops=1)
Filter: ((lft = $0) AND (lft =
$1))
- Index Scan using product_eigenschap_key on product_eigenschap
pe (cost=0.00..5.13 rows=1 width=4) (actual time=0.006..0.007 rows=0
loops=3852)
Index Cond: ("outer".productid =
pe.productid)
Filter: (stocktypeid
3)
- Hash (cost=1.36..1.36 rows=36 width=13) (actual
time=0.081..0.081 rows=36
loops=1)
- Seq Scan on geg_leverancier dl (cost=0.00..1.36 rows=36
width=13) (actual time=0.010..0.042 rows=36 loops=1)Total runtime: 125.432
ms
This means that the Query is 200 times slower on the
webhost!
How can I resolve this?
Re: [PERFORM] database model tshirt sizes
So a default value for all products would be size:all for example, the same tshirt shop also sells cdroms. It size attribute would be to place it to be :all. (because we cannot place an uniqe index on null values) But the industry evolves and so in time the same cdrom is now available for pc and playstation. So i would like to have it as 1 productid but with different attributes: pc (with quantity 5) and playstation (with quantity 3). So when I do an insert for this 2 products with 1 productid it would be like: insert into versions (productid,size,quantity) values (345,'pc',5); insert into versions (productid,size,quantity) values (345,'playstation',3); if however the product existed we get an error: because the default value version all did also exist and is now obsolete population versions: productid: 123, versionid: 1, color: 'black', size: 'all', quantity: 11 productid: 442, versionid: 2, color: 'yellow', size: 'l', quantity: 1 productid: 442, versionid: 2, color: 'yellow', size: 's', quantity: 4 productid: 442, versionid: 2, color: 'red', size: 'xl', quantity: 9 productid: 442, versionid: 2, color: 'blue', size: 's', quantity: 0 productid: 345, versionid: 3, color: null, size: 'all', quantity: 15 productid: 345, versionid: 3, color: null, size: 'pc', quantity: 5 productid: 345, versionid: 3, color: null, size: 'playstation', quantity: 3 WOULD HAVE TO BE: population versions: productid: 123, versionid: 1, color: 'black', size: 'all', quantity: 11 productid: 442, versionid: 2, color: 'yellow', size: 'l', quantity: 1 productid: 442, versionid: 2, color: 'yellow', size: 's', quantity: 4 productid: 442, versionid: 2, color: 'red', size: 'xl', quantity: 9 productid: 442, versionid: 2, color: 'blue', size: 's', quantity: 0 productid: 345, versionid: 3, color: null, size: 'pc', quantity: 5 productid: 345, versionid: 3, color: null, size: 'playstation', quantity: 3 ALSO: what is versionid used for? - Original Message - From: [EMAIL PROTECTED] To: NbForYou [EMAIL PROTECTED] Cc: pgsql-performance@postgresql.org; [EMAIL PROTECTED] Sent: Sunday, March 19, 2006 3:37 PM Subject: Re: [PERFORM] database model tshirt sizes another approach would be: table product: productid int8 PK productname charvar(255) table versions productid int8 FK versionid int8 PK size color ... quantity int4 an example would be then: table product: - productid: 123, productname: 'nice cotton t-shirt' - productid: 442, productname: 'another cotton t-shirt' table versions: - productid: 123, versionid: 1, color: 'black', size: 'all', quantity: 11 - productid: 442, versionid: 2, color: 'yellow', size: 'l', quantity: 1 - productid: 442, versionid: 2, color: 'yellow', size: 's', quantity: 4 - productid: 442, versionid: 2, color: 'red', size: 'xl', quantity: 9 - productid: 442, versionid: 2, color: 'blue', size: 's', quantity: 0 that way you can have more than 1 quantity / color / size combination per product and still have products that come in one size. so instead of only using a 2nd table for cases where more than one size is available, you would always use a 2nd table. this probably reduces your code complexity quite a bit and only needs 1 JOIN. - thomas - Original Message - From: Patrick Hatcher [EMAIL PROTECTED] To: NbForYou [EMAIL PROTECTED] Cc: pgsql-performance@postgresql.org; [EMAIL PROTECTED] Sent: Sunday, March 19, 2006 2:59 PM Subject: Re: [PERFORM] database model tshirt sizes We have size and color in the product table itself. It is really an attribute of the product. If you update the availability of the product often, I would split out the quantity into a separate table so that you can truncate and update as needed. Patrick Hatcher Development Manager Analytics/MIO Macys.com NbForYou [EMAIL PROTECTED] .com To Sent by: pgsql-performance@postgresql.org pgsql-performance cc [EMAIL PROTECTED] .org Subject [PERFORM] database model tshirt sizes 03/18/06 07:03 AM Hello, Does anybody know how to build a database model to include sizes for rings, tshirts, etc? the current database is built like: table product = productid int8 PK productname charvar(255) quantity int4 what i want now is that WHEN (not all products have multiple sizes) there are multiple sizes available. The sizes are stored into the database. I was wondering to include a extra table: table sizes: productid int8 FK size varchar(100) but then i have a quantity problem. Because now not all size quantities can be stored into this table, because it allready exist in my product table. How do
[PERFORM] database model tshirt sizes
Hello, Does anybody know how to build a database model to include sizes for rings, tshirts, etc? the current database is built like: table product = productid int8 PK productname charvar(255) quantity int4 what i want now is that WHEN (not all products have multiple sizes) there are multiple sizes available. The sizes are stored into the database. I was wondering to include a extra table: table sizes: productid int8 FK size varchar(100) but then i have a quantity problem. Because now not all size quantities can be stored into this table, because it allready exist in my product table. How do professionals do it? How do they make their model to include sizes if any available?
Re: [PERFORM] Process Time X200
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 [EMAIL PROTECTED] To: NbForYou [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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
Re: [PERFORM] Process Time X200
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 [EMAIL PROTECTED] Cc: Michael Fuhr [EMAIL PROTECTED]; 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
[PERFORM] Process Time X200
Hello,
I could need some help.
I have a Postgresql
databaseWhen 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, gegevensnaamOn
theWEBHOST: 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 msOn 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 msAs you can see the
query isn't usefulanymore becauseof the processtime. Please Also
notice that both systems use a differentquery 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
