Horton

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

Tom Lane <tgl@sss.pgh.pa.us>

View thread View on postgr.es
From: Tom Lane <tgl@sss.pgh.pa.us>
To: depesz@depesz.com
Cc: Laurenz Albe <laurenz.albe@cybertec.at>, pgsql-hackers@lists.postgresql.org
Date: 2021-08-31T00:11:00Z
Lists: pgsql-hackers, pgsql-general

Attachments

[ 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.

			regards, tom lane

Commits

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

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