slow query if add order by

[Paolo Tavalazzi]

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,e nabledcodes.sal a,spettacoli.co de
from
tkstore,enabled codes,spettacol i
where
tkstore.id = enabledcodes.tk store_id and
tkstore.gruppo in ('amit') and
enabledcodes.sa la = spettacoli.teat ro and
spettacoli.syst em = 0 ;
The explain is :
Hash Join (cost=173.06..3 810.20 rows=115782 width=42) (actual
time=16.248..12 65.331 rows=380736 loops=1)
Hash Cond: ("outer".tea tro = "inner".sal a)
-> Seq Scan on spettacoli (cost=0.00..134 2.35 rows=23935 width=24)
(actual time=0.012..35. 999 rows=26846 loops=1)
Filter: (system = 0)
-> Hash (cost=168.28..1 68.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".tkstor e_id = "inner".id)
-> Seq Scan on enabledcodes (cost=0.00..113 .45 rows=6245
width=16) (actual time=0.007..3.4 39 rows=6245 loops=1)
-> Hash (cost=4.39..4.3 9 rows=34 width=23) (actual
time=0.213..0.2 13 rows=0 loops=1)
-> Seq Scan on tkstore (cost=0.00..4.3 9 rows=34
width=23) (actual time=0.024..0.1 87 rows=33 loops=1)
Filter: ((gruppo)::text = 'amit'::text)
Total runtime: 1330.843 ms

2) explain analyze
select
tkstore.gruppo, tkstore.cassa,e nabledcodes.sal a,spettacoli.co de
from
tkstore,enabled codes,spettacol i
where
tkstore.id = enabledcodes.tk store_id and
tkstore.gruppo in ('amit') and
enabledcodes.sa la = spettacoli.teat ro and
spettacoli.syst em = 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..3 810.20 rows=115782 width=42) (actual
time=16.227..13 92.206 rows=380736 loops=1)
Hash Cond: ("outer".tea tro = "inner".sal a)
-> Seq Scan on spettacoli (cost=0.00..134 2.35 rows=23935 width=24)
(actual time=0.011..47. 329 rows=26846 loops=1)
Filter: (system = 0)
-> Hash (cost=168.28..1 68.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".tkstor e_id = "inner".id)
-> Seq Scan on enabledcodes (cost=0.00..113 .45
rows=6245 width=16) (actual time=0.008..3.4 69 rows=6245 loops=1)
-> Hash (cost=4.39..4.3 9 rows=34 width=23) (actual
time=0.214..0.2 14 rows=0 loops=1)
-> Seq Scan on tkstore (cost=0.00..4.3 9 rows=34
width=23) (actual time=0.023..0.1 81 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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Gaetano Mendola]

Paolo Tavalazzi wrote:

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

How much sort_mem are you using ?

Regards
Gaetano Mendola