X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 43489D1C7EB for ; Sun, 3 Aug 2003 10:19:10 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 22474-06 for ; Sun, 3 Aug 2003 07:17:59 -0300 (ADT) Received: from lyric.local (085.a.011.mel.iprimus.net.au [210.50.216.85]) by svr1.postgresql.org (Postfix) with ESMTP id DF1E0D1C9CF for ; Sun, 3 Aug 2003 07:18:57 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by lyric.local (Postfix) with ESMTP id 3F7293D2CD; Sun, 3 Aug 2003 20:20:53 +1000 (EST) From: Toby Tremayne Reply-To: toby@lyricist.com.au Organization: Lyricist Software To: Tom Lane Subject: Re: join optimization problem Date: Sun, 3 Aug 2003 20:20:50 +1000 User-Agent: KMail/1.5.1 Cc: pgsql-sql@postgresql.org References: <200307281412.36981.toby@lyricist.com.au> <2180.1059698182@sss.pgh.pa.us> In-Reply-To: <2180.1059698182@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: Text/Plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Content-Description: clearsigned data Content-Disposition: inline Message-Id: <200308032020.52741.toby@lyricist.com.au> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/24 X-Sequence-Number: 14640 -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi Tom, yes I re-run vacuum analyze every time I muck about with the tables/ index= es. The output of explain analyze is this: Sort (cost=3D1660930.18..1660946.63 rows=3D6581 width=3D568) (actual=20 time=3D545668.58..545675.05 rows=3D1537 loops=3D1) Sort Key: max(m.date_created) -> Aggregate (cost=3D0.00..1659452.99 rows=3D6581 width=3D568) (actual= =20 time=3D614.83..544598.17 rows=3D1537 loops=3D1) -> Group (cost=3D0.00..1659123.95 rows=3D65807 width=3D568) (act= ual=20 time=3D539.87..543988.21 rows=3D1537 loops=3D1) -> Nested Loop (cost=3D0.00..1657643.30 rows=3D65807 width= =3D568)=20 (actual time=3D539.82..543404.25 rows=3D1537 loops=3D1) Join Filter: ("inner".thread_id =3D=20 ("outer".thread_id)::double precision) -> Index Scan using fbof_group_idx on fbof_thread t= =20=20 (cost=3D0.00..642.03 rows=3D1537 width=3D548) (actual time=3D29.27..1043.40= rows=3D1537=20 loops=3D1) 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) (actual time=3D0.08.. 294.28 rows=3D8563 loops=3D1537) Total runtime: 545763.83 msec (10 rows) any input at all would be great. cheers, Toby On Friday 01 August 2003 10:36, Tom Lane wrote: > Toby Tremayne writes: > > the explain data I'm currently getting is this: > > "explain analyze" would be much more useful. > > Also, I assume you've vacuum analyzed these tables recently? > > regards, tom lane - --=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/LOIC6KYxVcB16DkRAm/GAJ99ZmFU1iqNyFyQemwfCxcihC1aTwCfWMeo hxTkh1K9qOwp9XkLsmE4XFQ=3D =3DHXUi -----END PGP SIGNATURE-----