Date: Fri, 27 Aug 2021 17:58:36 -0400
From: Stephen Frost <sfrost@snowman.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>, depesz@depesz.com,
	pgsql-general <pgsql-general@lists.postgresql.org>
Subject: Re: Can we get rid of repeated queries from pg_dump?
Message-ID: <20210827215836.GT17906@tamriel.snowman.net>
References: <20210826084430.GA26282@depesz.com>
 <648517.1629986527@sss.pgh.pa.us>
 <20210826140827.GA15868@depesz.com>
 <649446.1629987629@sss.pgh.pa.us>
 <20210826160644.GA3120@depesz.com>
 <e6a7503afd115d111fbb96c5787906d73cfe2605.camel@cybertec.at>
 <896732.1630099403@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="KMpoMTeiOjOCaFAr"
Content-Disposition: inline
In-Reply-To: <896732.1630099403@sss.pgh.pa.us>
User-Agent: Mutt/1.5.24 (2015-08-30)
Archived-At: 
 <https://www.postgresql.org/message-id/20210827215836.GT17906%40tamriel.snowman.net>
Precedence: bulk


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

Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Laurenz Albe <laurenz.albe@cybertec.at> writes:
> > On Thu, 2021-08-26 at 18:06 +0200, hubert depesz lubaczewski wrote:
> >> In total, there were 5000 queries:
> >> SELECT pg_catalog.format_type('[0-9]+'::pg_catalog.oid, NULL)
> >> But there were only 83 separate oids that were scanned.
>=20
> > That is a strong argument for using a hash table to cache the types.
>=20
> Those queries are coming from getFormattedTypeName(), which is used
> for function arguments and the like.  I'm not quite sure why Hubert
> is seeing 5000 such calls in a database with only ~100 functions;
> surely they don't all have an average of 50 arguments?
>=20
> I experimented with the attached, very quick-n-dirty patch to collect
> format_type results during the initial scan of pg_type, instead.  On the
> regression database in HEAD, it reduces the number of queries pg_dump
> issues from 3260 to 2905; but I'm having a hard time detecting any net
> performance change.

Seems like the issue here is mainly just the latency of each query being
rather high compared to most use-cases, so local testing where there's
basically zero latency wouldn't see any change in timing, but throw a
trans-atlantic or worse amount of latency between the system running
pg_dump and the PG server and you'd see notable wall-clock savings in
time.

Only took a quick look but generally +1 on reducing the number of
queries that pg_dump is doing and the changes suggested looked good to
me.

Thanks,

Stephen

--KMpoMTeiOjOCaFAr
Content-Type: application/pgp-signature; name="signature.asc"

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

iQIcBAEBCgAGBQJhKWAMAAoJEO1sijiDR2RVIqMP/3UPD7pyVv54FQ7r6g9INhGF
CgEbaNRerX5wJQU+2hCn2ARtntxzatBfq6966NSYtRDyOf8fSH8zp3I9CfU1yWIW
loaheSJd99gGgAGPN9hacjcTB3rVSM0gIyBikhMFtvf9mFlIf4IVk3FdeyTR7wjI
Ce01n4/ToAfFRVPnA42pgvrGJKZ/My2qYKhUNDauAQsbpbpkDTDgTtkTSpAhOlea
ddzO1/fSWrBHQLtnaVtMaaD7WmoDs5wwZ0iwMpebj90wLPqCWoPrmtlesGcsycyJ
905zsrNQ5/aes4tbupS5HXblaWWtOwDxlnviKnXM0sdgIngNRzD9DoAucgB223+X
esIwfBC+gpEn+myGrKp3Z6bVNYdSH0P+yiu6o53OB0qv6RPrGK/dZ5Ac5qB5MCGO
6eOta69ZDznW83K7FOQRETGjNB6KcJ+CU8D8YYYBmoaceLK9/61zH2+H7tJEbFTR
MPhFOY4cmPheTjpFwBKp/aNIRSYbeTA31BKWzl/lh4x5nIe9vezvDasgClNkBh8j
GO0rbHbhk3WhvZ2jDaGZhoy8hX50grVCLLpF+/kODqEqh2tyFuaKIIDj30ON6CBQ
sbGvkE3A/n38gaYT4/EVJixL/P3ebtsEKNAAHzhtmC/0MsxsmqjECHF2eflmLmjG
VGQAPNGIJ37txxyVFoBs
=JN2/
-----END PGP SIGNATURE-----

--KMpoMTeiOjOCaFAr--