Content-Type: multipart/alternative;
 boundary="------------nzmYHhivehr40JZ7DBcoZu0F"
Message-ID: <06840539-e321-4930-9f89-cfc783c5b80a@tantorlabs.com>
Date: Sun, 27 Apr 2025 22:58:52 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost
 adjustment
To: David Rowley <dgrowleyml@gmail.com>
Cc: Robert Haas <robertmhaas@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>,
 Lukas Fittl <lukas@fittl.com>, Daniel Gustafsson <daniel@yesql.se>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
 Nikolay Samokhvalov <samokhvalov@gmail.com>,
 Andrei Lepikhov <lepihov@gmail.com>
References: <CAP53Pky29GWAVVk3oBgKBDqhND0BRBN6yTPeguV_qSivFL5N_g@mail.gmail.com>
 <1164845.1742856354@sss.pgh.pa.us>
 <CA+TgmoZ8qXiZmmn4P9Mk1cf2mjMMLFPOjSasCjuKSiHFcm-ncw@mail.gmail.com>
 <3ba16aa7-3cb2-4289-b773-522ae2f03a77@tantorlabs.com>
 <81ef0f5a-b6a1-4417-ae3a-2bae9b8d419e@gmail.com>
 <67b5c84a-dedd-4103-af7e-0117046102fe@tantorlabs.com>
 <580fe72c-85be-45a8-9e00-6b5417690557@tantorlabs.com>
 <CAApHDvpT0Y1BfYgeWz+PkKaLBEff1izYOAjNSibhBTNFEdqLtg@mail.gmail.com>
 <CA+TgmobDHmaNTqi5yLPJoW6fTCuiWOgF9CSCSHX_OTLmGxYRUQ@mail.gmail.com>
 <CAApHDvqYL3OVvOsyvEPsDJ7h4zQh=LEygQgcQSN-TemRJLMb6Q@mail.gmail.com>
 <3ea66a50-e4b0-4f20-ae0c-4d9cbaf81cfb@tantorlabs.com>
 <CAApHDvpuM8jW-BGu7B=i6uv+jmndEo-2z_BYvj5JNmx=qV-uSQ@mail.gmail.com>
 <0e8dc4fa-3655-4e9f-9424-9b32a15e6acd@tantorlabs.com>
 <CAApHDvqmyHuEt7rdFLJr4cZecbhno5OZ6iL5EQNNu9Oi1f93sQ@mail.gmail.com>
 <7e995158-157e-4ec6-a7c8-266c1a6f7b43@tantorlabs.com>
 <CAApHDvq0Zz3+nh9hPRBP-oAocFZETfhdE_jcN8aG+Thc24Zk5g@mail.gmail.com>
Content-Language: en-US
From: Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>
In-Reply-To: <CAApHDvq0Zz3+nh9hPRBP-oAocFZETfhdE_jcN8aG+Thc24Zk5g@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/06840539-e321-4930-9f89-cfc783c5b80a%40tantorlabs.com>
Precedence: bulk

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

Sorry for the delayed reply.


On 18.04.2025 00:29, David Rowley wrote:
> I know I did suggest that the hit_ratio should be a percent and it
> should be multiplied by 100.0.  This seems fine for the text format as
> you can have the % unit there. However, looking at
> ExplainPropertyFloat(), we don't print the unit for non-text formats.
> I wonder if printing a number between 0 and 100 there will be
> confusing. I don't believe we have anything else in EXPLAIN that shows
> a percentage. I don't quite know what to do about this. One thought I
> had was to only * 100 for text format, but that might be more
> confusing.


I agree that displaying the percentage instead of the fraction is a 
better choice, because it is much more convenient for analysis. If this 
value were intended to be passed into some API for further computations, 
it would make sense to keep it as a fraction to avoid unnecessary 
multiplications and divisions by 100. However, such use cases seem 
extremely unlikely.

Therefore, I think it is better to report percentages directly. Since 
non-text EXPLAIN formats do not display units, I propose to rename the 
field to "hit_percent" in all formats, including the text format. This 
way, the meaning of the value remains clear without needing additional 
context. What do you think about this approach?


>
> Aside from that, I'd prefer the new fields in struct Memoize to be
> prefixed with "est_" the same as the existing "est_entries" field. I'm
> unsure why MemoizePath.calls becomes Memoize.lookups. Seems
> unnecessary and just means more brain space being used to maintain a
> mental model.


Agree.

--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC.

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

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p>Sorry for the delayed reply.</p>
    <p><br>
    </p>
    <div class="moz-cite-prefix">On 18.04.2025 00:29, David Rowley
      wrote:<span style="white-space: pre-wrap">
</span></div>
    <blockquote type="cite"
cite="mid:CAApHDvq0Zz3+nh9hPRBP-oAocFZETfhdE_jcN8aG+Thc24Zk5g@mail.gmail.com">
      <pre class="moz-quote-pre" wrap="">
I know I did suggest that the hit_ratio should be a percent and it
should be multiplied by 100.0.  This seems fine for the text format as
you can have the % unit there. However, looking at
ExplainPropertyFloat(), we don't print the unit for non-text formats.
I wonder if printing a number between 0 and 100 there will be
confusing. I don't believe we have anything else in EXPLAIN that shows
a percentage. I don't quite know what to do about this. One thought I
had was to only * 100 for text format, but that might be more
confusing.</pre>
    </blockquote>
    <p><br>
    </p>
    <p>I agree that displaying the percentage instead of the fraction is
      a better choice, because it is much more convenient for analysis.
      If this value were intended to be passed into some API for further
      computations, it would make sense to keep it as a fraction to
      avoid unnecessary multiplications and divisions by 100. However,
      such use cases seem extremely unlikely.</p>
    <p>Therefore, I think it is better to report percentages directly.
      Since non-text EXPLAIN formats do not display units, I propose to
      rename the field to "hit_percent" in all formats, including the
      text format. This way, the meaning of the value remains clear
      without needing additional context. What do you think about this
      approach?</p>
    <p><br>
    </p>
    <blockquote type="cite"
cite="mid:CAApHDvq0Zz3+nh9hPRBP-oAocFZETfhdE_jcN8aG+Thc24Zk5g@mail.gmail.com">
      <pre class="moz-quote-pre" wrap="">

Aside from that, I'd prefer the new fields in struct Memoize to be
prefixed with "est_" the same as the existing "est_entries" field. I'm
unsure why MemoizePath.calls becomes Memoize.lookups. Seems
unnecessary and just means more brain space being used to maintain a
mental model.
</pre>
    </blockquote>
    <p><br>
    </p>
    <p>Agree.</p>
    <p>--<br>
      Best regards,<br>
      Ilia Evdokimov,<br>
      Tantor Labs LLC.<br>
    </p>
  </body>
</html>

--------------nzmYHhivehr40JZ7DBcoZu0F--