From: Stephen Frost <sfrost@snowman.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-hackers@postgresql.org
Subject: Re: pg_dump vs. TRANSFORMs
Date: Thu, 8 Dec 2016 16:41:57 -0500
Message-ID: <20161208214157.GJ23417@tamriel.snowman.net> (raw)
In-Reply-To: <7183.1481230828@sss.pgh.pa.us>
References: <20160504183952.GE10850@tamriel.snowman.net>
<20161207200915.GZ23417@tamriel.snowman.net>
<729.1481151371@sss.pgh.pa.us>
<20161208142116.GC23417@tamriel.snowman.net>
<4955.1481209396@sss.pgh.pa.us>
<20161208204106.GI23417@tamriel.snowman.net>
<7183.1481230828@sss.pgh.pa.us>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
Tom,
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > Ok, thinking a bit more on this and testing, it looks like we record the
> > initdb-defined casts as 'pinned' in pg_depend all the way back to 7.3.
> > Therefore, we could use that as the gating factor for getFuncs() instead
> > of FirstNormalObjectId and then I can also modify getCasts() to exclude
> > pinned casts, which should fix pg_dump against 8.0 and earlier.
>
> Don't really like that; won't it make the getFuncs query substantially
> more expensive?
Adding the check against pg_depend doesn't appear to make it much slower
than having the check against FirstNormalObjectId. Adding either seems
to cause the getFuncs() query to go from ~1.7ms to ~3.4ms for HEAD, at
least on my system, which was also built with debugging and asserts and
all that, so the difference in the field is probably less.
Going back to older versions, the difference drops because we drop the
pg_init_privs logic for pre-9.6, and then drop the check for transforms
in pre-9.5.
All versions are just a few ms from HEAD down to 8.4.
In 8.3 and older, we do start to get slower because we don't use a
Merge Anti Join and instead use a Nested Loop Left Join. I was getting
about:
8.3 - ~35ms
8.2 - ~44ms
8.1 - ~66ms
8.0 - ~82ms
7.4 - ~64ms
7.3 - ~61ms
Again, these were Assert and debug-enabled builds, though, of course,
that's not going to make up for the lack of anti-join.
I'm really not sure that's worth troubling over though as I am not aware
of anyone with hundreds of 8.3 or earlier databases that they're going
to need a really fast pg_dump to work on.
Thanks!
Stephen
Attachments:
signature.asc (application/pgp-signature, 818B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: sfrost@snowman.net, tgl@sss.pgh.pa.us
Subject: Re: pg_dump vs. TRANSFORMs
In-Reply-To: <20161208214157.GJ23417@tamriel.snowman.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox