|
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?
|
