public inbox for [email protected]  

help / color / mirror / Atom feed

From: Ilia Evdokimov <[email protected]>
To: Robert Haas <[email protected]>
To: Tom Lane <[email protected]>
To: David Rowley <[email protected]>
Cc: Lukas Fittl <[email protected]>
Cc: Daniel Gustafsson <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: Nikolay Samokhvalov <[email protected]>
Cc: Andrei Lepikhov <[email protected]>
Subject: Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment
Date: Tue, 1 Apr 2025 10:52:52 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <CAP53Pky29GWAVVk3oBgKBDqhND0BRBN6yTPeguV_qSivFL5N_g@mail.gmail.com>
	<CAApHDvpq2gOt3gnzkd4Ud=wrT7YRGrF3zb29jgWzDsYEhETrOA@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<CAApHDvqHe15LL=K5j71qxLdPCaFFPeohtBe7=sK29Ord3ZVQcg@mail.gmail.com>
	<[email protected]>
	<CAApHDvoE5S5FkvEq+N3-J9LfaVUpWLOnczOYAOEvBMCY20=pdg@mail.gmail.com>
	<[email protected]>
	<CAApHDvr9kjuo0rX03Qm0cDP_tFNJ8crOtpRzbyS0Zwtg2BorRw@mail.gmail.com>
	<[email protected]>
	<CAP53Pky44MuEYPDFcqR+ogffnifbuXkAWi5TvjhHmpSdj88o8w@mail.gmail.com>
	<[email protected]>
	<CA+TgmoZ8qXiZmmn4P9Mk1cf2mjMMLFPOjSasCjuKSiHFcm-ncw@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<[email protected]>

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.

view thread (29+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox