Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TVjwu-0006zA-Ho for pgsql-performance@postgresql.org; Tue, 06 Nov 2012 14:16:16 +0000 Received: from sam.nabble.com ([216.139.236.26]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TVjwt-0005KC-1h for pgsql-performance@postgresql.org; Tue, 06 Nov 2012 14:16:15 +0000 Received: from [192.168.236.26] (helo=sam.nabble.com) by sam.nabble.com with esmtp (Exim 4.72) (envelope-from ) id 1TVjws-00052k-Nb for pgsql-performance@postgresql.org; Tue, 06 Nov 2012 06:16:14 -0800 Date: Tue, 6 Nov 2012 06:16:14 -0800 (PST) From: Denis To: pgsql-performance@postgresql.org Message-ID: <1352211374706-5730864.post@n5.nabble.com> In-Reply-To: References: <20120529.185149.1127748531209576379.t-ishii@sraoss.co.jp> <12147.1338306742@sss.pgh.pa.us> <20120530.180620.600165924826262795.t-ishii@sraoss.co.jp> <20120531.092043.1953698679173443322.t-ishii@sraoss.co.jp> <20120830204433.GW8753@momjian.us> <1086.1346359916@sss.pgh.pa.us> <27683.1346383075@sss.pgh.pa.us> Subject: Re: [HACKERS] pg_dump and thousands of schemas MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: 0.0 (/) X-Archive-Number: 201211/51 X-Sequence-Number: 48417 I've read all the posts in thread, and as I understood in version 9.2 some patches were applied to improve pg_dump speed. I've just installed PostgreSQL 9.2.1 and I still have the same problem. I have a database with 2600 schemas in it. I try to dump each schema individually, but it takes too much time for every schema (about 30-40 seconds per schema, no matter what the data size is). Also for each schema dump I have a slow query log entry, here is an example: >2012-11-06 13:15:32 GMTLOG: duration: 12029.334 ms statement: SELECT c.tableoid, c.oid, c.relname, c.relacl, c.relkind, c.relnamespace, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = c.relowner) AS rolname, c.relchecks, c.relhastriggers, c.relhasindex, c.relhasrules, c.relhasoids, c.relfrozenxid, tc.oid AS toid, tc.relfrozenxid AS tfrozenxid, c.relpersistence, CASE WHEN c.reloftype <> 0 THEN c.reloftype::pg_catalog.regtype ELSE NULL END AS reloftype, d.refobjid AS owning_tab, d.refobjsubid AS owning_col, (SELECT spcname FROM pg_tablespace t WHERE t.oid = c.reltablespace) AS reltablespace, array_to_string(c.reloptions, ', ') AS reloptions, array_to_string(array(SELECT 'toast.' || x FROM unnest(tc.reloptions) x), ', ') AS toast_reloptions FROM pg_class c LEFT JOIN pg_depend d ON (c.relkind = 'S' AND d.classid = c.tableoid AND d.objid = c.oid AND d.objsubid = 0 AND d.refclassid = c.tableoid AND d.deptype = 'a') LEFT JOIN pg_class tc ON (c.reltoastrelid = tc.oid) WHERE c.relkind in ('r', 'S', 'v', 'c', 'f') ORDER BY c.oid -- View this message in context: http://postgresql.1045698.n5.nabble.com/pg-dump-and-thousands-of-schemas-tp5709766p5730864.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com.