From: Toby Tremayne <toby@lyricist.com.au>
To: pgsql-sql@postgresql.org
Subject: join optimization problem
Date: Mon, 28 Jul 2003 14:12:35 +1000
Message-ID: <200307281412.36981.toby@lyricist.com.au> (raw)
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi all,
I'm trying to optimize what I had thought was a simple enough query but I'm
not having much success. It's from a forum based application, and the query
in question is operating on two tables - fbof_msg (8563 rows) and
fbof_thread(1537 rows) and it looks like this:
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(m.msg_id) as msgcount,
max(m.date_created) as lastpost
from fbof_thread t LEFT OUTER JOIN fbof_msg m ON m.thread_id = t.thread_id
where t.forum_id = 1
and t.bArchived = 0
and t.bSticky = 0
group by t.thread_id,
t.thread_title,
t.thread_owner,
t.thread_owner_id,
t.date_created,
t.thread_hits,
t.forum_id,
t.poem_reference,
t.bArchived,
t.bSticky
order by t.date_created desc
the explain data I'm currently getting is this:
Sort (cost=1660930.18..1660946.63 rows=6581 width=568)
Sort Key: max(m.date_created)
-> Aggregate (cost=0.00..1659452.99 rows=6581 width=568)
-> Group (cost=0.00..1659123.95 rows=65807 width=568)
-> Nested Loop (cost=0.00..1657643.30 rows=65807 width=568)
Join Filter: (("outer".thread_id)::double precision =
"inner".thread_id)
-> Index Scan using fbof_group_idx on fbof_thread t
(cost=0.00..642.03 rows=1537 width=548)
Filter: ((forum_id = 1::double precision) AND
(barchived = 0) AND (bsticky = 0))
-> Seq Scan on fbof_msg m (cost=0.00..949.63 rows=8563
width=20)
(9 rows)
The index it's using is the only one I've been able to get it to use, and
looks like this: (\d fbof_group_idx)
Index "public.fbof_group_idx"
Column | Type
- -----------------+------------------
thread_id | integer
thread_title | character(255)
thread_owner | character(255)
thread_owner_id | integer
date_created | date
thread_hits | integer
forum_id | double precision
poem_reference | integer
barchived | smallint
btree, for table "public.fbof_thread"
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.
cheers,
Toby
- --
- --------------------------------
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/JKKz6KYxVcB16DkRAjl+AKCCrZswP4TL9aAzZUs7CkY9ajjoYwCfZO/v
SGR8GSi++ZZ+DrNXicabzvo=
=kx4x
-----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
Subject: Re: join optimization problem
In-Reply-To: <200307281412.36981.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