Horton

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

hubert depesz lubaczewski <depesz@depesz.com>

View thread View on postgr.es
From: hubert depesz lubaczewski <depesz@depesz.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>, pgsql-general <pgsql-general@lists.postgresql.org>
Date: 2021-08-28T06:46:50Z
Lists: pgsql-hackers, pgsql-general 
On Fri, Aug 27, 2021 at 05:23:23PM -0400, Tom Lane wrote:
> 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?

Oh. missed that part.
So I checked. In the mean time I got -Fc dump, so:

#v+
=$ pg_restore -l schema.dump | \
    grep -P '^\d*; \d+ \d+ FUNCTION ' |
    sed 's/^[^(]*(//; s/)[^)]*$//' |
    awk -F, '{print NF}' |
    sort -n |
    uniq -c
23 0
52 1
21 2
 8 3
 1 4
 2 5
#v-

23 functions with 0 arguments, 52 with 1, and the max is 5 arguments - two
functions have these.

Not sure if it matters but there is a lot of enums. 83 of them. And they have
up to 250 elements (2 such types).

Best regards,

depesz

Commits

  1. In pg_dump, avoid doing per-table queries for RLS policies.

  2. Cache the results of format_type() queries in pg_dump.