Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 89A96B53E86 for ; Fri, 25 May 2012 13:56:32 -0300 (ADT) Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SXxoV-0005Is-Oe for pgsql-performance@postgresql.org; Fri, 25 May 2012 16:56:32 +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 q4PGuH3h024046; Fri, 25 May 2012 12:56:17 -0400 (EDT) To: Jeff Janes cc: Bruce Momjian , Craig James , 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> Comments: In-reply-to Jeff Janes message dated "Fri, 25 May 2012 08:40:04 -0700" Date: Fri, 25 May 2012 12:56:17 -0400 Message-ID: <24045.1337964977@sss.pgh.pa.us> From: Tom Lane X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201205/145 X-Sequence-Number: 46969 Jeff Janes writes: > There is an operation in pg_dump which is O(#_of_schemata_in_db * > #_of_table_in_db), or something like that. > The attached very crude patch reduces that to > O(log_of_#_of_schemata_in_db * #_of_table_in_db) > I was hoping this would be a general improvement. It doesn't seem be. > But it is a very substantial improvement in the specific case of > dumping one small schema out of a very large database. Your test case in shows pretty conclusively that findNamespace is a time sink for large numbers of schemas, so that seems worth fixing. I don't like this patch though: we already have infrastructure for this in pg_dump, namely buildIndexArray/findObjectByOid, so what we should do is use that not invent something new. I will go see about doing that. > It seems like dumping one schema would be better optimized by not > loading up the entire database catalog, but rather by restricting to > just that schema at the catalog stage. The reason pg_dump is not built that way is that considerations like dump order dependencies are not going to work at all if it only looks at a subset of the database. Of course, dependency chains involving objects not dumped might be problematic anyway, but I'd still want it to do the best it could. > For dumping entire databases, It looks like the biggest problem is > going to be LockReassignCurrentOwner in the server. And that doesn't > seem to be easy to fix, as any change to it to improve pg_dump will > risk degrading normal use cases. I didn't try profiling the server side, but pg_dump doesn't use subtransactions so it's not clear to me why LockReassignCurrentOwner would get called at all ... regards, tom lane