X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 663959DCB11 for ; Fri, 6 Jan 2006 19:48:02 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 25659-09 for ; Fri, 6 Jan 2006 19:48:05 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 04B4C9DCB65 for ; Fri, 6 Jan 2006 19:47:59 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id k06Nlt9g023744; Fri, 6 Jan 2006 18:47:55 -0500 (EST) To: peter royal cc: pgsql-performance@postgresql.org Subject: Re: help tuning queries on large database In-reply-to: References: Comments: In-reply-to peter royal message dated "Fri, 06 Jan 2006 17:59:24 -0500" Date: Fri, 06 Jan 2006 18:47:55 -0500 Message-ID: <23743.1136591275@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.073 required=5 tests=[AWL=0.073] X-Spam-Score: 0.073 X-Spam-Level: X-Archive-Number: 200601/46 X-Sequence-Number: 16524 peter royal writes: > So, my question is, is there anything I can do to boost performance > with what I've got, or am I in a position where the only 'fix' is > more faster disks? I can't think of any schema/index changes that > would help, since everything looks pretty optimal from the 'explain > analyze' output. I'd like to get a 10x improvement when querying from > the 'cold' state. I don't think you have any hope of improving the "cold" state much. The right way to think about this is not to be in the "cold" state. Check your kernel parameters and make sure it's not set to limit the amount of memory used for cache (I'm not actually sure if there is such a limit on Linux, but there definitely is on some other Unixen). Look around and see if you can reduce the memory used by processes, or even better, offload non-database tasks to other machines. Basically you need to get as much of the database as you can to stay in disk cache. regards, tom lane