Received: from echna.ne.jp (echna.ne.jp [210.175.52.4]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9HELWP37022 for ; Wed, 17 Oct 2001 10:21:33 -0400 (EDT) (envelope-from rk73@echna.ne.jp) Received: from VIP333 [210.175.55.199] by echna.ne.jp with ESMTP (SMTPD32-6.06) id A3BB49800146; Wed, 17 Oct 2001 23:20:43 +0900 Date: Wed, 17 Oct 2001 23:21:30 +0900 From: Masaru Sugawara To: pgsql-sql@postgresql.org Subject: Re: Why would this slow the query down so much? In-Reply-To: References: <14373.1003158756@sss.pgh.pa.us> Errors-To: Message-Id: <20011017215538.5D57.RK73@echna.ne.jp> MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit X-Mailer: Becky! ver. 2.00.06 X-Archive-Number: 200110/234 X-Sequence-Number: 5189 On Tue, 16 Oct 2001 17:58:32 +0100 Stuart Grimshaw wrote: > On Monday 15 October 2001 16:12 pm, Tom Lane wrote: > > Stuart Grimshaw writes: > > > SELECT a.category, b.headline, b.added, c.friendlyname > > > FROM caturljoin as a > > > INNER JOIN stories as b ON (a.url = b.source) > > > INNER JOIN urllist as c ON (a.url = d.urn) > > > WHERE a.category = 93 ORDER BY b.added DESC LIMIT 1; > > > > (I assume "d.urn" is a typo for "c.urn"...) > > > > The query plan you show looks pretty reasonable if the planner's row > > count estimates are in the right ballpark. How many caturljoin rows > > have category = 93? How many stories rows will match each caturljoin > > row? How many urllist rows ditto? > > There are 194 rows in caturljoin where url = 93, 29806 rows in stories will > match those 194 rows and only 1 row in urllist will match. > If it's convenient, would you try to delete some indices of the "stories" table? the total number of sorts on the QUERY PLAN might decrease. However, this trial may be a vain effort. I can't expect the result of the QUERY PLAN. :-) The indices: "stories_source", "stories_unique_story", and "stories_urn_key" Regards, Masaru Sugawara