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>
In-Reply-To: <ff747c1d-1925-45ca-9c47-2763be45cba0@gmail.com>
From: David Rowley <dgrowleyml@gmail.com>
Date: Mon, 24 Mar 2025 10:16:18 +1300
Message-ID: 
 <CAApHDvoE5S5FkvEq+N3-J9LfaVUpWLOnczOYAOEvBMCY20=pdg@mail.gmail.com>
Subject: Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost
 adjustment
To: Andrei Lepikhov <lepihov@gmail.com>
Cc: Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>,
 Lukas Fittl <lukas@fittl.com>,
	Daniel Gustafsson <daniel@yesql.se>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAApHDvoE5S5FkvEq%2BN3-J9LfaVUpWLOnczOYAOEvBMCY20%3Dpdg%40mail.gmail.com>
Precedence: bulk

On Fri, 21 Mar 2025 at 22:02, Andrei Lepikhov <lepihov@gmail.com> wrote:
> In cases
> I have seen before, the main question is how effective was (or maybe) a
> Memoize node == how often the incoming key fits the cache. In that case,
> the hit ratio fraction is more understandable for a broad audience.
> That's why according to my experience in case of a good cache
> reusability factor, users are usually okay with increasing the cache
> size to the necessary numbers and avoiding evictions at all costs. So,
> the predicted evict_ratio also tells us about incrementing work_mem to
> enhance the chances of Memoisation.

Can you explain why "Estimated Capacity" and "Estimated Distinct
Lookup Keys" don't answer that?  If there are more distinct lookup
keys than there is capacity to store them, then some will be evicted.

Once again, I'm not necessarily objecting to hit and evict ratios
being shown, I just want to know they're actually useful enough to
show and don't just bloat the EXPLAIN output needlessly. So far your
arguments aren't convincing me that they are.

> Having written the last sentence I came back to the point why work_mem
> is so universal and is used at each node as a criteria of memory
> allocation size? But it is a different story, I think.

We have to set the limit somehow.  We could have done this by having a
GUC per node type that uses memory, but it looks like something more
universal was decided, perhaps to save on GUCs. I don't know the exact
history, but once upon a time, sort_mem existed. Perhaps that
disappeared because we grew more node types that needed to allocate
large, otherwise unbounded amounts of memory.  We did more recently
grow a hash_mem_multiplier GUC, so it's not true to say that work_mem
solely controls the limits of each node's memory allocation sizes.

David