From: Rod Taylor <rbt@rbt.ca>
To: toby@lyricist.com.au
Cc: pgsql-sql@postgresql.org
Subject: Re: join optimization problem
Date: 31 Jul 2003 20:33:23 -0400
Message-ID: <1059698003.33557.5.camel@jester> (raw)
In-Reply-To: <200307281412.36981.toby@lyricist.com.au>
References: <200307281412.36981.toby@lyricist.com.au>
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.
Attachments:
signature.asc (application/pgp-signature, 186B) download
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: rbt@rbt.ca, toby@lyricist.com.au
Subject: Re: join optimization problem
In-Reply-To: <1059698003.33557.5.camel@jester>
* 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