X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id E877A9FB20A for ; Thu, 27 Jul 2006 13:45:46 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 17468-08 for ; Thu, 27 Jul 2006 13:45:42 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from bay0-omc3-s39.bay0.hotmail.com (bay0-omc3-s39.bay0.hotmail.com [65.54.246.239]) by postgresql.org (Postfix) with ESMTP id 03BAD9FA682 for ; Thu, 27 Jul 2006 13:45:11 -0300 (ADT) Received: from hotmail.com ([207.46.11.17]) by bay0-omc3-s39.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.1830); Thu, 27 Jul 2006 09:45:10 -0700 Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Thu, 27 Jul 2006 09:45:07 -0700 Message-ID: Received: from 84.193.85.191 by BAY123-DAV7.phx.gbl with DAV; Thu, 27 Jul 2006 16:45:06 +0000 X-Originating-IP: [84.193.85.191] X-Originating-Email: [nbforyou@hotmail.com] X-Sender: nbforyou@hotmail.com From: "NbForYou" To: Subject: Query 200x slower on server [PART 2] Date: Thu, 27 Jul 2006 18:45:03 +0200 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0063_01C6B1AC.C56FAF40" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2869 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869 X-OriginalArrivalTime: 27 Jul 2006 16:45:07.0587 (UTC) FILETIME=[048AFD30:01C6B19C] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.954 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, HTML_MESSAGE, MSGID_FROM_MTA_HEADER, SPF_PASS X-Spam-Level: X-Archive-Number: 200607/293 X-Sequence-Number: 20153 This is a multi-part message in MIME format. ------=_NextPart_000_0063_01C6B1AC.C56FAF40 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable See Query 200x slower on server [PART 1] before reading any further QUERY PLAN ON MY HOME SERVER Sort (cost=3D1516.55..1516.59 rows=3D15 width=3D640) (actual = time=3D123.008..123.435 rows=3D1103 loops=3D1) Sort Key: aanmaakdatum -> Subquery Scan producttabel (cost=3D1515.39..1516.26 rows=3D15 = width=3D640) (actual time=3D112.890..119.067 rows=3D1103 loops=3D1) -> Unique (cost=3D1515.39..1516.11 rows=3D15 width=3D834) = (actual time=3D112.886..117.950 rows=3D1103 loops=3D1) InitPlan -> Index Scan using geg_winkel_pkey on geg_winkel = (cost=3D0.00..5.44 rows=3D1 width=3D4) (actual time=3D0.022..0.023 = rows=3D1 loops=3D1) Index Cond: (winkelid =3D 0) -> Index Scan using geg_winkel_pkey on geg_winkel = (cost=3D0.00..5.44 rows=3D1 width=3D4) (actual time=3D0.004..0.005 = rows=3D1 loops=3D1) Index Cond: (winkelid =3D 0) -> Group (cost=3D1504.51..1505.18 rows=3D15 width=3D834) = (actual time=3D112.880..115.682 rows=3D1136 loops=3D1) -> Sort (cost=3D1504.51..1504.55 rows=3D15 = width=3D834) (actual time=3D112.874..113.255 rows=3D1137 loops=3D1) 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=3D925.74..1504.22 = rows=3D15 width=3D834) (actual time=3D34.143..107.937 rows=3D1137 = loops=3D1) Hash Cond: ("outer".leverancierid =3D = "inner".leverancierid) -> Nested Loop (cost=3D924.29..1502.54 = rows=3D15 width=3D829) (actual time=3D34.041..105.706 rows=3D1137 = loops=3D1) -> Hash Join = (cost=3D924.29..1399.67 rows=3D20 width=3D829) (actual = time=3D32.698..71.780 rows=3D3852 loops=3D1) Hash Cond: ("outer".winkelid = =3D "inner".winkelid) -> Hash Left Join = (cost=3D918.33..1373.61 rows=3D3981 width=3D249) (actual = time=3D31.997..64.938 rows=3D3852 loops=3D1) Hash Cond: = ("outer".genreid =3D "inner".genreid) -> Hash Left Join = (cost=3D917.14..1312.71 rows=3D3981 width=3D117) (actual = time=3D31.946..60.961 rows=3D3852 loops=3D1) Hash Cond: = ("outer".onderwerpid =3D "inner".onderwerpid) -> Hash Left = Join (cost=3D904.72..1240.57 rows=3D3981 width=3D117) (actual = time=3D31.104..56.264 rows=3D3852 loops=3D1) Hash Cond: = ("outer".onderwerpid =3D "inner".onderwerpid) -> Merge = Right Join (cost=3D890.28..1166.42 rows=3D3981 width=3D101) (actual = time=3D29.938..50.406 rows=3D3852 loops=3D1) = Merge Cond: ("outer".productid =3D "inner".productid) -> = Index Scan using koppel_product_onderwerp_pkey on = koppel_product_onderwerp kpo (cost=3D0.00..216.34 rows=3D5983 = width=3D8) (actual time=3D0.011..8.537 rows=3D5965 loops=3D1) -> = Sort (cost=3D890.28..900.23 rows=3D3981 width=3D97) (actual = time=3D29.918..31.509 rows=3D3852 loops=3D1) = Sort Key: p.productid = -> Seq Scan on product p (cost=3D0.00..652.24 rows=3D3981 width=3D97) = (actual time=3D0.012..18.012 rows=3D3819 loops=3D1) = Filter: (afdelingid =3D 1) -> Hash = (cost=3D12.75..12.75 rows=3D675 width=3D20) (actual time=3D1.119..1.119 = rows=3D675 loops=3D1) -> = Seq Scan on geg_onderwerp gego (cost=3D0.00..12.75 rows=3D675 = width=3D20) (actual time=3D0.010..0.598 rows=3D675 loops=3D1) -> Hash = (cost=3D10.74..10.74 rows=3D674 width=3D8) (actual time=3D0.822..0.822 = rows=3D674 loops=3D1) -> Seq = Scan on koppel_onderwerp_genre kog (cost=3D0.00..10.74 rows=3D674 = width=3D8) (actual time=3D0.010..0.423 rows=3D674 loops=3D1) -> Hash = (cost=3D1.15..1.15 rows=3D15 width=3D140) (actual time=3D0.033..0.033 = rows=3D15 loops=3D1) -> Seq Scan on = geg_genre defg (cost=3D0.00..1.15 rows=3D15 width=3D140) (actual = time=3D0.004..0.017 rows=3D15 loops=3D1) -> Hash (cost=3D5.96..5.96 = rows=3D1 width=3D584) (actual time=3D0.682..0.682 rows=3D197 loops=3D1) -> Seq Scan on = geg_winkel gegw (cost=3D0.00..5.96 rows=3D1 width=3D584) (actual = time=3D0.042..0.390 rows=3D197 loops=3D1) Filter: ((lft = >=3D $0) AND (lft <=3D $1)) -> Index Scan using = product_eigenschap_key on product_eigenschap pe (cost=3D0.00..5.13 = rows=3D1 width=3D4) (actual time=3D0.006..0.007 rows=3D0 loops=3D3852) Index Cond: = ("outer".productid =3D pe.productid) Filter: (stocktypeid < 3) -> Hash (cost=3D1.36..1.36 rows=3D36 = width=3D13) (actual time=3D0.081..0.081 rows=3D36 loops=3D1) -> Seq Scan on geg_leverancier dl = (cost=3D0.00..1.36 rows=3D36 width=3D13) (actual time=3D0.010..0.042 = rows=3D36 loops=3D1) Total runtime: 125.432 ms This means that the Query is 200 times slower on the webhost! How can I resolve this? ------=_NextPart_000_0063_01C6B1AC.C56FAF40 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
See Query 200x slower on server [PART 1] before = reading any=20 further
 
