X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id DF0A09FA6A2 for ; Wed, 24 May 2006 17:06:48 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 29950-05-2 for ; Wed, 24 May 2006 17:06:43 -0300 (ADT) X-Greylist: delayed 00:24:15.633196 by SQLgrey- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 5AAA49F94EA for ; Wed, 24 May 2006 17:06:41 -0300 (ADT) Received: from smtp.blackducksoftware.com (nat.blackducksoftware.com [216.195.218.91]) by svr4.postgresql.org (Postfix) with ESMTP id 1CBDF5AF060 for ; Wed, 24 May 2006 19:42:24 +0000 (GMT) Received: from [10.12.14.86] (tcook.blackducksoftware.com [10.12.14.86]) by smtp.blackducksoftware.com (Postfix) with ESMTP id 847875C458C; Wed, 24 May 2006 15:43:00 -0400 (EDT) Message-ID: <4474B721.8040101@blackducksoftware.com> Date: Wed, 24 May 2006 15:42:25 -0400 From: "Todd A. Cook" User-Agent: Mozilla Thunderbird 1.0.8 (X11/20060411) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane Cc: Ruairi , pgsql-admin@postgresql.org Subject: Re: Restore of pg_dump taking a long time... References: <008c01c67f26$9848caf0$2815a8c0@D5T7W62J> <468.1148496451@sss.pgh.pa.us> In-Reply-To: <468.1148496451@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/344 X-Sequence-Number: 21876 Tom Lane wrote: > > There's not much you can do to improve the performance of a restore > already in progress. Next time you might want to think about kicking up > maintenance_work_mem before you start it; that's about the only thing > you can really do to speed up index builds. I have found that increasing maintenance_work_mem can decrease index build speed on large tables: maintenance_work_mem 32768 262144 1048576 2097151 --------------------------------------------------------------- index 400M rows (min) 55 61 68 70 index 45M rows (min) 4.03 4.22 4.88 2.55 These timings were done on a machine with 8GB ram and postgresql 8.1.3 (from the RPMs on postgresql.org). Each time is an average of three runs; before each run, postgres was shut down and the filesystem with /var/lib/pgsql was remounted to clear the filesystem cache. I'm assuming that the times are increasing because there is less memory available for the filesystem cache. Similarly, I assume that the runs on the smaller table with maintenance_work_mem = 2097151 are faster because everything fits in that space. -- todd