slow query if add order by

Поиск
Список
Период
Сортировка
От Paolo Tavalazzi
Тема slow query if add order by
Дата
Msg-id 200405141537.58772.ptavalazzi@charta.it
обсуждение исходный текст
Ответы Re: slow query if add order by  (Gaetano Mendola <mendola@bigfoot.com>)
Список pgsql-general

I have 2 query that differ only for order by clause.
The time of execution  of the two query is a lot of different.


1) explain analyze
       select
          tkstore.gruppo,tkstore.cassa,enabledcodes.sala,spettacoli.code
       from
          tkstore,enabledcodes,spettacoli
      where
          tkstore.id = enabledcodes.tkstore_id and
          tkstore.gruppo in ('amit') and
          enabledcodes.sala = spettacoli.teatro and
          spettacoli.system = 0 ;


 The explain is :


Hash Join  (cost=173.06..3810.20 rows=115782 width=42) (actual
time=16.248..1265.331 rows=380736 loops=1)
   Hash Cond: ("outer".teatro = "inner".sala)
   ->  Seq Scan on spettacoli  (cost=0.00..1342.35 rows=23935 width=24)
(actual time=0.012..35.999 rows=26846 loops=1)
         Filter: (system = 0)
   ->  Hash  (cost=168.28..168.28 rows=1913 width=31) (actual
time=15.995..15.995 rows=0 loops=1)
         ->  Hash Join  (cost=4.47..168.28 rows=1913 width=31) (actual
time=1.021..12.693 rows=5076 loops=1)
               Hash Cond: ("outer".tkstore_id = "inner".id)
               ->  Seq Scan on enabledcodes  (cost=0.00..113.45 rows=6245
width=16) (actual time=0.007..3.439 rows=6245 loops=1)
               ->  Hash  (cost=4.39..4.39 rows=34 width=23) (actual
time=0.213..0.213 rows=0 loops=1)
                     ->  Seq Scan on tkstore  (cost=0.00..4.39 rows=34
width=23) (actual time=0.024..0.187 rows=33 loops=1)
                           Filter: ((gruppo)::text = 'amit'::text)
 Total runtime: 1330.843 ms





2) explain analyze
       select
          tkstore.gruppo,tkstore.cassa,enabledcodes.sala,spettacoli.code
       from
          tkstore,enabledcodes,spettacoli
      where
          tkstore.id = enabledcodes.tkstore_id and
          tkstore.gruppo in ('amit') and
          enabledcodes.sala = spettacoli.teatro and
          spettacoli.system = 0
       order by 2;


     The explain is :


   Sort  (cost=13548.08..13837.53 rows=115782 width=42) (actual
time=10631.389..10774.964 rows=380736 loops=1)
   Sort Key: tkstore.cassa
   ->  Hash Join  (cost=173.06..3810.20 rows=115782 width=42) (actual
time=16.227..1392.206 rows=380736 loops=1)
         Hash Cond: ("outer".teatro = "inner".sala)
         ->  Seq Scan on spettacoli  (cost=0.00..1342.35 rows=23935 width=24)
(actual time=0.011..47.329 rows=26846 loops=1)
               Filter: (system = 0)
         ->  Hash  (cost=168.28..168.28 rows=1913 width=31) (actual
time=16.018..16.018 rows=0 loops=1)
               ->  Hash Join  (cost=4.47..168.28 rows=1913 width=31) (actual
time=1.023..12.680 rows=5076 loops=1)
                     Hash Cond: ("outer".tkstore_id = "inner".id)
                     ->  Seq Scan on enabledcodes  (cost=0.00..113.45
rows=6245 width=16) (actual time=0.008..3.469 rows=6245 loops=1)
                     ->  Hash  (cost=4.39..4.39 rows=34 width=23) (actual
time=0.214..0.214 rows=0 loops=1)
                           ->  Seq Scan on tkstore  (cost=0.00..4.39 rows=34
width=23) (actual time=0.023..0.181 rows=33 loops=1)
                                 Filter: ((gruppo)::text = 'amit'::text)
 Total runtime: 10858.720 ms




The db is  ANALYZED;
 Which is the reason of this difference??
What can I do ??
Thank



В списке pgsql-general по дате отправления:

Предыдущее
От: "Fred Fung"
Дата:
Сообщение: Re: dbmirror
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Scary behavior after power failure