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 AACABD1B486 for ; Wed, 17 Dec 2003 15:26:44 +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 45255-01 for ; Wed, 17 Dec 2003 11:26:16 -0400 (AST) Received: from shire.ontko.com (shire.ontko.com [199.164.165.1]) by svr1.postgresql.org (Postfix) with ESMTP id 7D279D1B488 for ; Wed, 17 Dec 2003 11:26:12 -0400 (AST) Received: from nick (bilbo.ontko.com [199.164.165.101]) by shire.ontko.com (8.12.3/8.12.3/Debian-6.6) with SMTP id hBHFQ65j020851; Wed, 17 Dec 2003 10:26:13 -0500 Reply-To: From: "Nick Fankhauser" To: "Richard Huxton" , "Pgsql-Performance@Postgresql. Org" Subject: Re: Nested loop question Date: Wed, 17 Dec 2003 10:26:19 -0500 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) In-Reply-To: <200312170952.15759.dev@archonet.com> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Importance: Normal X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/255 X-Sequence-Number: 5115 > 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? I'm sure we don't have enough RAM to cache all of our large indexes, so your supposition makes sense. We have 1GB on this machine. In responding to the performance problems we're having, one of the questions has been adding memory vs crafting "helper" tables to speed things up. The issue is that this database needs to be able to scale easily to about 10 times the size, so although we could easily triple the memory at reasonable expense, we'd eventually hit a wall. Is there any solid method to insure that a particular index always resides in memory? A hybrid approach that might scale reliably would be to bump up our memory and then make sure key indexes are cached. however, I'm concerned that if we didn't have a way to ensure that the indexes that we choose remain cached, we would have very inconsistent responses. Thanks for your ideas! -Nick