Обсуждение: Query 200x slower on server [PART 2]

Поиск
Список
Период
Сортировка

Query 200x slower on server [PART 2]

От
"NbForYou"
Дата:
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
 
This means that the Query is 200 times slower on the webhost!
 
How can I resolve this?

Re: Query 200x slower on server [PART 2]

От
Richard Huxton
Дата:
NbForYou wrote:
> See Query 200x slower on server [PART 1] before reading any further

Cant' find it. Sorry.

>
> QUERY PLAN ON MY HOME SERVER
[snip]
> Total runtime: 125.432 ms
>
> This means that the Query is 200 times slower on the webhost!
>
> How can I resolve this?

First - what is different between the two plans and why? PostgreSQL will
be choosing a different plan because:
1. It's estimating different numbers of rows for one or more steps
2. It's estimating a different cost for one or more steps
3. It's triggering the genetic optimiser which means you're not
necessarily going to get the same plan each time.
4. You've got different versions of PG on the different machines.

--
   Richard Huxton
   Archonet Ltd