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 8481514B281B for ; Wed, 11 Aug 2004 12:55:37 -0300 (ADT) 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 05940-03 for ; Wed, 11 Aug 2004 15:55:26 +0000 (GMT) Received: from gawab.com (v2.gawab.com [204.97.230.42]) by svr1.postgresql.org (Postfix) with SMTP id A2C7214B2820 for ; Wed, 11 Aug 2004 12:55:23 -0300 (ADT) Received: (qmail 30538 invoked by uid 1004); 11 Aug 2004 15:55:21 -0000 Received: from unknown (HELO ?192.168.200.2?) (skpobox@202.164.39.130) by gawab.com with SMTP; 11 Aug 2004 15:55:21 -0000 Subject: Re: Hardware upgrade for a high-traffic database From: Sanjay Arora Reply-To: skpobox@gawab.com To: Postgresql Performance In-Reply-To: <6EE64EF3AB31D5448D0007DD34EEB3412A7449@Herge.rcsinc.local> References: <6EE64EF3AB31D5448D0007DD34EEB3412A7449@Herge.rcsinc.local> Content-Type: text/plain Organization: Transcontinental Impex Pvt. Ltd. Message-Id: <1092239230.2277.5.camel@Sewak.Asr.Lan> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.2 (1.2.2-5) Date: 11 Aug 2004 21:17:10 +0530 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=3.6 tagged_above=0.0 required=5.0 tests=RCVD_IN_DSBL, RCVD_IN_NJABL, RCVD_IN_NJABL_PROXY, RCVD_IN_RFCI, RCVD_IN_SORBS, RCVD_IN_SORBS_HTTP X-Spam-Level: *** X-Archive-Number: 200408/156 X-Sequence-Number: 7792 On Wed, 2004-08-11 at 17:51, Merlin Moncure wrote: > One thing you might consider is materialized views. Your aggregate > functions are killing you...try to avoid using them (except min/max on > an index). Just watch out for mutable functions like now(). > > http://www.varlena.com/varlena/GeneralBits/Tidbits/matviews.html > > An application specific approach is to use triggers to keep the data you > need in as close to query form as possible...you can reap enormous > savings particularly if your queries involve 3 or more tables or have > large aggregate scans. I thought materialized views support in pgsql was experimental as yet. Are the pg mat-view code upto production servers? Also, do you have to delete mat-views before you dump the db or does dump automatically not dump the mat-views data? Sanjay.