Content-Type: multipart/alternative;
 boundary="------------AOVD9Iagr4x5MTrKfKcg5iOK"
Message-ID: <d456752d-04f2-4ee2-afde-ae0765f7d436@postgrespro.ru>
Date: Fri, 9 May 2025 15:31:11 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Vacuum statistics
To: Andrei Lepikhov <lepihov@gmail.com>,
 Alexander Korotkov <aekorotkov@gmail.com>
Cc: Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>,
 Andrei Zubkov <zubkov@moonset.ru>, Alena Rybakina
 <lena.ribackina@yandex.ru>, pgsql-hackers <pgsql-hackers@postgresql.org>,
 jian he <jian.universality@gmail.com>
References: <bee34abe-fe87-49b3-8523-8ae19e641ccf@yandex.ru>
 <ff99283d-f9d4-4b37-b4fa-3a433fb408a4@tantorlabs.com>
 <e1f5b560691a4fe693625d529e19e13959210568.camel@moonset.ru>
 <53c47c2d-72a5-44f2-900c-9973b2af1808@tantorlabs.com>
 <c82e7776-7841-42a0-9247-878584174750@tantorlabs.com>
 <4a902cea-54fb-41b5-b208-b84731a5f577@postgrespro.ru>
 <CACJufxHb_YGCp=pVH6DZcpk9yML+SueffPeaRbX2LzXZVahd_w@mail.gmail.com>
 <CACJufxE6yAP+jUm4_GyKp7gUCkzbuJ9HGB-rR=92_hcLLZ9KTg@mail.gmail.com>
 <092adec6-4eae-4bd4-bd0d-473a9df1282b@tantorlabs.com>
 <3deae1bd-ad84-4459-a26e-04c9136b84e9@postgrespro.ru>
 <CAPpHfdug0s2MD7bBf-5nDQGn1WBxCKiTmZyGfxHz_7P0CDOjbg@mail.gmail.com>
 <c4e4e305-7119-4183-b49a-d7092f4efba3@postgrespro.ru>
 <CAPpHfdvSo3mfH=2m4ADCHAuN=22SnBY3TrPaPbGKTw3r_Jaw7Q@mail.gmail.com>
 <631e09ab-099c-4089-883d-4524d852dfa2@gmail.com>
Content-Language: en-US
From: Alena Rybakina <a.rybakina@postgrespro.ru>
In-Reply-To: <631e09ab-099c-4089-883d-4524d852dfa2@gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/d456752d-04f2-4ee2-afde-ae0765f7d436%40postgrespro.ru>
Precedence: bulk

This is a multi-part message in MIME format.
--------------AOVD9Iagr4x5MTrKfKcg5iOK
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

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

--------------AOVD9Iagr4x5MTrKfKcg5iOK
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p>Hi!<br>
    </p>
    <div class="moz-cite-prefix">On 22.04.2025 21:23, Andrei Lepikhov
      wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:631e09ab-099c-4089-883d-4524d852dfa2@gmail.com">On
      10/28/24 14:40, Alexander Korotkov wrote:
      <br>
      <blockquote type="cite">On Sun, Aug 25, 2024 at 6:59 PM Alena
        Rybakina
        <br>
        <blockquote type="cite">If I missed something or misunderstood,
          can you explain in more detail?
          <br>
        </blockquote>
        <br>
        Actually, I mean why do we need a possibility to return
        statistics for
        <br>
        all tables/indexes in one function call?  User anyway is
        supposed to
        <br>
        use pg_stat_vacuum_indexes/pg_stat_vacuum_tables view, which do
        <br>
        function calls one per relation.  I suppose we can get rid of
        <br>
        possibility to get all the objects in one function call and just
        <br>
        return a tuple from the functions like other pgstatfuncs.c
        functions
        <br>
        do.
        <br>
      </blockquote>
      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.
      <br>
      <br>
    </blockquote>
    <p>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. <br>
    </p>
    <pre
style="content-visibility: visible; line-height: 1.2em; margin: 0px; color: rgb(0, 0, 0); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial; width: 435ch; contain-intrinsic-height: 1200em;">+CREATE VIEW pg_stat_vacuum_tables AS
...
FROM pg_class rel
+  JOIN pg_namespace ns ON ns.oid = rel.relnamespace,
+  LATERAL <b>pg_stat_get_vacuum_tables(rel.oid)</b> stats
+WHERE rel.relkind = 'r';</pre>
    <p></p>
    <p>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 <br>
      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. <br>
    </p>
    <p>I can add the code if no one is against it.</p>
    <p><br>
    </p>
    <p>[0]
<a class="moz-txt-link-freetext" href="https://www.postgresql.org/message-id/2a04ad18-5572-4633-848b-eb57209e7ac0%40postgrespro.ru">https://www.postgresql.org/message-id/2a04ad18-5572-4633-848b-eb57209e7ac0%40postgrespro.ru</a></p>
    <p>[1]
<a class="moz-txt-link-freetext" href="https://www.postgresql.org/message-id/995657bc-9966-47c0-b085-4c5e8886d249%40postgrespro.ru">https://www.postgresql.org/message-id/995657bc-9966-47c0-b085-4c5e8886d249%40postgrespro.ru</a><br>
    </p>
    <p>[2]
<a class="moz-txt-link-freetext" href="https://www.postgresql.org/message-id/CAPpHfdvSo3mfH%3D2m4ADCHAuN%3D22SnBY3TrPaPbGKTw3r_Jaw7Q%40mail.gmail.com">https://www.postgresql.org/message-id/CAPpHfdvSo3mfH%3D2m4ADCHAuN%3D22SnBY3TrPaPbGKTw3r_Jaw7Q%40mail.gmail.com</a></p>
    <pre class="moz-signature" cols="72">-- 
Regards,
Alena Rybakina
Postgres Professional</pre>
  </body>
</html>

--------------AOVD9Iagr4x5MTrKfKcg5iOK--