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: Stephen Frost <sfrost@snowman.net>, Laurenz Albe <laurenz.albe@cybertec.at>, pgsql-general <pgsql-general@lists.postgresql.org>
Date: 2021-08-28T22:26:21Z
Lists: pgsql-hackers, pgsql-general

Attachments

Here is a second patch, quite independent of the first one, that
gets rid of some other repetitive queries.  On the regression database,
the number of queries needed to do "pg_dump -s regression" drops from
3260 to 2589, and on my machine it takes 1.8 sec instead of 2.1 sec.

What's attacked here is a fairly silly decision in getPolicies()
to query pg_policy once per table, when we could do so just once.
It might have been okay if we skipped the per-table query for
tables that lack policies, but it's not clear to me that we can
know that without looking into pg_policy.  In any case I doubt
this is ever going to be less efficient than the original coding.

			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.