MIME-Version: 1.0
References: 
 <CAP53Pky29GWAVVk3oBgKBDqhND0BRBN6yTPeguV_qSivFL5N_g@mail.gmail.com>
 <CAApHDvrBctjUFjY77BFd_MqPD+yf4nOZSKzbdzP70XntkknY8A@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>
In-Reply-To: <1161645.1742854534@sss.pgh.pa.us>
From: Lukas Fittl <lukas@fittl.com>
Date: Mon, 24 Mar 2025 15:30:03 -0700
Message-ID: 
 <CAP53Pky44MuEYPDFcqR+ogffnifbuXkAWi5TvjhHmpSdj88o8w@mail.gmail.com>
Subject: Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost
 adjustment
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: David Rowley <dgrowleyml@gmail.com>, Andrei Lepikhov <lepihov@gmail.com>,
	Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>,
 Daniel Gustafsson <daniel@yesql.se>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
	Nikolay Samokhvalov <samokhvalov@gmail.com>
Content-Type: multipart/alternative; boundary="0000000000005fb6c406311e278c"
Archived-At: 
 <https://www.postgresql.org/message-id/CAP53Pky44MuEYPDFcqR%2BogffnifbuXkAWi5TvjhHmpSdj88o8w%40mail.gmail.com>
Precedence: bulk

--0000000000005fb6c406311e278c
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Mon, Mar 24, 2025 at 3:15=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> FWIW, I share these doubts about whether these values are useful
> enough to include in the default EXPLAIN output.  My main beef
> with them though is that they are basically numbers derived along
> the way to producing a cost estimate, and I don't think we break
> out such intermediate results for other node types.
>

The main argument I had initially when proposing this, is that Memoize is
different from other plan nodes, in that it makes the child node costs
"cheaper". Clearly seeing the expected cache hit/ratio (that drives that
costing modification) helps interpret why the planner came up with a given
plan.

Put differently, the reason this should be in the default EXPLAIN output
(or at least the VERBOSE output), is because Memoize's impact on costing is
counterintuitive (in my experience), and breaks the user's understanding of
planner costs you can usually derive by looking at the per-node cost
details, which typically flows up (i.e. gets larger as you step higher up
in the tree).


>
> It's looking like Robert's "pg_overexplain" will hit the tree soon,
> so maybe there could be a case for teaching that to emit additional
> costing details such as these?
>

I don't think that addresses the end-user usability sufficiently - from
what I gathered "pg_overexplain" was meant for a hacker audience, not
DBAs/etc interpreting Postgres plan choices.

Thanks,
Lukas

--=20
Lukas Fittl

--0000000000005fb6c406311e278c
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Mon, Mar 24, 2025 at 3:15=E2=80=AFPM T=
om Lane &lt;<a href=3D"mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; =
wrote:</div><div class=3D"gmail_quote gmail_quote_container"><blockquote cl=
ass=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid=
 rgb(204,204,204);padding-left:1ex">FWIW, I share these doubts about whethe=
r these values are useful<br>
enough to include in the default EXPLAIN output.=C2=A0 My main beef<br>
with them though is that they are basically numbers derived along<br>
the way to producing a cost estimate, and I don&#39;t think we break<br>
out such intermediate results for other node types.<br></blockquote><div><b=
r></div><div>The main argument I had initially when proposing this, is that=
 Memoize is different from other plan nodes, in that it makes the child nod=
e costs &quot;cheaper&quot;. Clearly seeing the expected cache hit/ratio (t=
hat drives that costing modification) helps interpret why the planner came =
up with a given plan.</div><div><br></div><div>Put differently, the reason =
this should be in the default EXPLAIN output (or at least the VERBOSE outpu=
t), is because Memoize&#39;s impact on costing is counterintuitive (in my e=
xperience), and breaks the user&#39;s understanding of planner costs you ca=
n usually derive by looking at the per-node cost details, which typically f=
lows up (i.e. gets larger as you step higher up in the tree).</div><div>=C2=
=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8e=
x;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<br>
It&#39;s looking like Robert&#39;s &quot;pg_overexplain&quot; will hit the =
tree soon,<br>
so maybe there could be a case for teaching that to emit additional<br>
costing details such as these?<br></blockquote><div><br></div><div>I don=
9;t think that addresses the end-user usability sufficiently - from what I =
gathered &quot;pg_overexplain&quot; was meant for a hacker audience, not DB=
As/etc interpreting Postgres plan choices.</div></div><div class=3D"gmail_q=
uote gmail_quote_container"><br></div><div class=3D"gmail_quote gmail_quote=
_container">Thanks,</div><div class=3D"gmail_quote gmail_quote_container">L=
ukas</div><div class=3D"gmail_quote gmail_quote_container"><br></div><span =
class=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gm=
ail_signature"><div dir=3D"ltr"><div><div dir=3D"ltr"><div><div dir=3D"ltr"=
>Lukas Fittl</div></div></div></div></div></div></div>

--0000000000005fb6c406311e278c--