From: Toby Tremayne <toby@lyricist.com.au>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: join optimization problem
Date: Sun, 3 Aug 2003 20:20:50 +1000
Message-ID: <200308032020.52741.toby@lyricist.com.au> (raw)
In-Reply-To: <2180.1059698182@sss.pgh.pa.us>
References: <200307281412.36981.toby@lyricist.com.au>
<2180.1059698182@sss.pgh.pa.us>
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi Tom,
yes I re-run vacuum analyze every time I muck about with the tables/ indexes.
The output of explain analyze is this:
Sort (cost=1660930.18..1660946.63 rows=6581 width=568) (actual
time=545668.58..545675.05 rows=1537 loops=1)
Sort Key: max(m.date_created)
-> Aggregate (cost=0.00..1659452.99 rows=6581 width=568) (actual
time=614.83..544598.17 rows=1537 loops=1)
-> Group (cost=0.00..1659123.95 rows=65807 width=568) (actual
time=539.87..543988.21 rows=1537 loops=1)
-> Nested Loop (cost=0.00..1657643.30 rows=65807 width=568)
(actual time=539.82..543404.25 rows=1537 loops=1)
Join Filter: ("inner".thread_id =
("outer".thread_id)::double precision)
-> Index Scan using fbof_group_idx on fbof_thread t
(cost=0.00..642.03 rows=1537 width=548) (actual time=29.27..1043.40 rows=1537
loops=1)
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) (actual time=0.08.. 294.28 rows=8563 loops=1537)
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 <toby@lyricist.com.au> 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
- --
- --------------------------------
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/LOIC6KYxVcB16DkRAm/GAJ99ZmFU1iqNyFyQemwfCxcihC1aTwCfWMeo
hxTkh1K9qOwp9XkLsmE4XFQ=
=HXUi
-----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, tgl@sss.pgh.pa.us
Subject: Re: join optimization problem
In-Reply-To: <200308032020.52741.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