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

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.