X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 24A76D1E20A for ; Wed, 7 Jan 2004 17:21:21 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 48514-02 for ; Wed, 7 Jan 2004 13:20:38 -0400 (AST) Received: from jefftrout.com (h00a0cc4084e5.ne.client2.attbi.com [24.128.241.68]) by svr1.postgresql.org (Postfix) with SMTP id 51AFCD1E1EA for ; Wed, 7 Jan 2004 13:19:42 -0400 (AST) Received: (qmail 42047 invoked from network); 7 Jan 2004 17:19:51 -0000 Received: from localhost (HELO squeegit) (threshar@127.0.0.1) by localhost with SMTP; 7 Jan 2004 17:19:51 -0000 Date: Wed, 7 Jan 2004 12:20:15 -0500 From: Jeff To: "Eric Jain" Cc: pgsql-performance@postgresql.org Subject: Re: Index creation Message-Id: <20040107122015.1466a8c4.threshar@torgo.978.org> In-Reply-To: <001101c3d540$d3ea8090$c300000a@caliente> References: <001101c3d540$d3ea8090$c300000a@caliente> X-Mailer: Sylpheed version 0.9.7 (GTK+ 1.2.10; i686-pc-linux-gnu) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/81 X-Sequence-Number: 5256 On Wed, 7 Jan 2004 18:08:06 +0100 "Eric Jain" wrote: > Any tips for speeding up index creation? > > I need to bulk load a large table with 100M rows and several indexes, > some of which span two columns. > > By dropping all indexes prior to issuing the 'copy from' command, the > operation completes 10x as fast (1.5h vs 15h). > > Unfortunately, recreating a single index takes nearly as long as > loading all of the data into the table; this more or less eliminates > the time gained by dropping the index in the first place. > > Also, there doesn't seem to be a simple way to disable/recreate all > indexes for a specific table short of explicitely dropping and later > recreating each index? Before creating your index bump up your sort_mem high. set sort_mem = 64000 create index foo on baz(a, b); BIG increases. [This also helps on FK creation] -- Jeff Trout http://www.jefftrout.com/ http://www.stuarthamm.net/