Content-Type: multipart/alternative;
 boundary="------------d6LPkMVx280kVySqMgBoRUOF"
Message-ID: <3ba16aa7-3cb2-4289-b773-522ae2f03a77@tantorlabs.com>
Date: Thu, 27 Mar 2025 13:12:38 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost
 adjustment
To: Robert Haas <robertmhaas@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>
Cc: Lukas Fittl <lukas@fittl.com>, David Rowley <dgrowleyml@gmail.com>,
 Andrei Lepikhov <lepihov@gmail.com>, Daniel Gustafsson <daniel@yesql.se>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
 Nikolay Samokhvalov <samokhvalov@gmail.com>
References: 
 <CAP53Pky29GWAVVk3oBgKBDqhND0BRBN6yTPeguV_qSivFL5N_g@mail.gmail.com>
 <51A15CD7-E31B-483D-B911-D0EB9F5FF952@yesql.se>
 <CAP53Pkz-sK8En2c05rJDcV7pz8+OsbNqOnm_SO4tsuwm=n=3OQ@mail.gmail.com>
 <bea2a2ae-3a95-4740-8926-837bacc7bd4e@tantorlabs.com>
 <CAApHDvpq2gOt3gnzkd4Ud=wrT7YRGrF3zb29jgWzDsYEhETrOA@mail.gmail.com>
 <44229379-3901-4cb0-8812-b354ef70d5e2@gmail.com>
 <f68e7271-4648-4e0f-882f-f4e93bc9c9cf@tantorlabs.com>
 <8e12aa55-39eb-4e03-a8f7-077fe02309c3@gmail.com>
 <CAApHDvqHe15LL=K5j71qxLdPCaFFPeohtBe7=sK29Ord3ZVQcg@mail.gmail.com>
 <ff747c1d-1925-45ca-9c47-2763be45cba0@gmail.com>
 <CAApHDvoE5S5FkvEq+N3-J9LfaVUpWLOnczOYAOEvBMCY20=pdg@mail.gmail.com>
 <11c709e1-9b17-4c00-9ead-93609a09b9db@gmail.com>
 <CAApHDvr9kjuo0rX03Qm0cDP_tFNJ8crOtpRzbyS0Zwtg2BorRw@mail.gmail.com>
 <1161645.1742854534@sss.pgh.pa.us>
 <CAP53Pky44MuEYPDFcqR+ogffnifbuXkAWi5TvjhHmpSdj88o8w@mail.gmail.com>
 <1164845.1742856354@sss.pgh.pa.us>
 <CA+TgmoZ8qXiZmmn4P9Mk1cf2mjMMLFPOjSasCjuKSiHFcm-ncw@mail.gmail.com>
Content-Language: en-US
From: Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>
In-Reply-To: 
 <CA+TgmoZ8qXiZmmn4P9Mk1cf2mjMMLFPOjSasCjuKSiHFcm-ncw@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/3ba16aa7-3cb2-4289-b773-522ae2f03a77%40tantorlabs.com>
Precedence: bulk

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


On 26.03.2025 22:37, Robert Haas wrote:
> I also don't think that we should be too concerned about bloating the
> EXPLAIN output in the context of a patch that only affects Memoize.
> Memoize nodes are not incredibly common in the query plans that I see,
> so even if we added another line or three to the output for each one,
> I don't think that would create major problems. On the other hand,
> maybe there's an argument that what this patch touches is only the tip
> of the iceberg, and that we're eventually going to want the same kinds
> of things for Nested Loop and Hash Joins and Merge Joins, and maybe
> even more detail that can be displayed in say 3 lines. In that case,
> there's a double concern. On the one hand, that really would make the
> output a whole lot more verbose, and on the other hand, it might
> generate a fair amount of work to maintain it across future planner
> changes. I can see deciding to reject changes of that sort on the
> grounds that we're not prepared to maintain it, or deciding to gate it
> behind a new option on the grounds that it is so verbose that even
> people who say EXPLAIN VERBOSE are going to be sad if they get all
> that crap by default. I'm not saying that we SHOULD make those
> decisions -- I think exposing more detail here could be pretty useful
> to people trying to solve query plan problems, including me, so I hope
> we don't just kick that idea straight to the curb without due thought
> -- but I would understand them.
>
> The part I'm least sure about with respect to the proposed patch is
> the actual stuff being displayed. I don't have the experience to know
> whether it's useful for tracking down issues. If it's not, then I
> agree we shouldn't display it. If it is, then I'm tentatively in favor
> of showing it in standard EXPLAIN, possibly only with VERBOSE, with
> the caveats from the previous paragraph: if more-common node types are
> also going to have a bunch of stuff like this, then we need to think
> more carefully. If Memoize is exceptional in needing additional
> information displayed, then I think it's fine.
>
> --
> Robert Haas
> EDB:http://www.enterprisedb.com


