Query 200x slower on server [PART 2] - Mailing list pgsql-performance
| From | NbForYou |
|---|---|
| Subject | Query 200x slower on server [PART 2] |
| Date | |
| Msg-id | BAY123-DAV70EDAD026EAE16A094D79DB580@phx.gbl Whole thread Raw |
| Responses |
Re: Query 200x slower on server [PART 2]
(Richard Huxton <dev@archonet.com>)
|
| List | pgsql-performance |
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 = "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
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 = "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: