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 1BAC8D1B491 for ; Wed, 7 Jan 2004 17:15:34 +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 45804-06 for ; Wed, 7 Jan 2004 13:14:51 -0400 (AST) Received: from expasy-ng.isb-sib.ch (expasy-f.unige.ch [192.33.215.142]) by svr1.postgresql.org (Postfix) with ESMTP id 58975D1B4B3 for ; Wed, 7 Jan 2004 13:12:28 -0400 (AST) Received: from caliente (router.isb-sib.ch [192.33.215.254]) (authenticated bits=0) by expasy-ng.isb-sib.ch (8.12.10/8.12.8) with ESMTP id i07H80is030931 for ; Wed, 7 Jan 2004 18:08:04 +0100 Message-ID: <001101c3d540$d3ea8090$c300000a@caliente> From: "Eric Jain" To: "pgsql-performance" Subject: Index creation Date: Wed, 7 Jan 2004 18:08:06 +0100 Organization: Swiss Institute of Bioinformatics MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-sib-MailScanner-Information: Please contact the ISP for more information X-sib-MailScanner: Found to be clean X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200401/79 X-Sequence-Number: 5254 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? -- Eric Jain