X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 33FA4D1C9D8 for ; Sun, 3 Aug 2003 10:19:51 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 22474-07 for ; Sun, 3 Aug 2003 07:18:40 -0300 (ADT) Received: from lyric.local (085.a.011.mel.iprimus.net.au [210.50.216.85]) by svr1.postgresql.org (Postfix) with ESMTP id 99EDBD1C9CF for ; Sun, 3 Aug 2003 07:19:39 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by lyric.local (Postfix) with ESMTP id AEDB53D2CE; Sun, 3 Aug 2003 20:21:45 +1000 (EST) From: Toby Tremayne Reply-To: toby@lyricist.com.au Organization: Lyricist Software To: Rod Taylor Subject: Re: join optimization problem Date: Sun, 3 Aug 2003 20:21:43 +1000 User-Agent: KMail/1.5.1 Cc: pgsql-sql@postgresql.org References: <200307281412.36981.toby@lyricist.com.au> <1059698003.33557.5.camel@jester> In-Reply-To: <1059698003.33557.5.camel@jester> MIME-Version: 1.0 Content-Type: Text/Plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Content-Description: clearsigned data Content-Disposition: inline Message-Id: <200308032021.45212.toby@lyricist.com.au> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/25 X-Sequence-Number: 14641 -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi Rod, thanks for that - I tried it out, fixed the minor typos and it ran at almo= st=20 the same speed! Any other ideas? cheers, Toby On Friday 01 August 2003 10:33, Rod Taylor wrote: > How about this? It should spit out the same number, but be quite a bit > quicker at it. Untested of course... > > > select t.thread_id, > t.forum_id, > t.thread_title, > t.thread_owner, > t.thread_owner_id, > t.date_created, > t.thread_hits, > t.poem_reference, > t.bArchived, > count, maxdate > from fbof_thread t > LEFT OUTER JOIN > (select thread_id > , count(msg_id) as count > , max(date_created) as maxdate > from msg > group by thread_id > ) as tab ON m.thread_id =3D t.thread_id > where t.forum_id =3D 1 > and t.bArchived =3D 0 > and t.bSticky =3D 0 > order by t.date_created desc > > > can anyone point out to me where I'm going wrong here? I can't seem to > > make it faster for the life of me.... I've tried adding indices on all > > the main fields etc but nada. I'm not subscribed the list currently so > > please reply to my address as well as the list. - --=20 - -------------------------------- Life is poetry -=20 write it in your own words - -------------------------------- Toby Tremayne Code Poet and Zen Master of the Heavy Sleep Senior Technical Consultant Lyricist Software www.lyricist.com.au +61 416 048 090 ICQ: 13107913 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.2-rc1-SuSE (GNU/Linux) iD8DBQE/LOI36KYxVcB16DkRAg7RAKCIALKF4TExS9Q38WiM8jTzRxFctgCgttI3 jbfhQ4GrW2BKPU5uhRoK4rc=3D =3DF+c9 -----END PGP SIGNATURE-----