Subject: Re: join optimization problem
From: Rod Taylor <rbt@rbt.ca>
To: toby@lyricist.com.au
Cc: pgsql-sql@postgresql.org
In-Reply-To: <200307281412.36981.toby@lyricist.com.au>
References: <200307281412.36981.toby@lyricist.com.au>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="=-puFSQbm6a89POIlgp9Hi"
Message-Id: <1059698003.33557.5.camel@jester>
Mime-Version: 1.0
Date: 31 Jul 2003 20:33:23 -0400

--=-puFSQbm6a89POIlgp9Hi
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

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=20
         (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
=20=20=20=20=20=20=20=20
> can anyone point out to me where I'm going wrong here?  I can't seem to m=
ake=20
> it faster for the life of me....  I've tried adding indices on all the ma=
in=20
> fields etc but nada.  I'm not subscribed the list currently so please rep=
ly=20
> to my address as well as the list.


--=-puFSQbm6a89POIlgp9Hi
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (FreeBSD)

iD8DBQA/KbVS6DETLow6vwwRAgX0AJ9B5nQRe3JcAfPEF1id5lpjhdqr+wCdHMnV
Brm74H+qTGOjETBRaiJt/yo=
=5UbH
-----END PGP SIGNATURE-----

--=-puFSQbm6a89POIlgp9Hi--