Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 422E31C11167 for ; Mon, 28 May 2012 19:26:51 -0300 (ADT) Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SZ8Oo-0001Rh-6A for pgsql-performance@postgresql.org; Mon, 28 May 2012 22:26:50 +0000 Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id q4SMQahV015139; Mon, 28 May 2012 18:26:36 -0400 (EDT) To: Jeff Janes cc: Hugo , pgsql-performance@postgresql.org Subject: Re: pg_dump and thousands of schemas In-reply-to: References: <1337843209018-5709766.post@n5.nabble.com> <20120525035455.GC25444@momjian.us> <1337921645663-5709975.post@n5.nabble.com> <20887.1337956883@sss.pgh.pa.us> <1338091933763-5710183.post@n5.nabble.com> Comments: In-reply-to Jeff Janes message dated "Mon, 28 May 2012 14:24:26 -0700" Date: Mon, 28 May 2012 18:26:36 -0400 Message-ID: <15138.1338243996@sss.pgh.pa.us> From: Tom Lane X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201205/162 X-Sequence-Number: 46986 Jeff Janes writes: > There is a quadratic behavior in pg_dump's "mark_create_done". This > should probably be fixed, but in the mean time it can be circumvented > by using -Fc rather than -Fp for the dump format. Doing that removed > 17 minutes from the run time. Hmm, that would just amount to postponing the work from pg_dump to pg_restore --- although I suppose it could be a win if the dump is for backup purposes and you probably won't ever have to restore it. inhibit_data_for_failed_table() has the same issue, though perhaps it's less likely to be exercised; and there is a previously noted O(N^2) behavior for the loop around repoint_table_dependencies. We could fix these things by setting up index arrays that map dump ID to TocEntry pointer and dump ID of a table to dump ID of its TABLE DATA TocEntry. The first of these already exists (tocsByDumpId) but is currently built only if doing parallel restore. We'd have to build it all the time to use it for fixing mark_create_done. Still, the extra space is small compared to the size of the TocEntry data structures, so I don't see that that's a serious objection. I have nothing else to do right now so am a bit tempted to go fix this. > I'm working on a patch to reduce the LockReassignCurrentOwner problem > in the server when using pg_dump with lots of objects. Cool. regards, tom lane