Horton

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

Gus Spier <gus.spier@gmail.com>

View thread View on postgr.es
From: Gus Spier <gus.spier@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: depesz@depesz.com, Stephen Frost <sfrost@snowman.net>, Laurenz Albe <laurenz.albe@cybertec.at>, pgsql-general <pgsql-general@lists.postgresql.org>
Date: 2021-08-29T11:35:21Z
Lists: pgsql-hackers, pgsql-general 
You guys are brilliant!

Regards,

Gus

On Sat, Aug 28, 2021 at 6:26 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

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