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

From: Joe Van Dyk <joe(at)tanga(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #11033: 'pg_dump -a' much slower than 'pg_dump'
Date: 2014-07-25 00:23:20
Message-ID: CACfv+pLAkXGrMJ3sXSjzzF00yu7W9fUzUwK6u57=pCdGQXvvZQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

This is what I'm seeing, as far as timing.

$ time pg_dump tanga_dev > /dev/null

real 0m1.182s
user 0m0.260s
sys 0m0.020s

$ time pg_dump -a tanga_dev > /dev/null
pg_dump: NOTICE: there are circular foreign-key constraints among these
table(s):
pg_dump: products
pg_dump: You might not be able to restore the dump without using
--disable-triggers or temporarily dropping the constraints.
pg_dump: Consider using a full dump instead of a --data-only dump to avoid
this problem.
<snip a few more of these>

real 0m11.390s
user 0m10.653s
sys 0m0.032s

On Thu, Jul 24, 2014 at 5:10 PM, <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.
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message enzesheng 2014-07-25 00:51:50 Re: BUG #11025: could not access status of transaction 7
Previous Message joe 2014-07-25 00:10:52 BUG #11033: 'pg_dump -a' much slower than 'pg_dump'