From: Toby Tremayne <toby@lyricist.com.au>
Reply-To: toby@lyricist.com.au
Organization: Lyricist Software
To: pgsql-sql@postgresql.org
Subject: join optimization problem
Date: Mon, 28 Jul 2003 14:12:35 +1000
User-Agent: KMail/1.5.1
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Content-Description: clearsigned data
Content-Disposition: inline
Message-Id: <200307281412.36981.toby@lyricist.com.au>

-----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=20
not having much success.  It's from a forum based application, and the quer=
y=20
in question is operating on two tables - fbof_msg (8563 rows) and=20
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 =3D t.thread=
_id
	where	t.forum_id =3D 1
	and		t.bArchived =3D 0
	and		t.bSticky =3D 0
	group by 	t.thread_id,=20
			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=3D1660930.18..1660946.63 rows=3D6581 width=3D568)
   Sort Key: max(m.date_created)
   ->  Aggregate  (cost=3D0.00..1659452.99 rows=3D6581 width=3D568)
         ->  Group  (cost=3D0.00..1659123.95 rows=3D65807 width=3D568)
               ->  Nested Loop  (cost=3D0.00..1657643.30 rows=3D65807 width=
=3D568)
                     Join Filter: (("outer".thread_id)::double precision =
=3D=20
"inner".thread_id)
                     ->  Index Scan using fbof_group_idx on fbof_thread t=
=20=20
(cost=3D0.00..642.03 rows=3D1537 width=3D548)
                           Filter: ((forum_id =3D 1::double precision) AND=
=20
(barchived =3D 0) AND (bsticky =3D 0))
                     ->  Seq Scan on fbof_msg m  (cost=3D0.00..949.63 rows=
=3D8563=20
width=3D20)
(9 rows)


The index it's using is the only one I've been able to get it to use, and=
=20
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 mak=
e=20
it faster for the life of me....  I've tried adding indices on all the main=
=20
fields etc but nada.  I'm not subscribed the list currently so please reply=
=20
to my address as well as the list.

cheers,
Toby
- --=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/JKKz6KYxVcB16DkRAjl+AKCCrZswP4TL9aAzZUs7CkY9ajjoYwCfZO/v
SGR8GSi++ZZ+DrNXicabzvo=3D
=3Dkx4x
-----END PGP SIGNATURE-----