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 862F13A4449 for ; Tue, 26 Oct 2004 17:39:50 +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 35967-08 for ; Tue, 26 Oct 2004 16:39:46 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 4352C3A442F for ; Tue, 26 Oct 2004 17:39:47 +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 i9QGdiRV010370; Tue, 26 Oct 2004 12:39:44 -0400 (EDT) To: Joshua Marsh Cc: pg@fastcrypt.com, pgsql-performance@postgresql.org Subject: Re: Large Database Performance suggestions In-reply-to: <38242de904102608246d060f7d@mail.gmail.com> References: <38242de904102120143c55ba34@mail.gmail.com> <4178F149.9090408@fastcrypt.com> <38242de904102608246d060f7d@mail.gmail.com> Comments: In-reply-to Joshua Marsh message dated "Tue, 26 Oct 2004 09:24:08 -0600" Date: Tue, 26 Oct 2004 12:39:44 -0400 Message-ID: <10369.1098808784@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/430 X-Sequence-Number: 8906 Joshua Marsh writes: > shared_buffers = 1000 # min 16, at least max_connections*2, 8KB each This is on the small side for an 8G machine. I'd try 10000 or so. > sort_mem = 4096000 Yikes. You do realize you just said that *each sort operation* can use 4G? (Actually, it's probably overflowing internally; I dunno what amount of sort space you are really ending up with but it could be small.) Try something saner, maybe in the 10 to 100MB range. > vacuum_mem = 1024000 This is probably excessive as well. > #max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes each > #max_fsm_relations = 1000 # min 100, ~50 bytes each You will need to bump these up a good deal to avoid database bloat. > Occasionally, because we store data from several sources, we will have > requests for data from several sources. We simply intersect the > view_of_data table with a sources table that lists what acctno belong > to what source. This query would look something like this: > SELECT acctno FROM view_of_data WHERE has_name AND is_active_member > AND state = 'OH' INTERSECT SELECT acctno FROM sources_data WHERE > source = 175; IMHO you need to rethink your table layout. There is simply no way that that query is going to be fast. Adding a source column to view_of_data would work much better. If you're not in a position to redo the tables, you might try it as a join: SELECT acctno FROM view_of_data JOIN sources_data USING (acctno) WHERE has_name AND is_active_member AND state = 'OH' AND source = 175; but I'm not really sure if that will be better or not. regards, tom lane