Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id F21222E0088 for ; Wed, 20 Feb 2008 14:05:13 -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 92271-01-3 for ; Wed, 20 Feb 2008 14:05:09 -0400 (AST) Received: from serv01.siteground137.com (ns1.siteground137.com [67.15.250.10]) by postgresql.org (Postfix) with ESMTP id 1DD1F2E0070 for ; Wed, 20 Feb 2008 14:04:53 -0400 (AST) Received: from cragnux.laika.com ([209.162.219.253]:64893 helo=dogma.ljc.laika.com) by serv01.siteground137.com with esmtpa (Exim 4.68) (envelope-from ) id 1JRtJK-0002NE-BB; Wed, 20 Feb 2008 12:04:50 -0600 Subject: Re: 7 hrs for a pg_restore? From: Jeff Davis To: Pavan Deolasee Cc: Douglas J Hunley , pgsql-performance@postgresql.org In-Reply-To: <2e78013d0802200101q44dbe6edvac067c39629ed9cd@mail.gmail.com> References: <200802191303.58840.doug@hunley.homeip.net> <1203445403.3846.86.camel@dogma.ljc.laika.com> <2e78013d0802200101q44dbe6edvac067c39629ed9cd@mail.gmail.com> Content-Type: text/plain Date: Wed, 20 Feb 2008 10:04:47 -0800 Message-Id: <1203530687.3846.151.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/253 X-Sequence-Number: 28795 On Wed, 2008-02-20 at 14:31 +0530, Pavan Deolasee wrote: > 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. He's already said that his I/O usage was not the problem. For one thing, he has 8GB of memory for a 5GB dataset. Even when the table is much larger than memory, what percentage of the time is spent on the table scan? A table scan is O(N), whereas an index build is O(N logN). If you combine that with expensive comparisons, e.g. for localized text, then I would guess that the index building itself was much more expensive than the scans themselves. However, building indexes in parallel would allow better CPU utilization. > 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). I don't think that this would be necessary, because (as you say below) the synchronized scan facility should already handle this. > 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. I like this approach more. I think that pg_restore is the right place to do this, if we can make the options reasonably simple enough to use. See: http://archives.postgresql.org/pgsql-hackers/2008-02/msg00699.php Regards, Jeff Davis