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>
In-Reply-To: <3ea66a50-e4b0-4f20-ae0c-4d9cbaf81cfb@tantorlabs.com>
From: David Rowley <dgrowleyml@gmail.com>
Date: Tue, 15 Apr 2025 12:23:11 +1200
Message-ID: <CAApHDvpuM8jW-BGu7B=i6uv+jmndEo-2z_BYvj5JNmx=qV-uSQ@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: 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>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAApHDvpuM8jW-BGu7B%3Di6uv%2BjmndEo-2z_BYvj5JNmx%3DqV-uSQ%40mail.gmail.com>
Precedence: bulk

On Tue, 15 Apr 2025 at 11:14, Ilia Evdokimov
<ilya.evdokimov@tantorlabs.com> wrote:
> On 14.04.2025 23:53, David Rowley wrote:
> If we can't get consensus for everything people want to add at once
> then maybe the patch could be broken into two, with 0001 being pretty
> much the v4 patch and then have 0002 add the Estimated Hit Ratio.
> Having the physical patches and being able to try it out or view the
> regression test changes might lower the bar on people chipping in with
> their views.
>
>
> Agreed - I=E2=80=99ll split the patch into two: one adds Estimated Capaci=
ty and Estimated Distinct Keys, and the second adds Estimated Hit Ratio. I=
=E2=80=99ll also add regression test differences to make it easier to evalu=
ate the usefulness of each part.

Thanks for the patches.

I'm just looking for ways to allow us to group all three of these
together for the TEXT format type. I see the BUFFERS are displayed
quite compactly, e.g. "Buffers: shared hit=3D17 read=3D4".
show_wal_usage() does something similar and so does
show_modifytable_info() for MERGE.

Maybe we could compress the text output a bit with:

"Estimates: capacity=3DN distinct keys=3DN hit ratio=3DN.N%"

If we get it that compact, maybe lookups could fit in there too, i.e.:

"Estimates: capacity=3DN distinct keys=3DN lookups=3DN hit ratio=3DN.N%"

I'd also like to vote that you modify explain.c and multiply the
hit_ratio by 100 and use "%" as the unit in ExplainPropertyFloat().
Just looking at the raw number of "1.00" in the expected output, it
isn't obvious if the planner expects every lookup to be a cache hit or
just 1% of them.

Also, to get something commitable, you'll also need to modify
explain_memoize() at the top of memoize.sql and add handling to mask
out the value of these new properties. These are not going to be
anywhere near stable enough across platforms to have these shown in
the expected output files.

David