QUERY PLAN ON MY HOME SERVER
Sort  (cost=3D1516.55..1516.59 rows=3D15 = width=3D640) (actual=20 time=3D123.008..123.435 rows=3D1103 loops=3D1)
  Sort Key:=20 aanmaakdatum
  ->  Subquery Scan producttabel =20 (cost=3D1515.39..1516.26 rows=3D15 width=3D640) (actual = time=3D112.890..119.067=20 rows=3D1103 loops=3D1)
        = -> =20 Unique  (cost=3D1515.39..1516.11 rows=3D15 width=3D834) (actual=20 time=3D112.886..117.950 rows=3D1103=20 loops=3D1)
          = ;   =20 InitPlan
          &= nbsp;    =20 ->  Index Scan using geg_winkel_pkey on geg_winkel =20 (cost=3D0.00..5.44 rows=3D1 width=3D4) (actual time=3D0.022..0.023 = rows=3D1=20 loops=3D1)
          = ;           =20 Index Cond: (winkelid =3D=20 0)
           &= nbsp;   =20 ->  Index Scan using geg_winkel_pkey on geg_winkel =20 (cost=3D0.00..5.44 rows=3D1 width=3D4) (actual time=3D0.004..0.005 = rows=3D1=20 loops=3D1)
          = ;           =20 Index Cond: (winkelid =3D=20 0)
           &= nbsp; =20 ->  Group  (cost=3D1504.51..1505.18 rows=3D15 width=3D834) = (actual=20 time=3D112.880..115.682 rows=3D1136=20 loops=3D1)
          = ;         =20 ->  Sort  (cost=3D1504.51..1504.55 rows=3D15 width=3D834) = (actual=20 time=3D112.874..113.255 rows=3D1137=20 loops=3D1)
          = ;            =    =20 Sort Key: p.productid, p.serienummer, p.artikelnaam, p.inkoopprijs,=20 p.vasteverkoopprijs, gegw.winkelid, gegw.winkelnaam, gegw.winkelnaamnl,=20 gegw.winkelnaamenkelvoud, gegw.winkelnaamenkelvoudnl, defg.genrenaam,=20 defg.genrenaamnl, p.=20 (..)
           = ;            =   =20 ->  Hash Join  (cost=3D925.74..1504.22 rows=3D15 = width=3D834) (actual=20 time=3D34.143..107.937 rows=3D1137=20 loops=3D1)
          = ;            =          =20 Hash Cond: ("outer".leverancierid =3D=20 "inner".leverancierid)
        = ;            =            =20 ->  Nested Loop  (cost=3D924.29..1502.54 rows=3D15 = width=3D829) (actual=20 time=3D34.041..105.706 rows=3D1137=20 loops=3D1)
          = ;            =             &= nbsp;  =20 ->  Hash Join  (cost=3D924.29..1399.67 rows=3D20 = width=3D829) (actual=20 time=3D32.698..71.780 rows=3D3852=20 loops=3D1)
          = ;            =             &= nbsp;        =20 Hash Cond: ("outer".winkelid =3D=20 "inner".winkelid)
        &nbs= p;            = ;            =           =20 ->  Hash Left Join  (cost=3D918.33..1373.61 rows=3D3981 = width=3D249)=20 (actual time=3D31.997..64.938 rows=3D3852=20 loops=3D1)
          = ;            =             &= nbsp;           &n= bsp;  =20 Hash Cond: ("outer".genreid =3D=20 "inner".genreid)
         = ;            =             &= nbsp;           &n= bsp;   =20 ->  Hash Left Join  (cost=3D917.14..1312.71 rows=3D3981 = width=3D117)=20 (actual time=3D31.946..60.961 rows=3D3852=20 loops=3D1)
          = ;            =             &= nbsp;           &n= bsp;        =20 Hash Cond: ("outer".onderwerpid =3D=20 "inner".onderwerpid)
        &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;          =20 ->  Hash Left Join  (cost=3D904.72..1240.57 rows=3D3981 = width=3D117)=20 (actual time=3D31.104..56.264 rows=3D3852=20 loops=3D1)
          = ;            =             &= nbsp;           &n= bsp;           &nb= sp;  =20 Hash Cond: ("outer".onderwerpid =3D=20 "inner".onderwerpid)
        &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;    =20 ->  Merge Right Join  (cost=3D890.28..1166.42 rows=3D3981 = width=3D101)=20 (actual time=3D29.938..50.406 rows=3D3852=20 loops=3D1)
          = ;            =             &= nbsp;           &n= bsp;           &nb= sp;        =20 Merge Cond: ("outer".productid =3D=20 "inner".productid)
        &nb= sp;           &nbs= p;            = ;            =             &= nbsp;         =20 ->  Index Scan using koppel_product_onderwerp_pkey on=20 koppel_product_onderwerp kpo  (cost=3D0.00..216.34 rows=3D5983 = width=3D8) (actual=20 time=3D0.011..8.537 rows=3D5965=20 loops=3D1)
          = ;            =             &= nbsp;           &n= bsp;           &nb= sp;        =20 ->  Sort  (cost=3D890.28..900.23 rows=3D3981 width=3D97) = (actual=20 time=3D29.918..31.509 rows=3D3852=20 loops=3D1)
          = ;            =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;  =20 Sort Key:=20 p.productid
         &nbs= p;            = ;            =             &= nbsp;           &n= bsp;           &nb= sp;  =20 ->  Seq Scan on product p  (cost=3D0.00..652.24 rows=3D3981 = width=3D97)=20 (actual time=3D0.012..18.012 rows=3D3819=20 loops=3D1)
          = ;            =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;        =20 Filter: (afdelingid =3D=20 1)
           &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ; =20 ->  Hash  (cost=3D12.75..12.75 rows=3D675 width=3D20) = (actual=20 time=3D1.119..1.119 rows=3D675=20 loops=3D1)
          = ;            =             &= nbsp;           &n= bsp;           &nb= sp;        =20 ->  Seq Scan on geg_onderwerp gego  (cost=3D0.00..12.75 = rows=3D675=20 width=3D20) (actual time=3D0.010..0.598 rows=3D675=20 loops=3D1)
          = ;            =             &= nbsp;           &n= bsp;        =20 ->  Hash  (cost=3D10.74..10.74 rows=3D674 width=3D8) = (actual=20 time=3D0.822..0.822 rows=3D674=20 loops=3D1)
          = ;            =             &= nbsp;           &n= bsp;           &nb= sp;  =20 ->  Seq Scan on koppel_onderwerp_genre kog  = (cost=3D0.00..10.74=20 rows=3D674 width=3D8) (actual time=3D0.010..0.423 rows=3D674=20 loops=3D1)
          = ;            =             &= nbsp;           &n= bsp;  =20 ->  Hash  (cost=3D1.15..1.15 rows=3D15 width=3D140) (actual = time=3D0.033..0.033 rows=3D15=20 loops=3D1)
          = ;            =             &= nbsp;           &n= bsp;        =20 ->  Seq Scan on geg_genre defg  (cost=3D0.00..1.15 = rows=3D15 width=3D140)=20 (actual time=3D0.004..0.017 rows=3D15=20 loops=3D1)
          = ;            =             &= nbsp;        =20 ->  Hash  (cost=3D5.96..5.96 rows=3D1 width=3D584) (actual=20 time=3D0.682..0.682 rows=3D197=20 loops=3D1)
          = ;            =             &= nbsp;           &n= bsp;  =20 ->  Seq Scan on geg_winkel gegw  (cost=3D0.00..5.96 = rows=3D1 width=3D584)=20 (actual time=3D0.042..0.390 rows=3D197=20 loops=3D1)
          = ;            =             &= nbsp;           &n= bsp;        =20 Filter: ((lft >=3D $0) AND (lft <=3D=20 $1))
           = ;            =             &= nbsp; =20 ->  Index Scan using product_eigenschap_key on = product_eigenschap=20 pe  (cost=3D0.00..5.13 rows=3D1 width=3D4) (actual = time=3D0.006..0.007 rows=3D0=20 loops=3D3852)
         &n= bsp;           &nb= sp;           &nbs= p;         =20 Index Cond: ("outer".productid =3D=20 pe.productid)
         &n= bsp;           &nb= sp;           &nbs= p;         =20 Filter: (stocktypeid <=20 3)
           &= nbsp;           &n= bsp;       =20 ->  Hash  (cost=3D1.36..1.36 rows=3D36 width=3D13) (actual=20 time=3D0.081..0.081 rows=3D36=20 loops=3D1)
          = ;            =             &= nbsp;  =20 ->  Seq Scan on geg_leverancier dl  (cost=3D0.00..1.36 = rows=3D36=20 width=3D13) (actual time=3D0.010..0.042 rows=3D36 loops=3D1)
Total = runtime: 125.432=20 ms
 
This means that the Query is 200 times slower on the = webhost!
 
How can I resolve this?
------=_NextPart_000_0063_01C6B1AC.C56FAF40--