From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
Cc: | depesz(at)depesz(dot)com, pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Can we get rid of repeated queries from pg_dump? |
Date: | 2021-08-27 21:23:23 |
Message-ID: | 896732.1630099403@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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?
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
Attachment | Content-Type | Size |
---|---|---|
avoid-repeated-format_type-queries-0.1.patch | text/x-diff | 3.2 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Rob Sargent | 2021-08-27 21:28:19 | Re: Idempotent DDL Updates |
Previous Message | Adrian Klaver | 2021-08-27 20:32:31 | Re: Idempotent DDL Updates |
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2021-08-27 21:53:01 | Re: Can we get rid of repeated queries from pg_dump? |
Previous Message | Andres Freund | 2021-08-27 21:05:47 | Re: Async-unsafe functions in signal handlers |