slow query if add order by - Mailing list pgsql-general
| From | Paolo Tavalazzi |
|---|---|
| Subject | slow query if add order by |
| Date | |
| Msg-id | 000801c439b3$1a43b2b0$0f0b640a@pppji7vdtyklcr Whole thread Raw |
| List | 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 ;
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
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
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
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 by date: