Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 753AC2E0044 for ; Wed, 20 Feb 2008 07:30:09 -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 19416-08 for ; Wed, 20 Feb 2008 07:30:06 -0400 (AST) Received: from sun.pyrenet (ohp.adsl.pyrenet.fr [62.212.125.145]) by postgresql.org (Postfix) with ESMTP id 926BD2E0032 for ; Wed, 20 Feb 2008 07:30:05 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by sun.pyrenet (Postfix) with ESMTP id AE73F165EF; Wed, 20 Feb 2008 12:30:00 +0100 (CET) X-Virus-Scanned: amavisd-new at pyrenet.fr Received: from sun.pyrenet ([127.0.0.1]) by localhost (sun.pyrenet [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id taJIQ5PDSDhh; Wed, 20 Feb 2008 12:29:59 +0100 (CET) Received: from localhost (localhost [127.0.0.1]) by sun.pyrenet (Postfix) with ESMTP id 529C61556E; Wed, 20 Feb 2008 12:29:59 +0100 (CET) Date: Wed, 20 Feb 2008 12:29:59 +0100 (CET) From: ohp@pyrenet.fr X-X-Sender: ohp@sun.pyrenet Reply-To: ohp@pyrenet.fr To: Pavan Deolasee cc: Jeff Davis , Douglas J Hunley , pgsql-performance@postgresql.org Subject: Re: 7 hrs for a pg_restore? In-Reply-To: <2e78013d0802200101q44dbe6edvac067c39629ed9cd@mail.gmail.com> Message-ID: References: <200802191303.58840.doug@hunley.homeip.net> <1203445403.3846.86.camel@dogma.ljc.laika.com> <2e78013d0802200101q44dbe6edvac067c39629ed9cd@mail.gmail.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/240 X-Sequence-Number: 28782 On Wed, 20 Feb 2008, Pavan Deolasee wrote: > Date: Wed, 20 Feb 2008 14:31:09 +0530 > From: Pavan Deolasee > To: Jeff Davis > Cc: Douglas J Hunley , > pgsql-performance@postgresql.org > Subject: Re: [PERFORM] 7 hrs for a pg_restore? > > On Feb 19, 2008 11:53 PM, Jeff Davis wrote: > > > > > > Keep in mind, if you have several GB worth of indexes, they take up > > basically no space in the logical dump (just the "CREATE INDEX" command, > > and that's it). But they can take a lot of processor time to build up > > again, especially with localized text. > > > > > > I think it would be interesting if we can build these indexes in parallel. > Each index build requires a seq scan on the table. If the table does > not fit in shared buffers, each index build would most likely result > in lots of IO. > > One option would be to add this facility to the backend so that multiple > indexes can be built with a single seq scan of the table. In theory, it > should be possible, but might be tricky given the way index build works > (it calls respective ambuild method to build the index which internally > does the seq scan). > > Other option is to make pg_restore multi-threaded/processed. The > synchronized_scans facility would then synchronize the multiple heap > scans. ISTM that if we can make pg_restore mult-processed, then > we can possibly add more parallelism to the restore process. > > My two cents. > > Thanks, > Pavan > > That'd be great! Maybe an option to pg_restore to spawn AT MOST n processes (1 per CPU) my .02 Euro -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 15, Chemin des Monges +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: ohp@pyrenet.fr ------------------------------------------------------------------------------ Make your life a dream, make your dream a reality. (St Exupery)