Re: Vacuum statistics

Hi!

On 22.04.2025 21:23, Andrei Lepikhov wrote:
> On 10/28/24 14:40, Alexander Korotkov wrote:
>> On Sun, Aug 25, 2024 at 6:59 PM Alena Rybakina
>>> If I missed something or misunderstood, can you explain in more detail?
>>
>> Actually, I mean why do we need a possibility to return statistics for
>> all tables/indexes in one function call?  User anyway is supposed to
>> use pg_stat_vacuum_indexes/pg_stat_vacuum_tables view, which do
>> function calls one per relation.  I suppose we can get rid of
>> possibility to get all the objects in one function call and just
>> return a tuple from the functions like other pgstatfuncs.c functions
>> do.
> I suppose it was designed this way because databases may contain
> thousands of tables and indexes - remember, at least, partitions. But
> it may be okay to use the SRF_FIRSTCALL_INIT / SRF_RETURN_NEXT API. I
> think by registering a prosupport routine predicting cost and rows of
> these calls, we may let the planner build adequate plans for queries
> involving those stats - people will definitely join it with something
> else in the database.
>
I think we can add this, but first we need to answer the main question -
are there cases when we have statistics for a relation that are not in
pg_class? After all, we have views that show vacuum statistics for all
relations for objects stored in pg_class.

+CREATE VIEW pg_stat_vacuum_tables AS
...
FROM pg_class rel
+ JOIN pg_namespace ns ON ns.oid = rel.relnamespace,
+ LATERAL*pg_stat_get_vacuum_tables(rel.oid)* stats
+WHERE rel.relkind = 'r';

I tend to think that such a case will happen because to solve the
problem with the memory consumed for storing vacuum statistics, we need
to store them separately from the relations' statistics (I already wrote
the code here [0]), so
the approach with the output of all statistics from a snapshot, as we
did here [1] and removed this approach here [2] and this approach now
makes sense and it is worth organizing it as you suggest.

I can add the code if no one is against it.

[0]
https://www.postgresql.org/message-id/2a04ad18-5572-4633-848b-eb57209e7ac0%40postgrespro.ru

[1]
https://www.postgresql.org/message-id/995657bc-9966-47c0-b085-4c5e8886d249%40postgrespro.ru

[2]
https://www.postgresql.org/message-id/CAPpHfdvSo3mfH%3D2m4ADCHAuN%3D22SnBY3TrPaPbGKTw3r_Jaw7Q%40mail.gmail.com

--
Regards,
Alena Rybakina
Postgres Professional