From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: depesz@depesz.com, pgsql-general <pgsql-general@lists.postgresql.org>
Subject: Re: Can we get rid of repeated queries from pg_dump?
Date: Fri, 27 Aug 2021 14:53:01 -0700
Message-ID: <1fecd427-291e-5494-4b5e-0315f762f892@aklaver.com> (raw)
In-Reply-To: <896732.1630099403@sss.pgh.pa.us>
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>
On 8/27/21 2:23 PM, Tom Lane 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.
>
>> That is a strong argument for using a hash table to cache the types.
>
> 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?
Could be.
From the stats post:
"Based on my reading of queries in order it seems to follow the pattern of:
One call for:
SELECT proretset, prosrc, probin, provolatile, proisstrict,
prosecdef, lanname, proconfig, procost, prorows,
pg_catalog.pg_get_function_arguments(p.oid) AS funcargs,
pg_catalog.pg_get_function_identity_arguments(p.oid) AS funciargs,
pg_catalog.pg_get_function_re
sult(p.oid) AS funcresult, proleakproof, array_to_string(protrftypes,
' ') AS protrftypes, proparallel, prokind, prosupport, NULL AS
prosqlbody FROM pg_catalog.pg_proc p, pg_catalog.pg_language l WHERE
p.oid = 'SOME_NUMBER'::pg_catalog.oid AND l.oid = p.prolang
and then one or more:
SELECT pg_catalog.format_type('SOME_NUMBER'::pg_catalog.oid, NULL)
In one case, after proc query, there were 94 concecutive
pg_catalog.format_type queries.
"
>
> 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.
>
> (This is not meant for commit as-is; notably, I didn't bother to fix
> getTypes' code paths for pre-9.6 servers. It should be fine for
> performance testing though.)
>
> regards, tom lane
>
--
Adrian Klaver
adrian.klaver@aklaver.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: adrian.klaver@aklaver.com, tgl@sss.pgh.pa.us, laurenz.albe@cybertec.at, pgsql-general@lists.postgresql.org
Subject: Re: Can we get rid of repeated queries from pg_dump?
In-Reply-To: <1fecd427-291e-5494-4b5e-0315f762f892@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox