Thread

Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment

Ilia Evdokimov <ilya.evdokimov@tantorlabs.com> — 2025-04-01T07:52:52Z

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.