MIME-Version: 1.0
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>
 <3ba16aa7-3cb2-4289-b773-522ae2f03a77@tantorlabs.com>
In-Reply-To: <3ba16aa7-3cb2-4289-b773-522ae2f03a77@tantorlabs.com>
From: Robert Haas <robertmhaas@gmail.com>
Date: Thu, 27 Mar 2025 08:46:07 -0400
Message-ID: 
 <CA+TgmoaYA6hy6hynQZ_Lwk-_HkhXhH6oDGSPFLQjcDgL0kSFcQ@mail.gmail.com>
Subject: Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost
 adjustment
To: Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, 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>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2BTgmoaYA6hy6hynQZ_Lwk-_HkhXhH6oDGSPFLQjcDgL0kSFcQ%40mail.gmail.com>
Precedence: bulk

On Thu, Mar 27, 2025 at 6:12=E2=80=AFAM Ilia Evdokimov
<ilya.evdokimov@tantorlabs.com> wrote:
> I understand the concerns raised about the risk of opening the door to mo=
re diagnostic detail across various plan nodes. However, in Hash Join, Merg=
e Join, and Nested Loop, EXPLAIN typically reveals at least some of the pla=
nner=E2=80=99s expectations. For example, Hash Join shows the number of bat=
ches 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 informatio=
n about what the planner thought would happen.
>
> Memoize is unique in that it shows runtime statistics (hits, misses, evic=
tions), but reveals nothing about the planner=E2=80=99s expectations. We do=
n=E2=80=99t see how many distinct keys were estimated or how many entries t=
he planner thought would fit in memory. This makes it very difficult to und=
erstand whether Memoize was a good choice or not, or how to fix it when it =
performs poorly.

Right. Without taking a strong position on this particular patch, I
think it's entirely reasonable, as a concept, to think of making
Memoize work similarly to what other nodes already do.

--=20
Robert Haas
EDB: http://www.enterprisedb.com