On Mon, Aug 30, 2021 at 08:11:00PM -0400, Tom Lane wrote: > [ redirecting to -hackers ] > > I wrote: > > 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. > > I decided that that patch wasn't too safe, because it applies > format_type() to pg_type rows that we have no reason to trust the > longevity of. I think it could fall over if some concurrent process > were busy dropping a temp table, for example. > > So here's a version that just does plain caching of the results > of retail getFormattedTypeName() calls. This visibly adds no > queries that were not done before, so it should be safe enough. > And there can't be any cases that it makes slower, either.
Hi, tested it in my case, and it reduced query count to 4261. Which is great. But, I also looked closer into the pg_proc queries and extensions. And - most functions come from relatively standard extensions: - postgis 1246 functions - btree_gist 179 functions - btree_gin 87 functions - hstore 58 functions My point in here is that potential optimizations regarding queries for pg_proc might speed up dumps for more people - as they might use things like postgis, but never realized that it can be much faster. Best regards, depesz
