Date: Thu, 8 Dec 2016 16:41:57 -0500
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
Message-ID: <20161208214157.GJ23417@tamriel.snowman.net>
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>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="6kWzDLe2BYLyVXGp"
Content-Disposition: inline
In-Reply-To: <7183.1481230828@sss.pgh.pa.us>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org


--6kWzDLe2BYLyVXGp
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

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.
>=20
> 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

--6kWzDLe2BYLyVXGp
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJYSdOlAAoJEO1sijiDR2RVnTkQAMALH6Xc+Mg56TcX0nFmfhiP
ijt+y7Plpyhrcqw6Es2G7mWSLtn0Z6Po7HgdcduoHucRdF4EcuwQjUaQd8yZiv5r
UtHsaixfs0ztC52T1fpmXy+Frj/q+D4GXlCBhX9rHMjCg2fRrlqw2GH5nVuRgz60
+Q8jX8TwDxrDpTy/bZo0lyodqX4w8JkjjER01tnIs/CopV2ocHf1J56WYuLEfqf9
36K+nNeoIbovhDDIElf6pk1qb5s31a4J6oI4FoH5kEuAlhy414adWj2LbvhtwTGr
lM3Pj9lvTumeCgyIb5EObRmF/Hdy34GDyXlRRLui14AObCIlno67HFnm76Yuwml7
7IUCn/Ba1G4X3GAUL8D5iP+/HOUREIKf0WPlSpCc3Wc7+OUulMGc/pk/8eVUq/4a
xsTf/Dp7Ixv05gmv+O/c86pMvm6QZmm6EaolKuQOe/7FYmqZD5IweQv69LR0RgQt
E3j5O0awEfIZ9NFudf7TXE81LVjZ1M4n2ZxuGyXszEyy7OUdt3oskCn7RWwBPbMr
0ex45kdGV3B2I/zwDNZ0jrWUwKJh2YfhA1fW8tHdqYAsx2JOGK4qOuJmHwuGThRa
+fKqnt1BrNRIfcUdppdFSNalDYoRBq2gy9f6hISjVK47xp26S2+005BrS4qYRrpK
zuZJH3awKvLNiwftZKV6
=d002
-----END PGP SIGNATURE-----

--6kWzDLe2BYLyVXGp--