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
