Re: pg_dump dump catalog ACLs

* Noah Misch (noah(at)leadboat(dot)com) wrote:
> On Wed, May 04, 2016 at 08:14:55AM -0400, Stephen Frost wrote:
> > * Noah Misch (noah(at)leadboat(dot)com) wrote:
> > > On Mon, Apr 25, 2016 at 12:39:09AM -0400, Stephen Frost wrote:
> > > > * Noah Misch (noah(at)leadboat(dot)com) wrote:
> > > > > On Fri, Apr 22, 2016 at 12:31:41PM -0400, Stephen Frost wrote:
> > > > > > After looking through the code a bit, I realized that there are a lot of
> > > > > > object types which don't have ACLs at all but which exist in pg_catalog
> > > > > > and were being analyzed because the bitmask for pg_catalog included ACLs
> > > > > > and therefore was non-zero.
> > > > > >
> > > > > > Clearing that bit for object types which don't have ACLs improved the
> > > > > > performance for empty databases quite a bit (from about 3s to a bit
> > > > > > under 1s on my laptop). That's a 42-line patch, with comment lines
> > > > > > being half of that, which I'll push once I've looked into the other
> > > > > > concerns which were brought up on this thread.
> > > > >
> > > > > That's good news.
> > > >
> > > > Attached patch-set includes this change in patch #2.
> > >
> > > Timings for the 100-database pg_dumpall:
> > >
> > > HEAD: 131s
> > > HEAD+patch: 33s
> > > 9.5: 8.6s
> > >
> > > Nice improvement for such a simple patch.
> >
> > Patch #2 in the attached patchset includes that improvement and a
> > further one which returns the performance to very close to 9.5.
>
> What timings did you measure? (How close?)

On my laptop, with 100 databases, I get:

9.5: 0m3.306s, 0m3.290s, 0m3.309s, avg: 3.302
HEAD+patch: 0m4.681s, 0m4.681s, 0m4.709s, avg: 4.690

9.5 was installed from Debian packages, HEAD+patch was built with -O2,
no debugging, no casserts, though it's entirely possible I've got
something else enabled in my builds that slow it down a bit.

In any case, as I was saying, that's far closer to 9.5 run-time. I've
not measured the time added when things like TRANSFORMs were added, but
it wouldn't surprise me if adding a new query for every database to
pg_dump adds something similar to this difference.

Thanks!

Stephen