I understand the concerns raised about the risk of opening the door to 
more diagnostic detail across various plan nodes. However, in Hash Join, 
Merge Join, and Nested Loop, EXPLAIN typically reveals at least some of 
the planner’s expectations. For example, Hash Join shows the number of 
batches and originally expected buckets, giving insight into whether the 
hash table fit in memory. Merge Join shows unexpected Sort nodes when 
presorted inputs were assumed. Nested Loop reflects planner assumptions 
via loops and row estimates. In other words, these nodes expose at least 
some information about what the planner thought would happen.

Memoize is unique in that it shows runtime statistics (hits, misses, 
evictions), but reveals nothing about the planner’s expectations. We 
don’t see how many distinct keys were estimated or how many entries the 
planner thought would fit in memory. This makes it very difficult to 
understand whether Memoize was a good choice or not, or how to fix it 
when it performs poorly.

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

--------------d6LPkMVx280kVySqMgBoRUOF
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><br>
    </p>
    <div class="moz-cite-prefix">On 26.03.2025 22:37, Robert Haas wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CA+TgmoZ8qXiZmmn4P9Mk1cf2mjMMLFPOjSasCjuKSiHFcm-ncw@mail.gmail.com"><span
      style="white-space: pre-wrap">
</span><span style="white-space: pre-wrap">
</span>
      <pre class="moz-quote-pre" wrap="">I also don't think that we should be too concerned about bloating the
EXPLAIN output in the context of a patch that only affects Memoize.
Memoize nodes are not incredibly common in the query plans that I see,
so even if we added another line or three to the output for each one,
I don't think that would create major problems. On the other hand,
maybe there's an argument that what this patch touches is only the tip
of the iceberg, and that we're eventually going to want the same kinds
of things for Nested Loop and Hash Joins and Merge Joins, and maybe
even more detail that can be displayed in say 3 lines. In that case,
there's a double concern. On the one hand, that really would make the
output a whole lot more verbose, and on the other hand, it might
generate a fair amount of work to maintain it across future planner
changes. I can see deciding to reject changes of that sort on the
grounds that we're not prepared to maintain it, or deciding to gate it
behind a new option on the grounds that it is so verbose that even
people who say EXPLAIN VERBOSE are going to be sad if they get all
that crap by default. I'm not saying that we SHOULD make those
decisions -- I think exposing more detail here could be pretty useful
to people trying to solve query plan problems, including me, so I hope
we don't just kick that idea straight to the curb without due thought
-- but I would understand them.

The part I'm least sure about with respect to the proposed patch is
the actual stuff being displayed. I don't have the experience to know
whether it's useful for tracking down issues. If it's not, then I
agree we shouldn't display it. If it is, then I'm tentatively in favor
of showing it in standard EXPLAIN, possibly only with VERBOSE, with
the caveats from the previous paragraph: if more-common node types are
also going to have a bunch of stuff like this, then we need to think
more carefully. If Memoize is exceptional in needing additional
information displayed, then I think it's fine.

--
Robert Haas
EDB: <a class="moz-txt-link-freetext" href="http://www.enterprisedb.com">http://www.enterprisedb.com</a>
</pre>
    </blockquote>
    <p><br>
    </p>
    <p>I understand the concerns raised about the risk of opening the
      door to more diagnostic detail across various plan nodes. However,
      in Hash Join, Merge Join, and Nested Loop, EXPLAIN typically
      reveals at least some of the planner’s expectations. For example,
      Hash Join shows the number of batches and originally expected
      buckets, giving insight into whether the hash table fit in memory.
      Merge Join shows unexpected Sort nodes when presorted inputs were
      assumed. Nested Loop reflects planner assumptions via loops and
      row estimates. In other words, these nodes expose at least some
      information about what the planner thought would happen.</p>
    <p>Memoize is unique in that it shows runtime statistics (hits,
      misses, evictions), but reveals nothing about the planner’s
      expectations. We don’t see how many distinct keys were estimated
      or how many entries the planner thought would fit in memory. This
      makes it very difficult to understand whether Memoize was a good
      choice or not, or how to fix it when it performs poorly.</p>
    <p>--<br>
      Best regards,<br>
      Ilia Evdokimov,<br>
      Tantor Labs LLC.<br>
    </p>
  </body>
</html>

--------------d6LPkMVx280kVySqMgBoRUOF--