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

joe(at)tanga(dot)com writes:
> 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.

Given that and the warning messages shown in your followup, a plausible
guess is that pg_dump is wasting a lot of time vainly searching for ways
to break the circular dependency loops that arise from trying to order
the tables in a way that allows them to be restored with foreign key
constraints active. However: (a) it seems like it'd take a heck of a lot
of FK constraints for that to become a dominant factor, and (b) if that
were the explanation, seems like it should not be that hard to make an
artificial test case. So I'm thinking there's some other contributing
factor you haven't shown us.

> 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'd be willing to take a look, but it's not clear that there is any easy
fix, and TBH I'm not sure that this case is worth spending a lot of time
on. As the warning messages are telling you, a --data-only dump is of
limited value when you've got circular foreign key references. What
are you intending to do with the dump anyway?

regards, tom lane