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

On 06.07.2023 11:27, Lukas Fittl wrote:
> On Thu, Jul 6, 2023 at 12:56 AM Daniel Gustafsson <daniel(at)yesql(dot)se> wrote:
>
> Lukas: do you have an updated patch for this commitfest to address
> David's
> comments?
>
>
> I have a draft - I should be able to post an updated patch in the next
> days. Thanks for checking!
>
> Thanks,
> Lukas
>
> --
> Lukas Fittl

Hi hackers,

While debugging a query execution plan involving Memoize, it'd be nice
to determine how many unique keys would fit into the cache. The
est_entries value provides some insight, but without knowing ndistinct,
it is unclear whether the cache is large enough to hold all unique keys
or if some will be evicted.

Given its potential usefulness, I would like to work for this. I
attached v2 patch with changes.

Example from memoize.sql

EXPLAIN SELECT COUNT(*),AVG(t1.unique1) FROM tenk1 t1
INNER JOIN tenk1 t2 ON t1.unique1 = t2.thousand
WHERE t2.unique1 < 1200;
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Aggregate  (cost=815.12..815.13 rows=1 width=40)
   ->  Nested Loop  (cost=0.30..809.12 rows=1200 width=4)
         ->  Seq Scan on tenk1 t2  (cost=0.00..470.00 rows=1200 width=4)
               Filter: (unique1 < 1200)
         ->  Memoize  (cost=0.30..0.41 rows=1 width=4)
               Cache Key: t2.thousand
               Cache Mode: logical
               Cache Estimated Entries: 655
               Cache Estimated NDistinct: 721
               ->  Index Only Scan using tenk1_unique1 on tenk1 t1 
(cost=0.29..0.40 rows=1 width=4)
                     Index Cond: (unique1 = t2.thousand)
(11 rows)

Additionally, since this information would only be shown in EXPLAIN when
costs are enabled, it should not cause any performance regression in
normal execution. However, reviewers should be especially careful when
verifying test outputs, as this change could affect plan details in
regression tests.

Any thoughts?

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