Re: pg_dump needs SELECT privileges on irrelevant extension table

On Mon, Mar 20, 2023 at 11:23 AM Jacob Champion <jchampion(at)timescale(dot)com> wrote:
> On Mon, Mar 20, 2023 at 10:43 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > I fear that it's
> > also fairly expensive: adding sub-selects to the query we must do
> > before we can lock any tables is not appetizing, because making that
> > window wider adds to the risk of deadlocks, dump failures, etc.
>
> I was hoping an EXISTS subselect would be cheap enough, but maybe I
> don't have enough entries in pg_policy to see a slowdown. Any
> suggestions on an order of magnitude so I can characterize it? Or
> would you just like to know at what point I start seeing slower
> behavior? (Alternatively: are there cheaper ways to write this query?)

As a smoke test, I have 10M policies spread across 100k tables on my
laptop (that is, 100 policies each). I also have 100k more empty
tables with no policies on them, to try to stress both sides of the
EXISTS. On PG11, the baseline query duration is roughly 20s; with the
patch, it increases to roughly 22s (~10% slowdown). Setup SQL
attached.

This appears to be tied to the number of policies more than the number
of tables; if I reduce it to "only" 1M policies, the slowdown drops to
~400ms (2%), and at 10k policies any difference is lost in noise. That
doesn't seem unreasonable to me, but I don't know what a worst-case
pg_policy catalog looks like.

--Jacob