Re: Can we get rid of repeated queries from pg_dump?

hubert depesz lubaczewski <depesz(at)depesz(dot)com> writes:
> On Fri, Aug 27, 2021 at 05:23:23PM -0400, Tom Lane wrote:
>> I experimented with the attached, very quick-n-dirty patch to collect
>> format_type results during the initial scan of pg_type, instead.

> So, I applied it to brand new HEAD from git, Result:
> From total of 9173 queries it went down to 4178.
> Originally 5000 type queries, now 19!
> This is actually strange given that previously it was asking querying
> about 83 separate type oids. But, as far as I was able to check with
> "pg_restore -l" (from -Fc dump), results are the same.

Hm. So we're still no wiser than before about how such a small (in
terms of number of objects) database could have produced so many
getFormattedTypeName calls. Plus, this result raises a new question:
with the patch, I think you shouldn't have seen *any* queries of that
form. Where are the 19 survivors coming from?

I don't suppose you could send me a schema-only dump of that
database, off-list? I'm now quite curious.

> But since we're looking at it, and with both patches applied, I looked
> at the next most common query. Which is:
> [ collection of details about a function ]

> The thing is - even though it was called 1804 times, dump contains data only
> about 107 functions (pg_restore -l schema.dump | grep -c FUNCTION), so it kinda
> seems that 94% of these calls is not needed.

Hm. It's not doing that for *every* row in pg_proc, at least.
I speculate that it is collecting and then not printing the info
about functions that are in extensions --- can you check on
how many there are of those?

(Actually, if you've got a whole lot of objects inside extensions,
maybe that explains the 5000 calls?)

regards, tom lane