Message-ID: <779faa14-274a-4c29-a40b-72e92478586e@gmail.com>
Date: Mon, 14 Apr 2025 21:49:23 +0200
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>, David Rowley <dgrowleyml@gmail.com>
Cc: Ilia Evdokimov <ilya.evdokimov@tantorlabs.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>
References: <CAP53Pky29GWAVVk3oBgKBDqhND0BRBN6yTPeguV_qSivFL5N_g@mail.gmail.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>
 <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>
Content-Language: en-AU
From: Andrei Lepikhov <lepihov@gmail.com>
In-Reply-To: <CA+TgmobDHmaNTqi5yLPJoW6fTCuiWOgF9CSCSHX_OTLmGxYRUQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/779faa14-274a-4c29-a40b-72e92478586e%40gmail.com>
Precedence: bulk

On 4/14/25 18:31, Robert Haas wrote:
> On Tue, Apr 1, 2025 at 5:07 PM David Rowley <dgrowleyml@gmail.com> wrote:
>> I tried to move things along to address Tom's concern about not
>> wanting to show this in EXPLAIN's standard output. I suggested in [1]
>> that we could use EXPLAIN ANALYZE, but nobody commented on that.
>> EXPLAIN ANALYZE is much more verbose than EXPLAIN already, and if we
>> put these in EXPLAIN ANALYZE then the viewer can more easily compare
>> planned vs actual. I had mentioned that Hash Join does something like
>> this for buckets.
> Having looked at v6, I think it would help, at least if the reader is
> sufficiently knowledgeable. From the values displayed, it looks like
> someone could reconstruct the evict_ratio value in
> cost_memoize_rescan(), and if they know the loop count, also the
> hit_ratio. But that seems hard: if you weren't reading the code, how
> would you know how to do it? Even if you are reading the code, are you
> sure you'd reconstruct it correctly? I wonder why we think it's better
> to display this than a more "cooked" number like the estimated hit
> ratio that was proposed in v1?
+1.
My reasons: a) it more "physical"; b) We don't need to look for the 
corresponding NestLoop JOIN node - my explans usually contain 150+ lines 
and it is hard to get into the analysis something out of current screen.

-- 
regards, Andrei Lepikhov