Skip site navigation (1) Skip section navigation (2)

Query 200x slower on server [PART 2]

From: "NbForYou" <nbforyou(at)hotmail(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Query 200x slower on server [PART 2]
Date: 2006-07-27 16:45:03
Message-ID: BAY123-DAV70EDAD026EAE16A094D79DB580@phx.gbl (view raw or flat)
Lists: pgsql-performance
See Query 200x slower on server [PART 1] before reading any further

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)
                ->  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 = "inner".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 = "inner".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?


pgsql-performance by date

Next:From: Mark LewisDate: 2006-07-27 17:19:15
Subject: Savepoint performance
Previous:From: Hristo MarkovDate: 2006-07-27 16:29:29
Subject: How to increase performance?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group