Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 26D8E2E0080 for ; Wed, 20 Feb 2008 05:03:12 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 76779-05-5 for ; Wed, 20 Feb 2008 05:02:58 -0400 (AST) Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.176]) by postgresql.org (Postfix) with ESMTP id 9CA1E2E00AD for ; Wed, 20 Feb 2008 05:01:10 -0400 (AST) Received: by py-out-1112.google.com with SMTP id d37so4336626pye.29 for ; Wed, 20 Feb 2008 01:01:09 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=Pq2r4vtw5qCFfkU8EE3pz07YAZZ0/BDYqS/rtwodYA0=; b=sbbV/yVI2A/NwaBHmLsegI9kPVf5ZCRJG/cEzqQKgPiuNZLt+N3NItOepHdkj+N97t1ST+I2n5oSddaTyEnPBjFPLakmracCGZtZAijnTqBeorxzQbm176ARodNfaYOGQxtldS9F0T5c7YcRcfntePJlPfTbIUjkSUOsCD8rUgE= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=F0pXxZpAnotnXum8qei6q+5eY9gDOvR+yMH5QuPwdlzXLwhBgWLqeO856a9UUb/qxwWxJ3v6o0oIuFzAOolrtuwQwFXTxFPvadM8EJG/Yp0N8OAVnS6Yp9jWHAH93BhXlFrHHgvAFIVaSwMgxvs2IhCDqTTyaNZ+qbyl66GybnE= Received: by 10.65.253.6 with SMTP id f6mr15840093qbs.7.1203498069519; Wed, 20 Feb 2008 01:01:09 -0800 (PST) Received: by 10.64.7.14 with HTTP; Wed, 20 Feb 2008 01:01:09 -0800 (PST) Message-ID: <2e78013d0802200101q44dbe6edvac067c39629ed9cd@mail.gmail.com> Date: Wed, 20 Feb 2008 14:31:09 +0530 From: "Pavan Deolasee" To: "Jeff Davis" Subject: Re: 7 hrs for a pg_restore? Cc: "Douglas J Hunley" , pgsql-performance@postgresql.org In-Reply-To: <1203445403.3846.86.camel@dogma.ljc.laika.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <200802191303.58840.doug@hunley.homeip.net> <1203445403.3846.86.camel@dogma.ljc.laika.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/237 X-Sequence-Number: 28779 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 -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com