From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rowley <dgrowleyml@gmail.com>
cc: Andrei Lepikhov <lepihov@gmail.com>,
        Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>,
        Lukas Fittl <lukas@fittl.com>, Daniel Gustafsson <daniel@yesql.se>,
        PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
        Nikolay Samokhvalov <samokhvalov@gmail.com>
Subject: Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost
 adjustment
In-reply-to: 
 <CAApHDvr9kjuo0rX03Qm0cDP_tFNJ8crOtpRzbyS0Zwtg2BorRw@mail.gmail.com>
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>
Comments: In-reply-to David Rowley <dgrowleyml@gmail.com>
	message dated "Tue, 25 Mar 2025 11:05:16 +1300"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1161644.1742854534.1@sss.pgh.pa.us>
Date: Mon, 24 Mar 2025 18:15:34 -0400
Message-ID: <1161645.1742854534@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1161645.1742854534%40sss.pgh.pa.us>
Precedence: bulk

David Rowley <dgrowleyml@gmail.com> writes:
> I'm not following what the -1 is for. Is it for showing both hit and
> evict ratios?  And your vote is only for adding hit ratio?

> Just to make it clear, the evict ratio isn't redundant because we show
> hit ratio. If you have 1000 calls and 1000 distinct values and enough
> memory to store those, then that's a 0% hit ratio since the first
> lookup is a miss. If you change the calls to 2000 then that's a 50%
> hit ratio and still 0% evict.

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.

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'd kind of like to see a larger
scope than just Memoize for such an addition, though I'm not sure
exactly which other intermediate estimates are worth showing.

			regards, tom lane