X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id E0354DB271 for ; Fri, 28 Oct 2005 18:30:56 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 55407-01 for ; Fri, 28 Oct 2005 21:30:53 +0000 (GMT) Received: from xproxy.gmail.com (xproxy.gmail.com [66.249.82.203]) by svr1.postgresql.org (Postfix) with ESMTP id 492C7DB25F for ; Fri, 28 Oct 2005 18:30:52 -0300 (ADT) Received: by xproxy.gmail.com with SMTP id i30so454825wxd for ; Fri, 28 Oct 2005 14:30:55 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition; b=l0+z5z+gNqRiU7CLl1FPqVBWqGNHbx5Em4F7I4NA9koWq9/wSNFKfDOEoOyGGFxUrvPedtlhzySbVAvhKyeFsE9wzcLGWarYtn/tcs4S0hblJybElorqEAilqMu2qcxkUrHNMelIvf3uWztSTY+Xp7RwTYyIsCeAfMCPsx7mikY= Received: by 10.64.149.12 with SMTP id w12mr437643qbd; Fri, 28 Oct 2005 14:30:55 -0700 (PDT) Received: by 10.64.208.6 with HTTP; Fri, 28 Oct 2005 14:30:55 -0700 (PDT) Message-ID: <58e14cf00510281430y172deecak32839c101608522d@mail.gmail.com> Date: Fri, 28 Oct 2005 15:30:55 -0600 From: Jason Turner To: pgsql-sql@postgresql.org Subject: Changing location of ORDER BY has large effect on performance, but not results... MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200511/50 X-Sequence-Number: 23301 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 =3D tp.performerid and tp.trackid =3D foo.trackid limit 1) AS performername, (SELECT coverartid FROM trackcoverart WHERE trackcoverart.trackid =3D foo.trackid LIMIT 1) as trackcoverart, (SELECT albumcoverart.coverartid FROM albumcoverart, track WHERE foo.trackid =3D trackid AND albumcoverart.albumid =3D foo.albumid LIMIT 1) as albumcoverart FROM (select * from track order by tracktitle) as foo, album, genre where foo.albumid =3D album.albumid and foo.genreid =3D genre.genreid offset 2000 limit 20; --- First Explain Analyze --- Limit (cost=3D20915.07..21123.71 rows=3D20 width=3D338) (actual time=3D184.997..186.417 rows=3D20 loops=3D1) -> Hash Join (cost=3D50.81..131860.75 rows=3D12635 width=3D338) (actual time=3D5.085..185.202 rows=3D2020 loops=3D1) Hash Cond: ("outer".albumid =3D "inner".albumid) -> Hash Join (cost=3D13.07..938.94 rows=3D12635 width=3D318) (actual time=3D1.317..34.143 rows=3D2020 loops=3D1) Hash Cond: ("outer".genreid =3D "inner".genreid) -> Subquery Scan foo (cost=3D0.00..736.34 rows=3D12635 width=3D288) (actual time=3D0.021..16.317 rows=3D2020 loops=3D1) -> Index Scan using track_tracktitle on track=20 (cost=3D0.00..609.99 rows=3D12635 width=3D332) (actual time=3D0.012..4.266 rows=3D2020 loops=3D1) -> Hash (cost=3D11.66..11.66 rows=3D566 width=3D34) (actual time=3D1.267..1.267 rows=3D0 loops=3D1) -> Seq Scan on genre (cost=3D0.00..11.66 rows=3D566 width=3D34) (actual time=3D0.004..0.737 rows=3D566 loops=3D1) -> Hash (cost=3D33.59..33.59 rows=3D1659 width=3D24) (actual time=3D3.646..3.646 rows=3D0 loops=3D1) -> Seq Scan on album (cost=3D0.00..33.59 rows=3D1659 width=3D24) (actual time=3D0.012..2.194 rows=3D1659 loops=3D1) SubPlan -> Limit (cost=3D0.00..7.53 rows=3D1 width=3D4) (actual time=3D0.021..0.021 rows=3D1 loops=3D2020) -> Nested Loop (cost=3D0.00..7.53 rows=3D1 width=3D4) (actual time=3D0.019..0.019 rows=3D1 loops=3D2020) -> Index Scan using albumcoverart_albumid on albumcoverart (cost=3D0.00..3.01 rows=3D1 width=3D4) (actual time=3D0.007..0.007 rows=3D1 loops=3D2020) Index Cond: (albumid =3D $1) -> Index Scan using track_pkey on track=20 (cost=3D0.00..4.51 rows=3D1 width=3D0) (actual time=3D0.007..0.007 rows=3D1 loops=3D2020) Index Cond: ($0 =3D trackid) -> Limit (cost=3D0.00..2.78 rows=3D1 width=3D4) (actual time=3D0.006..0.006 rows=3D0 loops=3D2020) -> Index Scan using trackcoverart_trackid on trackcoverart (cost=3D0.00..27.80 rows=3D10 width=3D4) (actual time=3D0.004..0.004 rows=3D0 loops=3D2020) Index Cond: (trackid =3D $0) -> Limit (cost=3D0.00..0.03 rows=3D1 width=3D17) (actual time=3D0.028..0.028 rows=3D1 loops=3D2020) -> Nested Loop (cost=3D0.00..64.89 rows=3D2142 width=3D17= ) (actual time=3D0.025..0.025 rows=3D1 loops=3D2020) -> Nested Loop (cost=3D0.00..6.05 rows=3D2 width=3D0) (actual time=3D0.019..0.019 rows=3D1 loops=3D2020) -> Index Scan using trackperformers_trackid on trackperformers tp (cost=3D0.00..3.01 rows=3D1 width=3D4) (actual time=3D0.007..0.007 rows=3D1 loops=3D2020) Index Cond: (trackid =3D $0) -> Index Scan using performer_pkey on performer p (cost=3D0.00..3.02 rows=3D1 width=3D4) (actual time=3D0.007..0.007 rows=3D1 loops=3D2020) Index Cond: (p.performerid =3D "outer".performerid) -> Seq Scan on performer (cost=3D0.00..18.71 rows=3D1071 width=3D17) (actual time=3D0.002..0.002 rows=3D1 loops=3D2020) 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 =3D tp.performerid and tp.trackid =3D foo.trackid limit 1) AS performername, (SELECT coverartid FROM trackcoverart WHERE trackcoverart.trackid =3D foo.trackid LIMIT 1) as trackcoverart, (SELECT albumcoverart.coverartid FROM albumcoverart, track WHERE foo.trackid =3D trackid AND albumcoverart.albumid =3D foo.albumid LIMIT 1) as albumcoverart FROM track as foo, album, genre where foo.albumid =3D album.albumid and foo.genreid =3D genre.genreid order by foo.tracktitle offset 2000 limit 20; --- Second Explain Analyze --- Limit (cost=3D134126.42..134126.47 rows=3D20 width=3D382) (actual time=3D1068.650..1068.698 rows=3D20 loops=3D1) -> Sort (cost=3D134121.42..134153.01 rows=3D12635 width=3D382) (actual time=3D1064.642..1067.106 rows=3D2020 loops=3D1) Sort Key: foo.tracktitle -> Hash Join (cost=3D50.81..131602.77 rows=3D12635 width=3D382) (actual time=3D5.242..956.526 rows=3D12635 loops=3D1) Hash Cond: ("outer".albumid =3D "inner".albumid) -> Hash Join (cost=3D13.07..680.95 rows=3D12635 width=3D362= ) (actual time=3D1.332..119.681 rows=3D12635 loops=3D1) Hash Cond: ("outer".genreid =3D "inner".genreid) -> Seq Scan on track foo (cost=3D0.00..478.35 rows=3D12635 width=3D332) (actual time=3D0.003..19.214 rows=3D12635 loops= =3D1) -> Hash (cost=3D11.66..11.66 rows=3D566 width=3D34) (actual time=3D1.297..1.297 rows=3D0 loops=3D1) -> Seq Scan on genre (cost=3D0.00..11.66 rows=3D566 width=3D34) (actual time=3D0.004..0.760 rows=3D566 loops=3D1) -> Hash (cost=3D33.59..33.59 rows=3D1659 width=3D24) (actua= l time=3D3.801..3.801 rows=3D0 loops=3D1) -> Seq Scan on album (cost=3D0.00..33.59 rows=3D1659 width=3D24) (actual time=3D0.012..2.246 rows=3D1659 loops=3D1) SubPlan -> Limit (cost=3D0.00..7.53 rows=3D1 width=3D4) (actual time=3D0.018..0.019 rows=3D1 loops=3D12635) -> Nested Loop (cost=3D0.00..7.53 rows=3D1 width=3D4) (actual time=3D0.016..0.016 rows=3D1 loops=3D12635) -> Index Scan using albumcoverart_albumid on albumcoverart (cost=3D0.00..3.01 rows=3D1 width=3D4) (actual time=3D0.006..0.006 rows=3D1 loops=3D12635) Index Cond: (albumid =3D $1) -> Index Scan using track_pkey on track=20 (cost=3D0.00..4.51 rows=3D1 width=3D0) (actual time=3D0.005..0.005 rows=3D1 loops=3D12635) Index Cond: ($0 =3D trackid) -> Limit (cost=3D0.00..2.78 rows=3D1 width=3D4) (actual time=3D0.005..0.005 rows=3D0 loops=3D12635) -> Index Scan using trackcoverart_trackid on trackcoverart (cost=3D0.00..27.80 rows=3D10 width=3D4) (actual time=3D0.003..0.003 rows=3D0 loops=3D12635) Index Cond: (trackid =3D $0) -> Limit (cost=3D0.00..0.03 rows=3D1 width=3D17) (actual time=3D0.024..0.025 rows=3D1 loops=3D12635) -> Nested Loop (cost=3D0.00..64.89 rows=3D2142 width=3D17) (actual time=3D0.022..0.022 rows=3D1 loops=3D12635) -> Nested Loop (cost=3D0.00..6.05 rows=3D2 width=3D0) (actual time=3D0.016..0.016 rows=3D1 loops=3D12635) -> Index Scan using trackperformers_trackid on trackperformers tp (cost=3D0.00..3.01 rows=3D1 width=3D4) (actual time=3D0.006..0.006 rows=3D1 loops=3D12635) Index Cond: (trackid =3D $0) -> Index Scan using performer_pkey on performer p (cost=3D0.00..3.02 rows=3D1 width=3D4) (actual time=3D0.005..0.005 rows=3D1 loops=3D12635) Index Cond: (p.performerid =3D "outer".performerid) -> Seq Scan on performer=20 (cost=3D0.00..18.71 rows=3D1071 width=3D17) (actual time=3D0.002..0.002 row= s=3D1 loops=3D12635) Total runtime: 1072.935 ms -- http://emptycrate.com Games, Programming, Travel & other stuff