Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 660B02E006D for ; Wed, 20 Feb 2008 14:35:39 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 56647-04 for ; Wed, 20 Feb 2008 14:35:36 -0400 (AST) Received: from serv01.siteground137.com (ns1.siteground137.com [67.15.250.10]) by postgresql.org (Postfix) with ESMTP id 06FF92E005A for ; Wed, 20 Feb 2008 14:35:37 -0400 (AST) Received: from cragnux.laika.com ([209.162.219.253]:63743 helo=dogma.ljc.laika.com) by serv01.siteground137.com with esmtpa (Exim 4.68) (envelope-from ) id 1JRtn5-0002cL-J5; Wed, 20 Feb 2008 12:35:35 -0600 Subject: Re: 7 hrs for a pg_restore? From: Jeff Davis To: Matthew Cc: pgsql-performance@postgresql.org In-Reply-To: References: <200802191303.58840.doug@hunley.homeip.net> <1203445403.3846.86.camel@dogma.ljc.laika.com> <2e78013d0802200101q44dbe6edvac067c39629ed9cd@mail.gmail.com> <1203530687.3846.151.camel@dogma.ljc.laika.com> Content-Type: text/plain Date: Wed, 20 Feb 2008 10:35:32 -0800 Message-Id: <1203532532.3846.158.camel@dogma.ljc.laika.com> Mime-Version: 1.0 X-Mailer: Evolution 2.0.4 (2.0.4-7) Content-Transfer-Encoding: 7bit X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - serv01.siteground137.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - j-davis.com X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/255 X-Sequence-Number: 28797 On Wed, 2008-02-20 at 18:18 +0000, Matthew wrote: > On Wed, 20 Feb 2008, Jeff Davis wrote: > > However, building indexes in parallel would allow better CPU > > utilization. > > We have a process here that dumps a large quantity of data into an empty > database, much like pg_restore, and then creates all the indexes at the > end. In order to speed up that bit, I initially made it spawn off several > threads, and make each thread run a CREATE INDEX operation in parallel. > However, this resulted in random errors from Postgres - something to do > with locked tables. So I changed it so that no two threads create indexes > for the same table at once, and that solved it. What was the specific problem? Were they UNIQUE indexes? Were you trying to write to the tables while indexing? Did you use "CONCURRENTLY"? Regards, Jeff Davis