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

On 15.04.2025 03:23, David Rowley wrote:
> I'm just looking for ways to allow us to group all three of these
> together for the TEXT format type. I see the BUFFERS are displayed
> quite compactly, e.g. "Buffers: shared hit=17 read=4".
> show_wal_usage() does something similar and so does
> show_modifytable_info() for MERGE.
>
> Maybe we could compress the text output a bit with:
>
> "Estimates: capacity=N distinct keys=N hit ratio=N.N%"
>
> If we get it that compact, maybe lookups could fit in there too, i.e.:
>
> "Estimates: capacity=N distinct keys=N lookups=N hit ratio=N.N%"
>
> I'd also like to vote that you modify explain.c and multiply the
> hit_ratio by 100 and use "%" as the unit in ExplainPropertyFloat().
> Just looking at the raw number of "1.00" in the expected output, it
> isn't obvious if the planner expects every lookup to be a cache hit or
> just 1% of them.

+1. I'll attach three patches with 'capacity=N distinct keys=N' output,
'lookups=N' and 'ratio=N.N%'

>
> Also, to get something commitable, you'll also need to modify
> explain_memoize() at the top of memoize.sql and add handling to mask
> out the value of these new properties. These are not going to be
> anywhere near stable enough across platforms to have these shown in
> the expected output files.
>
> David

There are other regression tests - such as create_index, subselect,
join, and partition_join - that also use Memoize nodes. These tests
currently don't have any masking in place, so the new output fields may
cause instability across platforms.

Wrapping the line in costs or verbose would help with test stability,
but doing that only to simplify test output doesn't look like the right
reason. Maybe it makes more sense to mask these values in other tests
that use Memoize too. What do you think?

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