Обсуждение: Changing location of ORDER BY has large effect on performance, but not results...
Changing location of ORDER BY has large effect on performance, but not results...
От
Jason Turner
Дата:
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
Jason Turner <lefticus@gmail.com> writes:
> 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.
Well, of course not. It should be able to do it before, though, and I'm
a bit surprised that you didn't get the same plan from both cases seeing
that the planner knows the first one is cheaper. Can you provide a
complete self-contained test case? I'm not interested in trying to
reverse-engineer your table definitions ...
regards, tom lane