Re: vacuumdb --missing-stats-only and permission issue

On Fri, Aug 22, 2025 at 11:20 PM Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:

> On Sat, Aug 23, 2025 at 12:00 PM Nathan Bossart
> <nathandbossart(at)gmail(dot)com> wrote:
> >
> > On Sat, Aug 23, 2025 at 10:59:43AM +0900, Fujii Masao wrote:
> > > I tested by creating many tables with make installcheck and running
> > > vacuumdb --missing-stats-only on the regression database.
> > > Without the patch, the query to find tables to analyze took about 60
> ms,
> > > but with the patch it took 18 seconds. That seems too slow,
> > > so probably we'll need to tune the query?
>

Sounds exactly like the issue we encountered with queries that used
pg_stats in pg_dump.

> >
> > Hm. Maybe we should just document that the option requires SELECT
> > privileges on pg_statistic and pg_statistic_ext_data (which are
> restricted
> > to superusers by default). I suspect we have relatively limited
> > opportunities for tuning the query, and I'd like to avoid invasive
> changes
> > to v18 at this point.
>
> Yeah, adding a note about the permissions required for
> --missing-stats-only,
> leaving the query unchanged in v18, and revisiting the issue in v19 seems
> reasonable given the limited time before the v18 release.
>

Rather than resorting to the redundant where-clause trick that we did in
pg_dump.

Looking to v19, I think the problem could be solved if we exposed starelid
in pg_stats and stxrelid in pg_stats_ext and joined on those. pg_dump would
benefit from that as well, eventually.