MIME-Version: 1.0
References: <CAP53Pky29GWAVVk3oBgKBDqhND0BRBN6yTPeguV_qSivFL5N_g@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>
 <CAApHDvqYL3OVvOsyvEPsDJ7h4zQh=LEygQgcQSN-TemRJLMb6Q@mail.gmail.com>
 <3ea66a50-e4b0-4f20-ae0c-4d9cbaf81cfb@tantorlabs.com> <CAApHDvpuM8jW-BGu7B=i6uv+jmndEo-2z_BYvj5JNmx=qV-uSQ@mail.gmail.com>
 <CA+TgmoZjVQf1-y2yj2OPWuL-eF=+UNv_vTUm_-98wg+Djj-vdA@mail.gmail.com> <CAApHDvoqHSX8OOLQy4Eu2_mz2MWMyHsoArnCy70zHuAV47ssnA@mail.gmail.com>
In-Reply-To: <CAApHDvoqHSX8OOLQy4Eu2_mz2MWMyHsoArnCy70zHuAV47ssnA@mail.gmail.com>
From: Maciek Sakrejda <maciek@pganalyze.com>
Date: Wed, 16 Apr 2025 09:09:41 -0700
Message-ID: <CADXhmgROX1Za_fy0JJj=c7=vVE4T7XArDLHmpofHyzzfWZ+r1Q@mail.gmail.com>
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>, 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>, Andrei Lepikhov <lepihov@gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CADXhmgROX1Za_fy0JJj%3Dc7%3DvVE4T7XArDLHmpofHyzzfWZ%2Br1Q%40mail.gmail.com>
Precedence: bulk

On Tue, Apr 15, 2025 at 1:50=E2=80=AFPM David Rowley <dgrowleyml@gmail.com>=
 wrote:
> On Wed, 16 Apr 2025 at 04:25, Robert Haas <robertmhaas@gmail.com> wrote:
> > On Mon, Apr 14, 2025 at 8:23=E2=80=AFPM David Rowley <dgrowleyml@gmail.=
com> wrote:
> > > "Estimates: capacity=3DN distinct keys=3DN lookups=3DN hit ratio=3DN.=
N%"
> >
> > Is lookups=3DN here the estimated number of lookups i.e. what we think
> > nloops will end up being?
>
> Yes. The estimate is the "calls" variable in cost_memoize_rescan(),
> which is fairly critical in the hit ratio estimate calculation.
>
> Technically this is just the Nested Loop's outer_path->rows. There was
> an argument earlier in the thread for putting this in along with the
> other fields to make things easier to read. I did argue that it was
> redundant due to the fact that the reader can look at the row estimate
> for the outer side of the Nest Loop, but maybe it's small enough to go
> in using the above format.

This kind of thing is nice to have in the text format, but it's really
nice to have when working with structured formats. Currently, to get a
loops estimate for the inner node, I need to find the parent, find the
outer node in the parent's children, and get that sibling's Plan Rows
(I think). It'd be nice to make things like that easier.

Thanks,
Maciek