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

On Wed, Oct 15, 2014 at 11:58 PM, Joe Van Dyk <joe(at)tanga(dot)com> wrote:

> On Sat, Oct 11, 2014 at 2:47 PM, Andres Freund <andres(at)2ndquadrant(dot)com>
> wrote:
>
>> 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.
>>
>
> I've been using the 9.3-stable branch for the past few months (as a result
> of needing this fix), this problem has gone away as far as I can tell.
>
>
I forgot this never got posted to the list -- this is what Tom wrote back
in July:

"I've not entirely worked out just what's creating the performance issue,
but it seemed like contributing factors included (a) there were long
chains of FK references (not loops, just table A refers to table B refers
to table C yadda yadda), and (b) there were a lot of plain old objects,
such as functions. The reference chains seemed to be creating a
combinatorial explosion in the number of times the search reached the
plain old objects, but I've still not quite wrapped my head around exactly
why. It might be that there's an easier/better fix available than this
one."

Joe