Re: BUG #11033: 'pg_dump -a' much slower than 'pg_dump'

On 2014-10-11 17:26:59 -0400, Bruce Momjian wrote:
> On Fri, Jul 25, 2014 at 12:10:52AM +0000, joe(at)tanga(dot)com wrote:
> > The following bug has been logged on the website:
> >
> > Bug reference: 11033
> > Logged by: Joe Van Dyk
> > Email address: joe(at)tanga(dot)com
> > PostgreSQL version: 9.3.5
> > Operating system: Ubuntu 12.04
> > Description:
> >
> > I've got a small database that takes about 0.8 seconds to dump with
> > 'pg_dump' and about 11 seconds to dump with 'pg_dump -a'.
> >
> > I've narrowed it down to the foreign key constraints in the database, if
> > those are removed, then 'pg_dump -a' becomes fast again.
> >
> > I can't come up with an artificial test case. I can reproduce it using our
> > company's db schema. I don't want to publicly post that schema to the
> > mailing list, but I'd be happy to send the schema that shows the problem to
> > someone privately.
> >
> > I can reproduce the problem in 9.3.4 and 9.4.beta1.
>
> Wow, that is certainly odd.

There've been a couple cases of that where the dependency resolution
gets more complex for data only dumps because of the added dependencies
that try to get the order right to not violate foreign keys. IIRC Tom
fixed a couple performance problems recently.
Yep, 51fc6133488a80a1310972b8a0ad20aca13f5b02.

Joe, can you check with some more recent version? Unfortunately that fix
got committed after 9.3.5. 9.4 beta3 should be fine.

Andres

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services