pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Denis <socsam@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: [HACKERS] pg_dump and thousands of schemas
Date: Tue, 06 Nov 2012 12:51:04 -0500
Message-ID: <16061.1352224264@sss.pgh.pa.us> (raw)
In-Reply-To: <1352216421913-5730877.post@n5.nabble.com>
References: <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>
	<CA+TgmobHksoQmbLyAMB7D5FwqDGA8+u5rooviEuP8rg_zcMzGg@mail.gmail.com>
	<27683.1346383075@sss.pgh.pa.us>
	<CAMkU=1xAUY_aCiWeVL1J5Xrub4vMObWG9UnOQrTR==6z4w1dUw@mail.gmail.com>
	<CA+TgmobHDYs2RoL0P+Fx0-W-f00ziy8M6pE7tSCuDAt8OTyRNg@mail.gmail.com>
	<1352211374706-5730864.post@n5.nabble.com>
	<13005.1352214454@sss.pgh.pa.us>
	<1352216421913-5730877.post@n5.nabble.com>

Denis <socsam@gmail.com> writes:
> Here is the output of EXPLAIN ANALYZE. It took 5 seconds but usually it
> takes from 10 to 15 seconds when I am doing backup.

> Sort  (cost=853562.04..854020.73 rows=183478 width=219) (actual
> time=5340.477..5405.604 rows=183924 loops=1)

Hmmm ... so the problem here isn't that you've got 2600 schemas, it's
that you've got 183924 tables.  That's going to take some time no matter
what.

It does seem like we could make some small changes to optimize that
query a little bit, but they're not going to result in any amazing
improvement overall, because pg_dump still has to deal with all the
tables it's getting back.  Fundamentally, I would ask whether you really
need so many tables.  It seems pretty likely that you have lots and lots
of basically-identical tables.  Usually it would be better to redesign
such a structure into fewer tables with more index columns.

> Here is the output of "pg_dump -s"  test.dump
> <http://postgresql.1045698.n5.nabble.com/file/n5730877/test.dump;  

This dump contains only 1 schema and 43 tables, so I don't think it's
for the database you're having trouble with ...

			regards, tom lane




view thread (66+ messages)  latest in thread

Message-ID: <16061.1352224264@sss.pgh.pa.us>
Permalink:  ../16061.1352224264@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/16061.1352224264@sss.pgh.pa.us

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, socsam@gmail.com
  Subject: Re: [HACKERS] pg_dump and thousands of schemas
  In-Reply-To: <16061.1352224264@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox