Message-ID: <580fe72c-85be-45a8-9e00-6b5417690557@tantorlabs.com>
Date: Tue, 1 Apr 2025 10:52:52 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost
 adjustment
From: Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>
To: Robert Haas <robertmhaas@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>,
 David Rowley <dgrowleyml@gmail.com>
Cc: 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>
References: <CAP53Pky29GWAVVk3oBgKBDqhND0BRBN6yTPeguV_qSivFL5N_g@mail.gmail.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>
 <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>
Content-Language: en-US
In-Reply-To: <67b5c84a-dedd-4103-af7e-0117046102fe@tantorlabs.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/580fe72c-85be-45a8-9e00-6b5417690557%40tantorlabs.com>
Precedence: bulk

On 28.03.2025 15:20, Ilia Evdokimov wrote:

> Then we need to decide clearly what exactly to display in EXPLAIN for 
> the Memoize node: absolute values (estimated distinct keys and 
> estimated cache capacity) or ratios (hit_ratio and evict_ratio). 
> Ratios have the advantage of quickly reflecting the overall 
> effectiveness of Memoize. However, absolute values have a significant 
> advantage as they explicitly reveal the reason of Memoize's poor 
> performance, making problem diagnosis simpler.
>
> With absolute values, users can directly understand the underlying 
> reason for poor performance. For example: insufficient memory 
> (capacity < distinct keys), inaccurate planner statistics (distinct 
> keys significantly different from actual values), poorly ordered keys 
> (capacity ~ distinct keys, but frequent evictions as seen in the 
> Evictions parameter), or Memoize simply not being beneficial (capacity 
> ~ distinct keys ~ calls). Ratios, by contrast, only reflect the final 
> outcome without clearly indicating the cause or the specific steps 
> needed to resolve the issue.
>
> Thus, absolute values do more than just inform users that a problem 
> exists; they provide actionable details that enable users to directly 
> address the problem (increase work_mem, refresh statistics, create 
> extended statistics, or disable Memoize entirely). Additionally, no 
> other plan nodes in PostgreSQL currently use a similar ratio-based 
> approach - everywhere else absolute values are consistently shown 
> (e.g., number of rows, buckets, batches, memory used, etc.). Using 
> absolute values in Memoize maintains consistency with existing practice.
>
> I've updated the patch to v5, since the new parameter est_unique_keys 
> in make_memoize() is now placed near est_entries, which is more 
> logical and readable than putting it at the end.
>
> Any thoughts?
>
> -- 
> Best Regards,
> Ilia Evdokimov,
> Tantor Labs LLC.


With the feature freeze coming up soon, I’d like to ask: do we plan to 
include this patch in v18?

Please let me know if there’s anything I can do to help move it forward.

--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC.