From: Toby Tremayne <toby@lyricist.com.au>
To: Rod Taylor <rbt@rbt.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: join optimization problem
Date: Sun, 3 Aug 2003 20:21:43 +1000
Message-ID: <200308032021.45212.toby@lyricist.com.au> (raw)
In-Reply-To: <1059698003.33557.5.camel@jester>
References: <200307281412.36981.toby@lyricist.com.au>
<1059698003.33557.5.camel@jester>
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi Rod,
thanks for that - I tried it out, fixed the minor typos and it ran at almost
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 = t.thread_id
> where t.forum_id = 1
> and t.bArchived = 0
> and t.bSticky = 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.
- --
- --------------------------------
Life is poetry -
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=
=F+c9
-----END PGP SIGNATURE-----
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: toby@lyricist.com.au, rbt@rbt.ca
Subject: Re: join optimization problem
In-Reply-To: <200308032021.45212.toby@lyricist.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox