Thread

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

Ilia Evdokimov <ilya.evdokimov@tantorlabs.com> — 2025-04-27T19:58:52Z

Sorry for the delayed reply.


On 18.04.2025 00:29, David Rowley wrote:
> I know I did suggest that the hit_ratio should be a percent and it
> should be multiplied by 100.0.  This seems fine for the text format as
> you can have the % unit there. However, looking at
> ExplainPropertyFloat(), we don't print the unit for non-text formats.
> I wonder if printing a number between 0 and 100 there will be
> confusing. I don't believe we have anything else in EXPLAIN that shows
> a percentage. I don't quite know what to do about this. One thought I
> had was to only * 100 for text format, but that might be more
> confusing.


I agree that displaying the percentage instead of the fraction is a 
better choice, because it is much more convenient for analysis. If this 
value were intended to be passed into some API for further computations, 
it would make sense to keep it as a fraction to avoid unnecessary 
multiplications and divisions by 100. However, such use cases seem 
extremely unlikely.

Therefore, I think it is better to report percentages directly. Since 
non-text EXPLAIN formats do not display units, I propose to rename the 
field to "hit_percent" in all formats, including the text format. This 
way, the meaning of the value remains clear without needing additional 
context. What do you think about this approach?


>
> Aside from that, I'd prefer the new fields in struct Memoize to be
> prefixed with "est_" the same as the existing "est_entries" field. I'm
> unsure why MemoizePath.calls becomes Memoize.lookups. Seems
> unnecessary and just means more brain space being used to maintain a
> mental model.


Agree.

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