Received: from garner.smgsystems.co.uk ([212.56.92.26]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9GGwiP02769 for ; Tue, 16 Oct 2001 12:58:44 -0400 (EDT) (envelope-from stuart@smgsystems.co.uk) Received: from ism.smgsystems.co.uk ([10.0.0.1] helo=there) by garner.smgsystems.co.uk with smtp (Exim 3.22 #1 (Debian)) id 15tXY1-0005ZQ-00; Tue, 16 Oct 2001 17:58:33 +0100 Content-Type: text/plain; charset="iso-8859-1" From: Stuart Grimshaw To: Tom Lane , pgsql-sql@postgresql.org Subject: Re: Why would this slow the query down so much? Date: Tue, 16 Oct 2001 17:58:32 +0100 X-Mailer: KMail [version 1.3] References: <14373.1003158756@sss.pgh.pa.us> In-Reply-To: <14373.1003158756@sss.pgh.pa.us> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-Id: X-Archive-Number: 200110/214 X-Sequence-Number: 5169 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. -- | Stuart Grimshaw | Chief Operations Officer | Football Networks Ltd |- | t:07976 625221 | f:0870 7060260