X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 32F42D1B565 for ; Wed, 17 Dec 2003 09:52:51 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 98641-06 for ; Wed, 17 Dec 2003 05:52:22 -0400 (AST) Received: from anchor-post-34.mail.demon.net (anchor-post-34.mail.demon.net [194.217.242.92]) by svr1.postgresql.org (Postfix) with ESMTP id 00DC5D1C990 for ; Wed, 17 Dec 2003 05:52:20 -0400 (AST) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-34.mail.demon.net with esmtp (Exim 3.35 #1) id 1AWYLs-000Fn1-0Y; Wed, 17 Dec 2003 09:52:20 +0000 Received: by mainbox.archonet.com (Postfix, from userid 529) id 3BCCA167DB; Wed, 17 Dec 2003 09:52:19 +0000 (GMT) Received: from client17.archonet.com (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id E5F0615987; Wed, 17 Dec 2003 09:52:16 +0000 (GMT) From: Richard Huxton To: , "Pgsql-Performance@Postgresql. Org" Subject: Re: Nested loop question Date: Wed, 17 Dec 2003 09:52:15 +0000 User-Agent: KMail/1.5 References: In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200312170952.15759.dev@archonet.com> X-Bogosity: No, tests=bogofilter, spamicity=0.000000, version=0.15.3 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/253 X-Sequence-Number: 5113 On Tuesday 16 December 2003 17:06, Nick Fankhauser - Doxpop wrote: > Hi- > > I'm trying to optimize a query that I *think* should run very fast. > Essentially, I'm joining two tables that have very selective indexes and > constraining the query on an indexed field. (There's a third small lookup > table in the mix, but it doesn't really affect the bottom line.) > I'm unsure what is happening next. I notice that an index scan is occurring > on actor_summary_pk, with an "actual time" of 9.15, but then it looks like > a nested loop occurs at the next level to join these tables. Does this mean > that each probe of the actor_summary index will take 9.15 msec, but the > nested loop is going to do this once for each actor_id? That's right - you need to multiply the actual time by the number of loops. In your case this would seem to be about 33 seconds. > -> Index Scan using actor_summary_pk on > actor_summary (cost=0.00..8.11 rows=1 width=72) (actual time=9.14..9.15 > rows=1 loops=3639) > Index Cond: ("outer".actor_id = > actor_summary.actor_id) > The nested loop appears to be where most of my time is going, so I'm > focusing on this area, but don't know if there is a better approach to this > join. > > Is there a more efficient means than a nested loop to handle such a join? > Would a different method be chosen if there was exactly one row in > actor_summary for every row in actor? Hmm - tricky to say in your case. PG has decided to filter on actor then look up the corresponding values in actor_summary. Given that you have 3 million rows in both tables that seems a reasonable approach. You could always try forcing different plans by switching the various ENABLE_HASHJOIN etc options (see the runtime configuration section of the manuals). I'm not sure that will help you here though. The fact that it's taking you 9ms to do each index lookup suggests to me that it's going to disk each time. Does that sound plausible, or do you think you have enough RAM to cache your large indexes? -- Richard Huxton Archonet Ltd