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

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

>
> I'm not sure whether --missing-stats-only was intended to work for
> non-superusers, but if so, this restriction is inconvenient. Would it
> make sense to use the views pg_stats and pg_stats_ext instead?
> Since the catalogs are only consulted to check whether statistics exist,
> the views should be sufficient. Thought?
>

It seems like it should be possible.

Initially, I was afraid that index statistics don't make it into pg_stats,
but experimentation shows that they do as far back as I looked (10-stable).

A similar issue might happen with tables themselves, but if the user
doesn't have permission to see the stats for that table, they weren't going
to get far trying to vacuum the table.

Any issue with expressions columns in an index seems to be resolved as
well, as the generated pg_attribute.attname is carried forward. I suppose
there might be a collision with old stats and renamed columns, but that's a
rare case, and it would still mean that the table had stats, just not for
the new column.

The main problem would be if has_column_privilege() works on indexes for
non-owners all the way back, if it doesn't, we're stuck.

One issue that may come up is that because pg_stats and pg_stats_ext are
security barrier views, the planner is prone to hash joins (and thus full
scans) of pg_stats, ignoring otherwise indexes that are ideal for
single-row lookups like you get with EXISTS and NOT EXISTS clauses. See
comment about "pg_class_relname_nsp_index" and associated query block in
pg_dump.c for an example of what we had to do to make the planner avoid a
full-scan.

Assuming that I'm not missing something, the fix seems straightforward.
I'll set about coding it up tomorrow if nobody has done so by then.