Changing location of ORDER BY has large effect on performance, but not results... - Mailing list pgsql-sql
From | Jason Turner |
---|---|
Subject | Changing location of ORDER BY has large effect on performance, but not results... |
Date | |
Msg-id | 58e14cf00510281430y172deecak32839c101608522d@mail.gmail.com Whole thread Raw |
Responses |
Re: Changing location of ORDER BY has large effect on performance, but not results...
|
List | pgsql-sql |
I have two queries that return the same results, but one is 6 times slower than the other one, can anyone enlighten me as to why? My initial guess is that it is not able to utilize the index on foo.tracktitle to sort the result set after foo has been joined with other tables. This seems kind of broken to me. I am running 8.0.4 on Gentoo Linux. Thanks, Jason --- First Query --- select foo.*, genre.genrename, album.albumtitle, (select performer.performername from performer as p, trackperformers as tp where p.performerid = tp.performerid and tp.trackid = foo.trackid limit 1) AS performername, (SELECT coverartid FROM trackcoverart WHERE trackcoverart.trackid = foo.trackid LIMIT 1) as trackcoverart, (SELECT albumcoverart.coverartid FROM albumcoverart, track WHERE foo.trackid = trackid AND albumcoverart.albumid= foo.albumid LIMIT 1) as albumcoverart FROM (select * from track order by tracktitle) as foo, album, genre where foo.albumid = album.albumid and foo.genreid = genre.genreid offset 2000 limit 20; --- First Explain Analyze --- Limit (cost=20915.07..21123.71 rows=20 width=338) (actual time=184.997..186.417 rows=20 loops=1) -> Hash Join (cost=50.81..131860.75 rows=12635 width=338) (actual time=5.085..185.202 rows=2020 loops=1) Hash Cond: ("outer".albumid = "inner".albumid) -> Hash Join (cost=13.07..938.94rows=12635 width=318) (actual time=1.317..34.143 rows=2020 loops=1) Hash Cond: ("outer".genreid = "inner".genreid) -> Subquery Scan foo (cost=0.00..736.34 rows=12635 width=288) (actual time=0.021..16.317 rows=2020 loops=1) -> Index Scan using track_tracktitle on track (cost=0.00..609.99 rows=12635 width=332) (actual time=0.012..4.266 rows=2020 loops=1) -> Hash (cost=11.66..11.66 rows=566 width=34) (actual time=1.267..1.267 rows=0 loops=1) -> Seq Scan on genre (cost=0.00..11.66 rows=566 width=34) (actual time=0.004..0.737 rows=566 loops=1) -> Hash (cost=33.59..33.59 rows=1659 width=24) (actual time=3.646..3.646 rows=0 loops=1) -> Seq Scan on album (cost=0.00..33.59 rows=1659 width=24) (actual time=0.012..2.194 rows=1659 loops=1) SubPlan -> Limit (cost=0.00..7.53 rows=1 width=4)(actual time=0.021..0.021 rows=1 loops=2020) -> Nested Loop (cost=0.00..7.53 rows=1 width=4) (actual time=0.019..0.019 rows=1 loops=2020) -> Index Scan using albumcoverart_albumid on albumcoverart (cost=0.00..3.01 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=2020) Index Cond: (albumid = $1) -> Index Scanusing track_pkey on track (cost=0.00..4.51 rows=1 width=0) (actual time=0.007..0.007 rows=1 loops=2020) Index Cond: ($0 = trackid) -> Limit (cost=0.00..2.78 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=2020) -> Index Scan using trackcoverart_trackid on trackcoverart (cost=0.00..27.80 rows=10 width=4) (actual time=0.004..0.004 rows=0 loops=2020) Index Cond: (trackid = $0) -> Limit (cost=0.00..0.03 rows=1width=17) (actual time=0.028..0.028 rows=1 loops=2020) -> Nested Loop (cost=0.00..64.89 rows=2142 width=17) (actual time=0.025..0.025 rows=1 loops=2020) -> Nested Loop (cost=0.00..6.05 rows=2 width=0) (actual time=0.019..0.019 rows=1 loops=2020) -> Index Scan using trackperformers_trackid on trackperformers tp (cost=0.00..3.01 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=2020) Index Cond: (trackid = $0) -> Index Scan using performer_pkey on performer p (cost=0.00..3.02 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=2020) Index Cond: (p.performerid = "outer".performerid) -> Seq Scan on performer (cost=0.00..18.71 rows=1071 width=17) (actual time=0.002..0.002 rows=1 loops=2020) Total runtime: 186.706 ms --- Second Query --- select foo.*, genre.genrename, album.albumtitle, (select performer.performername from performer as p, trackperformers as tp where p.performerid = tp.performerid and tp.trackid = foo.trackid limit 1) AS performername, (SELECT coverartid FROM trackcoverart WHERE trackcoverart.trackid = foo.trackid LIMIT 1) as trackcoverart, (SELECT albumcoverart.coverartid FROM albumcoverart, track WHERE foo.trackid = trackid AND albumcoverart.albumid= foo.albumid LIMIT 1) as albumcoverart FROM track as foo, album, genre where foo.albumid = album.albumid and foo.genreid = genre.genreid order by foo.tracktitle offset 2000 limit 20; --- Second Explain Analyze --- Limit (cost=134126.42..134126.47 rows=20 width=382) (actual time=1068.650..1068.698 rows=20 loops=1) -> Sort (cost=134121.42..134153.01 rows=12635 width=382) (actual time=1064.642..1067.106 rows=2020 loops=1) Sort Key: foo.tracktitle -> Hash Join (cost=50.81..131602.77 rows=12635width=382) (actual time=5.242..956.526 rows=12635 loops=1) Hash Cond: ("outer".albumid = "inner".albumid) -> Hash Join (cost=13.07..680.95 rows=12635 width=362) (actual time=1.332..119.681 rows=12635 loops=1) Hash Cond: ("outer".genreid = "inner".genreid) -> Seq Scan on track foo (cost=0.00..478.35 rows=12635 width=332) (actual time=0.003..19.214 rows=12635 loops=1) -> Hash (cost=11.66..11.66 rows=566width=34) (actual time=1.297..1.297 rows=0 loops=1) -> Seq Scan on genre (cost=0.00..11.66 rows=566 width=34) (actual time=0.004..0.760 rows=566 loops=1) -> Hash (cost=33.59..33.59 rows=1659 width=24)(actual time=3.801..3.801 rows=0 loops=1) -> Seq Scan on album (cost=0.00..33.59 rows=1659 width=24) (actual time=0.012..2.246 rows=1659 loops=1) SubPlan -> Limit (cost=0.00..7.53 rows=1width=4) (actual time=0.018..0.019 rows=1 loops=12635) -> Nested Loop (cost=0.00..7.53 rows=1 width=4) (actual time=0.016..0.016 rows=1 loops=12635) -> Index Scan using albumcoverart_albumid on albumcoverart (cost=0.00..3.01 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=12635) Index Cond: (albumid = $1) -> Index Scan using track_pkey on track (cost=0.00..4.51 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=12635) Index Cond: ($0 = trackid) -> Limit (cost=0.00..2.78 rows=1width=4) (actual time=0.005..0.005 rows=0 loops=12635) -> Index Scan using trackcoverart_trackid on trackcoverart (cost=0.00..27.80 rows=10 width=4) (actual time=0.003..0.003 rows=0 loops=12635) Index Cond: (trackid = $0) -> Limit (cost=0.00..0.03rows=1 width=17) (actual time=0.024..0.025 rows=1 loops=12635) -> Nested Loop (cost=0.00..64.89 rows=2142 width=17) (actual time=0.022..0.022 rows=1 loops=12635) -> Nested Loop (cost=0.00..6.05 rows=2 width=0) (actual time=0.016..0.016 rows=1 loops=12635) -> Index Scan using trackperformers_trackid on trackperformers tp (cost=0.00..3.01 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=12635) Index Cond: (trackid = $0) -> Index Scan using performer_pkey on performer p (cost=0.00..3.02 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=12635) Index Cond: (p.performerid = "outer".performerid) -> Seq Scan on performer (cost=0.00..18.71 rows=1071 width=17) (actual time=0.002..0.002 rows=1 loops=12635) Total runtime: 1072.935 ms -- http://emptycrate.com Games, Programming, Travel & other stuff