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

On Thu, Jul 24, 2014 at 6:32 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> 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 don't have an unreasonable amount of foreign key constraints, as far as I
can tell.

For an example, on one of my slower systems, adding two foreign key
constraints caused 'pg_dump -a' to get 5 seconds slower (goes from 15
seconds to 20) -- the constraints are on tables that have 12 and 5 rows.

I'll send you a test db shortly. Thanks for looking into it!

>
> > 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?
>

This is used for running automated tests that modify the database. Before
the tests are run, I use 'pg_dump -a' to generate the initial set of data
that the tests use. Then, before each test, I disable all the triggers,
truncate all the tables, load the data-only dump into the database, then
re-enable the triggers. As far as I can tell, this is the fastest way to
revert a database to a known state, it's faster than dropping/recreating
databases.

Joe

>
> regards, tom lane
>