Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id BD6BA9FA26D for ; Wed, 17 Jan 2007 18:12:50 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 40822-10 for ; Wed, 17 Jan 2007 18:12:33 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from wolff.to (wolff.to [66.93.197.194]) by postgresql.org (Postfix) with SMTP id CC8489FB283 for ; Wed, 17 Jan 2007 18:12:32 -0400 (AST) Received: (qmail 11597 invoked by uid 500); 17 Jan 2007 22:12:29 -0000 Date: Wed, 17 Jan 2007 16:12:29 -0600 From: Bruno Wolff III To: Adam Rich Cc: Steve , pgsql-performance@postgresql.org Subject: Re: Configuration Advice Message-ID: <20070117221229.GA9719@wolff.to> Mail-Followup-To: Bruno Wolff III , Adam Rich , Steve , pgsql-performance@postgresql.org References: <20070117212352.7B6EC9FB28E@postgresql.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20070117212352.7B6EC9FB28E@postgresql.org> User-Agent: Mutt/1.4.2.1i X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/284 X-Sequence-Number: 22885 > From: "Steve" > To: pgsql-performance@postgresql.org > Sent: 1/17/2007 2:41 PM > Subject: [PERFORM] Configuration Advice > > SO ... our goal here is to make this load process take less time. It > seems the big part is building the big summary table; this big summary > table is currently 9 million rows big. Every night, we drop the table, > re-create it, build the 9 million rows of data (we use COPY to put hte > data in when it's prepared, not INSERT), and then build the indexes on it > -- of which there are many. Unfortunately this table gets queried > in a lot of different ways and needs these indexes; also unfortunately, we > have operator class indexes to support both ASC and DESC sorting on > columns so these are for all intents and purposes duplicate but required > under Postgres 8.1 (we've recently upgraded to Postgres 8.2, is this still > a requirement?) Note that you only need to have the ASC and DESC versions of opclasses when you are going to use multicolumn indexes with some columns in ASC order and some in DESC order. For columns used by themselves in an index, you don't need to do this, no matter which order you are sorting on.