X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 6EF9932B22F for ; Fri, 22 Oct 2004 04:37:04 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 65995-04 for ; Fri, 22 Oct 2004 03:36:57 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id D460832A33D for ; Fri, 22 Oct 2004 04:37:00 +0100 (BST) 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 i9M3b1CX002569; Thu, 21 Oct 2004 23:37:01 -0400 (EDT) To: Joshua Marsh Cc: pgsql-performance@postgresql.org Subject: Re: Large Database Performance suggestions In-reply-to: <38242de904102120143c55ba34@mail.gmail.com> References: <38242de904102120143c55ba34@mail.gmail.com> Comments: In-reply-to Joshua Marsh message dated "Thu, 21 Oct 2004 21:14:59 -0600" Date: Thu, 21 Oct 2004 23:37:00 -0400 Message-ID: <2568.1098416220@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/375 X-Sequence-Number: 8851 Joshua Marsh writes: > ... We did some original testing and with a server with 8GB or RAM and > found we can do operations on data file up to 50 million fairly well, > but performance drop dramatically after that. What you have to ask is *why* does it drop dramatically? There aren't any inherent limits in Postgres that are going to kick in at that level. I'm suspicious that you could improve the situation by adjusting sort_mem and/or other configuration parameters; but there's not enough info here to make specific recommendations. I would suggest posting EXPLAIN ANALYZE results for your most important queries both in the size range where you are getting good results, and the range where you are not. Then we'd have something to chew on. regards, tom lane