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

From: David Rowley <[email-masked]>
Date: Wed, 2 Apr 2025 10:06:59 +1300

On Tue, 1 Apr 2025 at 20:52, Ilia Evdokimov
<[email-masked]> wrote:
> With the feature freeze coming up soon, I’d like to ask: do we plan to
> include this patch in v18?

I don't think there's a clear enough consensus yet on what EXPLAIN
should display. We'd need that beforehand. There are now less than 7
days to get that, so it might be unrealistic.

I tried to move things along to address Tom's concern about not
wanting to show this in EXPLAIN's standard output. I suggested in [1]
that we could use EXPLAIN ANALYZE, but nobody commented on that.
EXPLAIN ANALYZE is much more verbose than EXPLAIN already, and if we
put these in EXPLAIN ANALYZE then the viewer can more easily compare
planned vs actual. I had mentioned that Hash Join does something like
this for buckets.

David

[1] https://postgr.es/m/CAApHDvqPkWmz1Lq23c9CD+mAW=[email-masked]

---

From: Ilia Evdokimov <[email-masked]>
Date: Wed, 2 Apr 2025 12:35:33 +0300

On 02.04.2025 00:06, David Rowley wrote:
> [1]https://postgr.es/m/CAApHDvqPkWmz1Lq23c9CD+mAW=[email-masked]

Apologies for not addressing your earlier suggestion properly. After 
reconsidering, I agree that showing ndistinct and est_entries in EXPLAIN 
ANALYZE when there are actual cache misses is the best approach. This is 
typically when users notice performance regressions and start 
investigating the cause, so surfacing planner expectations at that point 
can be the most useful. I attached v6 patch with changes.

--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC.
From 36ed866e3d28a709fd1133a89ecdc7a2654a657f Mon Sep 17 00:00:00 2001
From: Ilia Evdokimov <[email-masked]>
Date: Wed, 2 Apr 2025 10:49:27 +0300
Subject: [PATCH v6] Display estimated distinct keys and cache capacity in
 EXPLAIN ANALYZE for Memoize when cache misses occur

---
 src/backend/commands/explain.c          | 13 +++++++++++++
 src/backend/optimizer/path/costsize.c   |  3 +++
 src/backend/optimizer/plan/createplan.c | 10 +++++++---
 src/backend/optimizer/util/pathnode.c   |  3 +++
 src/include/nodes/pathnodes.h           |  2 ++
 src/include/nodes/plannodes.h           |  6 ++++++
 6 files changed, 34 insertions(+), 3 deletions(-)

diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index ef8aa489af8..f21de58e0a0 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -3635,6 +3635,19 @@ show_memoize_info(MemoizeState *mstate, List *ancestors, ExplainState *es)
 
 	if (mstate->stats.cache_misses > 0)
 	{
+		if (es->format == EXPLAIN_FORMAT_TEXT)
+		{
+			ExplainIndentText(es);
+			appendStringInfo(es->str, "Estimated Capacity: %u  Estimated Distinct Lookup Keys: %0.0f\n",
+							((Memoize *) plan)->est_entries,
+							((Memoize *) plan)->est_unique_keys);
+		}
+		else
+		{
+			ExplainPropertyUInteger("Estimated Capacity", "", ((Memoize *) plan)->est_entries, es);
+			ExplainPropertyFloat("Estimated Distinct Lookup Keys", "", ((Memoize *) plan)->est_unique_keys, 0, es);
+		}
+
 		/*
 		 * mem_peak is only set when we freed memory, so we must use mem_used
 		 * when mem_peak is 0.
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index f6f77b8fe19..e3abf9ccc26 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -2604,6 +2604,9 @@ cost_memoize_rescan(PlannerInfo *root, MemoizePath *mpath,
 	mpath->est_entries = Min(Min(ndistinct, est_cache_entries),
 							 PG_UINT32_MAX);
 
+	/* Remember ndistinct for a potential EXPLAIN later */
+	mpath->est_unique_keys = ndistinct;
+
 	/*
 	 * When the number of distinct parameter values is above the amount we can
 	 * store in the cache, then we'll have to evict some entries from the
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 75e2b0b9036..38882501484 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -284,7 +284,8 @@ static Material *make_material(Plan *lefttree);
 static Memoize *make_memoize(Plan *lefttree, Oid *hashoperators,
 							 Oid *collations, List *param_exprs,
 							 bool singlerow, bool binary_mode,
-							 uint32 est_entries, Bitmapset *keyparamids);
+							 uint32 est_entries, double est_unique_keys,
+							 Bitmapset *keyparamids);
 static WindowAgg *make_windowagg(List *tlist, WindowClause *wc,
 								 int partNumCols, AttrNumber *partColIdx, Oid *partOperators, Oid *partCollations,
 								 int ordNumCols, AttrNumber *ordColIdx, Oid *ordOperators, Oid *ordCollations,
@@ -1703,7 +1704,8 @@ create_memoize_plan(PlannerInfo *root, MemoizePath *best_path, int flags)
 
 	plan = make_memoize(subplan, operators, collations, param_exprs,
 						best_path->singlerow, best_path->binary_mode,
-						best_path->est_entries, keyparamids);
+						best_path->est_entries, best_path->est_unique_keys,
+						keyparamids);
 
 	copy_generic_path_info(&plan->plan, (Path *) best_path);
 
@@ -6636,7 +6638,8 @@ materialize_finished_plan(Plan *subplan)
 static Memoize *
 make_memoize(Plan *lefttree, Oid *hashoperators, Oid *collations,
 			 List *param_exprs, bool singlerow, bool binary_mode,
-			 uint32 est_entries, Bitmapset *keyparamids)
+			uint32 est_entries, double est_unique_keys,
+			Bitmapset *keyparamids)
 {
 	Memoize    *node = makeNode(Memoize);
 	Plan	   *plan = &node->plan;
@@ -6654,6 +6657,7 @@ make_memoize(Plan *lefttree, Oid *hashoperators, Oid *collations,
 	node->binary_mode = binary_mode;
 	node->est_entries = est_entries;
 	node->keyparamids = keyparamids;
+	node->est_unique_keys = est_unique_keys;
 
 	return node;
 }
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index 93e73cb44db..1fbcda99067 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -1701,6 +1701,9 @@ create_memoize_path(PlannerInfo *root, RelOptInfo *rel, Path *subpath,
 	Assert(enable_memoize);
 	pathnode->path.disabled_nodes = subpath->disabled_nodes;
 
+	/* Estimated number of distinct memoization keys, computed using estimate_num_groups() */
+	pathnode->est_unique_keys = 0;
+
 	/*
 	 * Add a small additional charge for caching the first entry.  All the
 	 * harder calculations for rescans are performed in cost_memoize_rescan().
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index c24a1fc8514..0946ccea994 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -2040,6 +2040,8 @@ typedef struct MemoizePath
 	uint32		est_entries;	/* The maximum number of entries that the
 								 * planner expects will fit in the cache, or 0
 								 * if unknown */
+	double		est_unique_keys;		/* Estimated number of distinct memoization keys,
+								 * used for cache size evaluation. Kept for EXPLAIN */
 } MemoizePath;
 
 /*
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 658d76225e4..3d9d3a1159d 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -1063,6 +1063,12 @@ typedef struct Memoize
 
 	/* paramids from param_exprs */
 	Bitmapset  *keyparamids;
+
+	/*
+	 * Estimated number of distinct memoization keys,
+	 * used for cache size evaluation. Kept for EXPLAIN
+	 */
+	double		est_unique_keys;
 } Memoize;
 
 /* ----------------
-- 
2.34.1

---

From: Ilia Evdokimov <[email-masked]>
Date: Mon, 14 Apr 2025 12:02:41 +0300

As there seems to be general support for the idea behind this patch, and 
no one has raised strong objections recently, but also no clear 
consensus on the exact final shape of the output has been reached yet - 
and I understand other priorities might be taking precedence right now - 
I’ve registered the patch in the upcoming CommitFest 2025-07 to ensure 
it stays in the review process.

https://commitfest.postgresql.org/patch/5697/

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

---

From: Robert Haas <[email-masked]>
Date: Mon, 14 Apr 2025 12:31:22 -0400

> planned vs actual. I had mentioned that Hash Join does something like

I don't think we should use ANALYZE for this because, IME, that should
just be about whether the query gets executed. Since this looks like
information that is available at plan time, I think it should be
displayed all the time or made contingent on VERBOSE. It would be sad
if you had to run the query to get information that was available
without needing to run the query. Personally, I think we can probably
just display it all the time. I mean, the typical plan is not going to
contain enough Memoize nodes that a little extra chatter for each one
impedes readability significantly.

As far as what to display, I have sympathy with Lukas's initial
complaint that it was hard to understand the costing of Memoize. I
haven't faced this exact problem, I think because Memoize isn't that
often used in plans I have seen, but I've faced a lot of similar
problems where you have to try to work backwards painfully to figure
out the chain of events that led to the value you're actually seeing,
and I'm +1 for efforts to make it more clear.

Having looked at v6, I think it would help, at least if the reader is
sufficiently knowledgeable. From the values displayed, it looks like
someone could reconstruct the evict_ratio value in
cost_memoize_rescan(), and if they know the loop count, also the
hit_ratio. But that seems hard: if you weren't reading the code, how
would you know how to do it? Even if you are reading the code, are you
sure you'd reconstruct it correctly? I wonder why we think it's better
to display this than a more "cooked" number like the estimated hit
ratio that was proposed in v1?

-- 
Robert Haas
EDB: http://www.enterprisedb.com

---

From: Andrei Lepikhov <[email-masked]>
Date: Mon, 14 Apr 2025 21:49:23 +0200

> ratio that was proposed in v1?
+1.
My reasons: a) it more "physical"; b) We don't need to look for the 
corresponding NestLoop JOIN node - my explans usually contain 150+ lines 
and it is hard to get into the analysis something out of current screen.

-- 
regards, Andrei Lepikhov

---

From: David Rowley <[email-masked]>
Date: Tue, 15 Apr 2025 08:53:05 +1200

> impedes readability significantly.

You might be right there. I just offered that as a possible solution
to the concern of bloating the EXPLAIN output.

> ratio that was proposed in v1?

I think the problem with only showing the estimated hit ratio is that
it's difficult to then know what you might change to improve that in
cases where you might have other join types disabled and are
experimenting to figure out why Nested Loop / Memoize isn't being
chosen.  If you see the estimated capacity isn't big enough to store
all the rows for all distinct keys then you know you can increase
work_mem to assist. If the Estimated Unique Keys is too high, then you
might be able to adjust some ndistinct estimates or increase stats
targets somewhere to improve that.  If we only showed the estimated
hit ratio, then you wouldn't know where to start to get the planner to
pick the Memoize plan.

As I mentioned in [1], I wouldn't object to having the estimated hit
ratio shown in addition to the components that are used to calculate
it. Per [2], Andrei didn't seem to like the idea due to the
duplication. FWIW, I do agree with you that the hit ratio isn't
exactly easy to calculate on the fly when looking at the input numbers
that are used to calculate it. I just wasn't 100% certain that it
would be needed in its calculated form. Maybe it depends on the reason
you're looking at EXPLAIN. Maybe the reason I mentioned above for
looking at EXPLAIN is just one of many and I'm not thinking hard
enough to consider the other ones.

If we can't get consensus for everything people want to add at once
then maybe the patch could be broken into two, with 0001 being pretty
much the v4 patch and then have 0002 add the Estimated Hit Ratio.
Having the physical patches and being able to try it out or view the
regression test changes might lower the bar on people chipping in with
their views.

David

[1] https://postgr.es/m/CAApHDvoE5S5FkvEq+N3-J9LfaVUpWLOnczOYAOEvBMCY20=[email-masked]
[2] https://postgr.es/m/[email-masked]

---

From: Ilia Evdokimov <[email-masked]>
Date: Tue, 15 Apr 2025 02:14:37 +0300

> regression test changes might lower the bar on people chipping in with

Agreed - I’ll split the patch into two: one adds Estimated Capacity 
and Estimated Distinct Keys, and the second adds Estimated Hit Ratio. 
I’ll also add regression test differences to make it easier to evaluate 
the usefulness of each part.

--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC.
From 99a23dfae4415a3eb88c3847deefae3c2103077d Mon Sep 17 00:00:00 2001
From: Evdokimov Ilia <[email-masked]>
Date: Tue, 15 Apr 2025 00:47:26 +0300
Subject: [PATCH v7 1/2] Show ndistinct and est_entries in EXPLAIN for Memoize

---
 src/backend/commands/explain.c               | 13 ++++
 src/backend/optimizer/path/costsize.c        |  3 +
 src/backend/optimizer/plan/createplan.c      | 10 ++-
 src/backend/optimizer/util/pathnode.c        |  3 +
 src/include/nodes/pathnodes.h                |  2 +
 src/include/nodes/plannodes.h                |  6 ++
 src/test/regress/expected/create_index.out   |  3 +-
 src/test/regress/expected/join.out           | 64 +++++++++++---------
 src/test/regress/expected/memoize.out        | 54 +++++++++++------
 src/test/regress/expected/partition_join.out | 18 ++++--
 src/test/regress/expected/subselect.out      | 10 +--
 11 files changed, 126 insertions(+), 60 deletions(-)

diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 786ee865f14..0a4ce5ee7d7 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -3628,6 +3628,19 @@ show_memoize_info(MemoizeState *mstate, List *ancestors, ExplainState *es)
 	ExplainPropertyText("Cache Key", keystr.data, es);
 	ExplainPropertyText("Cache Mode", mstate->binary_mode ? "binary" : "logical", es);
 
 	pfree(keystr.data);
 
 	if (!es->analyze)
index 60b0fcfb6be..f72319d903c 100644
 
index a8f22a8c154..a1456c9014d 100644
+							uint32 est_entries, Bitmapset *keyparamids,
+							double est_unique_keys);
 
 	plan = make_memoize(subplan, operators, collations, param_exprs,
 						best_path->singlerow, best_path->binary_mode,
-						best_path->est_entries, keyparamids);
+						best_path->est_entries, keyparamids,
+						best_path->est_unique_keys);

+			uint32 est_entries, Bitmapset *keyparamids,
+			double est_unique_keys)
 
 	return node;
 }
 
index bb678bdcdcd..07d97dc0b5b 100644
@@ -2138,6 +2138,8 @@ typedef struct MemoizePath
 
 /*

 /* ----------------
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index 9ade7b835e6..826018a8a9f 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -2245,10 +2245,11 @@ SELECT count(*) FROM tenk1 LEFT JOIN tenk2 ON
          ->  Memoize
                Cache Key: tenk1.hundred
                Cache Mode: logical
+               Estimated Capacity: 100  Estimated Distinct Lookup Keys: 100
                ->  Index Scan using tenk2_hundred on tenk2
                      Index Cond: (hundred = tenk1.hundred)
                      Filter: ((thousand = 42) OR (thousand = 41) OR (tenthous = 2))
-(10 rows)
+(11 rows)
 
 --
 -- Check behavior with duplicate index column contents
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 14da5708451..eca6ce2c6f0 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -2729,8 +2729,8 @@ select * from onek t1
     left join lateral
       (select * from onek t3 where t3.two = t2.two offset 0) s
       on t2.unique1 = 1;
-                    QUERY PLAN                    
---------------------------------------------------
+                                  QUERY PLAN                                  
+------------------------------------------------------------------------------
  Nested Loop Left Join
    ->  Seq Scan on onek t1
    ->  Materialize
@@ -2740,9 +2740,10 @@ select * from onek t1
                ->  Memoize
                      Cache Key: t2.two
                      Cache Mode: binary
+                     Estimated Capacity: 2  Estimated Distinct Lookup Keys: 2
                      ->  Seq Scan on onek t3
                            Filter: (two = t2.two)
-(11 rows)
+(12 rows)
 
 --
 -- check a case where we formerly got confused by conflicting sort orders
@@ -5128,8 +5129,8 @@ select * from
   on i8.q2 = 123,
   lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss
 where t1.f1 = ss.f1;
-                    QUERY PLAN                    
---------------------------------------------------
+                            QUERY PLAN                            
+------------------------------------------------------------------
  Nested Loop
    Output: t1.f1, i8.q1, i8.q2, (i8.q1), t2.f1
    Join Filter: (t1.f1 = t2.f1)
@@ -5146,11 +5147,12 @@ where t1.f1 = ss.f1;
          Output: (i8.q1), t2.f1
          Cache Key: i8.q1
          Cache Mode: binary
+         Estimated Capacity: 1  Estimated Distinct Lookup Keys: 1
          ->  Limit
                Output: (i8.q1), t2.f1
                ->  Seq Scan on public.text_tbl t2
                      Output: i8.q1, t2.f1
-(20 rows)
+(21 rows)
 
 select * from
   text_tbl t1
@@ -5171,8 +5173,8 @@ select * from
   lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss1,
   lateral (select ss1.* from text_tbl t3 limit 1) as ss2
 where t1.f1 = ss2.f1;
-                            QUERY PLAN                             
--------------------------------------------------------------------
+                               QUERY PLAN                               
+------------------------------------------------------------------------
  Nested Loop
    Output: t1.f1, i8.q1, i8.q2, (i8.q1), t2.f1, ((i8.q1)), (t2.f1)
    Join Filter: (t1.f1 = (t2.f1))
@@ -5191,6 +5193,7 @@ where t1.f1 = ss2.f1;
                Output: (i8.q1), t2.f1
                Cache Key: i8.q1
                Cache Mode: binary
+               Estimated Capacity: 1  Estimated Distinct Lookup Keys: 1
                ->  Limit
                      Output: (i8.q1), t2.f1
                      ->  Seq Scan on public.text_tbl t2
@@ -5199,11 +5202,12 @@ where t1.f1 = ss2.f1;
          Output: ((i8.q1)), (t2.f1)
          Cache Key: (i8.q1), t2.f1
          Cache Mode: binary
+         Estimated Capacity: 1  Estimated Distinct Lookup Keys: 1
          ->  Limit
                Output: ((i8.q1)), (t2.f1)
                ->  Seq Scan on public.text_tbl t3
                      Output: (i8.q1), t2.f1
-(30 rows)
+(32 rows)
 
 select * from
   text_tbl t1
@@ -5225,8 +5229,8 @@ select 1 from
   left join text_tbl as tt4 on (tt3.f1 = tt4.f1),
   lateral (select tt4.f1 as c0 from text_tbl as tt5 limit 1) as ss1
 where tt1.f1 = ss1.c0;
-                        QUERY PLAN                        
-----------------------------------------------------------
+                            QUERY PLAN                            
+------------------------------------------------------------------
  Nested Loop
    Output: 1
    ->  Nested Loop Left Join
@@ -5252,6 +5256,7 @@ where tt1.f1 = ss1.c0;
          Output: ss1.c0
          Cache Key: tt4.f1
          Cache Mode: binary
+         Estimated Capacity: 1  Estimated Distinct Lookup Keys: 1
          ->  Subquery Scan on ss1
                Output: ss1.c0
                Filter: (ss1.c0 = 'foo'::text)
@@ -5259,7 +5264,7 @@ where tt1.f1 = ss1.c0;
                      Output: (tt4.f1)
                      ->  Seq Scan on public.text_tbl tt5
                            Output: tt4.f1
-(32 rows)
+(33 rows)
 
 select 1 from
   text_tbl as tt1
@@ -6462,17 +6467,18 @@ select * from sj t1
     join lateral
       (select * from sj tablesample system(t1.b)) s
     on t1.a = s.a;
-              QUERY PLAN               
----------------------------------------
+                            QUERY PLAN                            
+------------------------------------------------------------------
  Nested Loop
    ->  Seq Scan on sj t1
    ->  Memoize
          Cache Key: t1.a, t1.b
          Cache Mode: binary
+         Estimated Capacity: 2  Estimated Distinct Lookup Keys: 2
          ->  Sample Scan on sj
                Sampling: system (t1.b)
                Filter: (t1.a = a)
-(8 rows)
+(9 rows)
 
 -- Ensure that SJE does not form a self-referential lateral dependency
 explain (costs off)
@@ -7614,43 +7620,46 @@ select count(*) from tenk1 a, lateral generate_series(1,two) g;
 
 explain (costs off)
   select count(*) from tenk1 a, lateral generate_series(1,two) g;
-                      QUERY PLAN                      
-------------------------------------------------------
+                               QUERY PLAN                               
+------------------------------------------------------------------------
  Aggregate
    ->  Nested Loop
          ->  Seq Scan on tenk1 a
          ->  Memoize
                Cache Key: a.two
                Cache Mode: binary
+               Estimated Capacity: 2  Estimated Distinct Lookup Keys: 2
                ->  Function Scan on generate_series g
-(7 rows)
+(8 rows)
 
 explain (costs off)
   select count(*) from tenk1 a cross join lateral generate_series(1,two) g;
-                      QUERY PLAN                      
-------------------------------------------------------
+                               QUERY PLAN                               
+------------------------------------------------------------------------
  Aggregate
    ->  Nested Loop
          ->  Seq Scan on tenk1 a
          ->  Memoize
                Cache Key: a.two
                Cache Mode: binary
+               Estimated Capacity: 2  Estimated Distinct Lookup Keys: 2
                ->  Function Scan on generate_series g
-(7 rows)
+(8 rows)
 
 -- don't need the explicit LATERAL keyword for functions
 explain (costs off)
   select count(*) from tenk1 a, generate_series(1,two) g;
-                      QUERY PLAN                      
-------------------------------------------------------
+                               QUERY PLAN                               
+------------------------------------------------------------------------
  Aggregate
    ->  Nested Loop
          ->  Seq Scan on tenk1 a
          ->  Memoize
                Cache Key: a.two
                Cache Mode: binary
+               Estimated Capacity: 2  Estimated Distinct Lookup Keys: 2
                ->  Function Scan on generate_series g
-(7 rows)
+(8 rows)
 
 -- lateral with UNION ALL subselect
 explain (costs off)
@@ -7702,8 +7711,8 @@ select count(*) from tenk1 a,
 explain (costs off)
   select count(*) from tenk1 a,
     tenk1 b join lateral (values(a.unique1),(-1)) ss(x) on b.unique2 = ss.x;
-                            QUERY PLAN                            
-------------------------------------------------------------------
+                               QUERY PLAN                               
+------------------------------------------------------------------------
  Aggregate
    ->  Nested Loop
          ->  Nested Loop
@@ -7712,9 +7721,10 @@ explain (costs off)
          ->  Memoize
                Cache Key: "*VALUES*".column1
                Cache Mode: logical
+               Estimated Capacity: 2  Estimated Distinct Lookup Keys: 2
                ->  Index Only Scan using tenk1_unique2 on tenk1 b
                      Index Cond: (unique2 = "*VALUES*".column1)
-(10 rows)
+(11 rows)
 
 select count(*) from tenk1 a,
   tenk1 b join lateral (values(a.unique1),(-1)) ss(x) on b.unique2 = ss.x;
diff --git a/src/test/regress/expected/memoize.out b/src/test/regress/expected/memoize.out
index 38dfaf021c9..39c76aaa1a4 100644
--- a/src/test/regress/expected/memoize.out
+++ b/src/test/regress/expected/memoize.out
@@ -46,12 +46,13 @@ WHERE t2.unique1 < 1000;', false);
          ->  Memoize (actual rows=1.00 loops=N)
                Cache Key: t2.twenty
                Cache Mode: logical
+               Estimated Capacity: 20  Estimated Distinct Lookup Keys: 20
                Hits: 980  Misses: 20  Evictions: Zero  Overflows: 0  Memory Usage: NkB
                ->  Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1.00 loops=N)
                      Index Cond: (unique1 = t2.twenty)
                      Heap Fetches: N
                      Index Searches: N
-(13 rows)
+(14 rows)
 
 -- And check we get the expected results.
 SELECT COUNT(*),AVG(t1.unique1) FROM tenk1 t1
@@ -78,12 +79,13 @@ WHERE t1.unique1 < 1000;', false);
          ->  Memoize (actual rows=1.00 loops=N)
                Cache Key: t1.twenty
                Cache Mode: binary
+               Estimated Capacity: 20  Estimated Distinct Lookup Keys: 20
                Hits: 980  Misses: 20  Evictions: Zero  Overflows: 0  Memory Usage: NkB
                ->  Index Only Scan using tenk1_unique1 on tenk1 t2 (actual rows=1.00 loops=N)
                      Index Cond: (unique1 = t1.twenty)
                      Heap Fetches: N
                      Index Searches: N
-(13 rows)
+(14 rows)
 
 -- And check we get the expected results.
 SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1,
@@ -113,6 +115,7 @@ WHERE t1.unique1 < 10;', false);
          ->  Memoize (actual rows=2.00 loops=N)
                Cache Key: t1.two
                Cache Mode: binary
+               Estimated Capacity: 2  Estimated Distinct Lookup Keys: 2
                Hits: 8  Misses: 2  Evictions: Zero  Overflows: 0  Memory Usage: NkB
                ->  Subquery Scan on t2 (actual rows=2.00 loops=N)
                      Filter: (t1.two = t2.two)
@@ -120,7 +123,7 @@ WHERE t1.unique1 < 10;', false);
                      ->  Index Scan using tenk1_unique1 on tenk1 t2_1 (actual rows=4.00 loops=N)
                            Index Cond: (unique1 < 4)
                            Index Searches: N
-(15 rows)
+(16 rows)
 
 -- And check we get the expected results.
 SELECT COUNT(*),AVG(t2.t1two) FROM tenk1 t1 LEFT JOIN
@@ -149,13 +152,14 @@ WHERE s.c1 = s.c2 AND t1.unique1 < 1000;', false);
          ->  Memoize (actual rows=1.00 loops=N)
                Cache Key: (t1.two + 1)
                Cache Mode: binary
+               Estimated Capacity: 2  Estimated Distinct Lookup Keys: 2
                Hits: 998  Misses: 2  Evictions: Zero  Overflows: 0  Memory Usage: NkB
                ->  Index Only Scan using tenk1_unique1 on tenk1 t2 (actual rows=1.00 loops=N)
                      Filter: ((t1.two + 1) = unique1)
                      Rows Removed by Filter: 9999
                      Heap Fetches: N
                      Index Searches: N
-(14 rows)
+(15 rows)
 
 -- And check we get the expected results.
 SELECT COUNT(*), AVG(t1.twenty) FROM tenk1 t1 LEFT JOIN
@@ -182,11 +186,12 @@ WHERE s.c1 = s.c2 AND t1.unique1 < 1000;', false);
          ->  Memoize (actual rows=1.00 loops=N)
                Cache Key: t1.two, t1.twenty
                Cache Mode: binary
+               Estimated Capacity: 40  Estimated Distinct Lookup Keys: 40
                Hits: 980  Misses: 20  Evictions: Zero  Overflows: 0  Memory Usage: NkB
                ->  Seq Scan on tenk1 t2 (actual rows=1.00 loops=N)
                      Filter: ((t1.twenty = unique1) AND (t1.two = two))
                      Rows Removed by Filter: 9999
-(12 rows)
+(13 rows)
 
 -- And check we get the expected results.
 SELECT COUNT(*), AVG(t1.twenty) FROM tenk1 t1 LEFT JOIN
@@ -220,13 +225,14 @@ ON t1.x = t2.t::numeric AND t1.t::numeric = t2.x;', false);
    ->  Memoize (actual rows=2.00 loops=N)
          Cache Key: t1.x, (t1.t)::numeric
          Cache Mode: logical
+         Estimated Capacity: 20  Estimated Distinct Lookup Keys: 20
          Hits: 20  Misses: 20  Evictions: Zero  Overflows: 0  Memory Usage: NkB
          ->  Index Only Scan using expr_key_idx_x_t on expr_key t2 (actual rows=2.00 loops=N)
                Index Cond: (x = (t1.t)::numeric)
                Filter: (t1.x = (t)::numeric)
                Heap Fetches: N
                Index Searches: N
-(11 rows)
+(12 rows)
 
 DROP TABLE expr_key;
 -- Reduce work_mem and hash_mem_multiplier so that we see some cache evictions
@@ -249,12 +255,13 @@ WHERE t2.unique1 < 1200;', true);
          ->  Memoize (actual rows=1.00 loops=N)
                Cache Key: t2.thousand
                Cache Mode: logical
+               Estimated Capacity: 655  Estimated Distinct Lookup Keys: 721
                Hits: N  Misses: N  Evictions: N  Overflows: 0  Memory Usage: NkB
                ->  Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1.00 loops=N)
                      Index Cond: (unique1 = t2.thousand)
                      Heap Fetches: N
                      Index Searches: N
-(13 rows)
+(14 rows)
 
 CREATE TABLE flt (f float);
 CREATE INDEX flt_f_idx ON flt (f);
@@ -273,12 +280,13 @@ SELECT * FROM flt f1 INNER JOIN flt f2 ON f1.f = f2.f;', false);
    ->  Memoize (actual rows=2.00 loops=N)
          Cache Key: f1.f
          Cache Mode: logical
+         Estimated Capacity: 1  Estimated Distinct Lookup Keys: 1
          Hits: 1  Misses: 1  Evictions: Zero  Overflows: 0  Memory Usage: NkB
          ->  Index Only Scan using flt_f_idx on flt f2 (actual rows=2.00 loops=N)
                Index Cond: (f = f1.f)
                Heap Fetches: N
                Index Searches: N
-(12 rows)
+(13 rows)
 
 -- Ensure memoize operates in binary mode
 SELECT explain_memoize('
@@ -292,12 +300,13 @@ SELECT * FROM flt f1 INNER JOIN flt f2 ON f1.f >= f2.f;', false);
    ->  Memoize (actual rows=2.00 loops=N)
          Cache Key: f1.f
          Cache Mode: binary
+         Estimated Capacity: 1  Estimated Distinct Lookup Keys: 1
          Hits: 0  Misses: 2  Evictions: Zero  Overflows: 0  Memory Usage: NkB
          ->  Index Only Scan using flt_f_idx on flt f2 (actual rows=2.00 loops=N)
                Index Cond: (f <= f1.f)
                Heap Fetches: N
                Index Searches: N
-(12 rows)
+(13 rows)
 
 DROP TABLE flt;
 -- Exercise Memoize in binary mode with a large fixed width type and a
@@ -320,11 +329,12 @@ SELECT * FROM strtest s1 INNER JOIN strtest s2 ON s1.n >= s2.n;', false);
    ->  Memoize (actual rows=4.00 loops=N)
          Cache Key: s1.n
          Cache Mode: binary
+         Estimated Capacity: 3  Estimated Distinct Lookup Keys: 3
          Hits: 3  Misses: 3  Evictions: Zero  Overflows: 0  Memory Usage: NkB
          ->  Index Scan using strtest_n_idx on strtest s2 (actual rows=4.00 loops=N)
                Index Cond: (n <= s1.n)
                Index Searches: N
-(10 rows)
+(11 rows)
 
 -- Ensure we get 3 hits and 3 misses
 SELECT explain_memoize('
@@ -337,11 +347,12 @@ SELECT * FROM strtest s1 INNER JOIN strtest s2 ON s1.t >= s2.t;', false);
    ->  Memoize (actual rows=4.00 loops=N)
          Cache Key: s1.t
          Cache Mode: binary
+         Estimated Capacity: 4  Estimated Distinct Lookup Keys: 4
          Hits: 3  Misses: 3  Evictions: Zero  Overflows: 0  Memory Usage: NkB
          ->  Index Scan using strtest_t_idx on strtest s2 (actual rows=4.00 loops=N)
                Index Cond: (t <= s1.t)
                Index Searches: N
-(10 rows)
+(11 rows)
 
 DROP TABLE strtest;
 -- Ensure memoize works with partitionwise join
@@ -366,6 +377,7 @@ SELECT * FROM prt t1 INNER JOIN prt t2 ON t1.a = t2.a;', false);
          ->  Memoize (actual rows=4.00 loops=N)
                Cache Key: t1_1.a
                Cache Mode: logical
+               Estimated Capacity: 2  Estimated Distinct Lookup Keys: 2
                Hits: 3  Misses: 1  Evictions: Zero  Overflows: 0  Memory Usage: NkB
                ->  Index Only Scan using iprt_p1_a on prt_p1 t2_1 (actual rows=4.00 loops=N)
                      Index Cond: (a = t1_1.a)
@@ -378,12 +390,13 @@ SELECT * FROM prt t1 INNER JOIN prt t2 ON t1.a = t2.a;', false);
          ->  Memoize (actual rows=4.00 loops=N)
                Cache Key: t1_2.a
                Cache Mode: logical
+               Estimated Capacity: 2  Estimated Distinct Lookup Keys: 2
                Hits: 3  Misses: 1  Evictions: Zero  Overflows: 0  Memory Usage: NkB
                ->  Index Only Scan using iprt_p2_a on prt_p2 t2_2 (actual rows=4.00 loops=N)
                      Index Cond: (a = t1_2.a)
                      Heap Fetches: N
                      Index Searches: N
-(25 rows)
+(27 rows)
 
 -- Ensure memoize works with parameterized union-all Append path
 SET enable_partitionwise_join TO off;
@@ -400,6 +413,7 @@ ON t1.a = t2.a;', false);
    ->  Memoize (actual rows=4.00 loops=N)
          Cache Key: t1.a
          Cache Mode: logical
+         Estimated Capacity: 1  Estimated Distinct Lookup Keys: 1
          Hits: 3  Misses: 1  Evictions: Zero  Overflows: 0  Memory Usage: NkB
          ->  Append (actual rows=4.00 loops=N)
                ->  Index Only Scan using iprt_p1_a on prt_p1 (actual rows=4.00 loops=N)
@@ -410,7 +424,7 @@ ON t1.a = t2.a;', false);
                      Index Cond: (a = t1.a)
                      Heap Fetches: N
                      Index Searches: N
-(17 rows)
+(18 rows)
 
 DROP TABLE prt;
 RESET enable_partitionwise_join;
@@ -424,8 +438,8 @@ WHERE unique1 < 3
 	SELECT 1 FROM tenk1 t1
 	INNER JOIN tenk1 t2 ON t1.unique1 = t2.hundred
 	WHERE t0.ten = t1.twenty AND t0.two <> t2.four OFFSET 0);
-                           QUERY PLAN                           
-----------------------------------------------------------------
+                                  QUERY PLAN                                  
+------------------------------------------------------------------------------
  Index Scan using tenk1_unique1 on tenk1 t0
    Index Cond: (unique1 < 3)
    Filter: EXISTS(SubPlan 1)
@@ -436,10 +450,11 @@ WHERE unique1 < 3
            ->  Memoize
                  Cache Key: t2.hundred
                  Cache Mode: logical
+                 Estimated Capacity: 100  Estimated Distinct Lookup Keys: 100
                  ->  Index Scan using tenk1_unique1 on tenk1 t1
                        Index Cond: (unique1 = t2.hundred)
                        Filter: (t0.ten = twenty)
-(13 rows)
+(14 rows)
 
 -- Ensure the above query returns the correct result
 SELECT unique1 FROM tenk1 t0
@@ -469,8 +484,8 @@ EXPLAIN (COSTS OFF)
 SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1,
 LATERAL (SELECT t2.unique1 FROM tenk1 t2 WHERE t1.twenty = t2.unique1) t2
 WHERE t1.unique1 < 1000;
-                                  QUERY PLAN                                   
--------------------------------------------------------------------------------
+                                      QUERY PLAN                                      
+--------------------------------------------------------------------------------------
  Finalize Aggregate
    ->  Gather
          Workers Planned: 2
@@ -483,9 +498,10 @@ WHERE t1.unique1 < 1000;
                      ->  Memoize
                            Cache Key: t1.twenty
                            Cache Mode: logical
+                           Estimated Capacity: 20  Estimated Distinct Lookup Keys: 20
                            ->  Index Only Scan using tenk1_unique1 on tenk1 t2
                                  Index Cond: (unique1 = t1.twenty)
-(14 rows)
+(15 rows)
 
 -- And ensure the parallel plan gives us the correct results.
 SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1,
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index 6101c8c7cf1..d3c37ffe22b 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -5289,8 +5289,8 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 1;
 
 -- Increase number of tuples requested and an IndexScan will be chosen
 EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100;
-                               QUERY PLAN                               
-------------------------------------------------------------------------
+                                   QUERY PLAN                                   
+--------------------------------------------------------------------------------
  Limit
    ->  Append
          ->  Nested Loop
@@ -5298,6 +5298,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100;
                ->  Memoize
                      Cache Key: p1_1.c
                      Cache Mode: logical
+                     Estimated Capacity: 12  Estimated Distinct Lookup Keys: 12
                      ->  Index Scan using pht1_p1_c_idx on pht1_p1 p2_1
                            Index Cond: (c = p1_1.c)
          ->  Nested Loop
@@ -5305,6 +5306,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100;
                ->  Memoize
                      Cache Key: p1_2.c
                      Cache Mode: logical
+                     Estimated Capacity: 12  Estimated Distinct Lookup Keys: 12
                      ->  Index Scan using pht1_p2_c_idx on pht1_p2 p2_2
                            Index Cond: (c = p1_2.c)
          ->  Nested Loop
@@ -5312,9 +5314,10 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100;
                ->  Memoize
                      Cache Key: p1_3.c
                      Cache Mode: logical
+                     Estimated Capacity: 12  Estimated Distinct Lookup Keys: 12
                      ->  Index Scan using pht1_p3_c_idx on pht1_p3 p2_3
                            Index Cond: (c = p1_3.c)
-(23 rows)
+(26 rows)
 
 -- If almost all the data should be fetched - prefer SeqScan
 EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 1000;
@@ -5343,8 +5346,8 @@ SET max_parallel_workers_per_gather = 1;
 SET debug_parallel_query = on;
 -- Partial paths should also be smart enough to employ limits
 EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100;
-                                  QUERY PLAN                                  
-------------------------------------------------------------------------------
+                                      QUERY PLAN                                      
+--------------------------------------------------------------------------------------
  Gather
    Workers Planned: 1
    Single Copy: true
@@ -5355,6 +5358,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100;
                      ->  Memoize
                            Cache Key: p1_1.c
                            Cache Mode: logical
+                           Estimated Capacity: 12  Estimated Distinct Lookup Keys: 12
                            ->  Index Scan using pht1_p1_c_idx on pht1_p1 p2_1
                                  Index Cond: (c = p1_1.c)
                ->  Nested Loop
@@ -5362,6 +5366,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100;
                      ->  Memoize
                            Cache Key: p1_2.c
                            Cache Mode: logical
+                           Estimated Capacity: 12  Estimated Distinct Lookup Keys: 12
                            ->  Index Scan using pht1_p2_c_idx on pht1_p2 p2_2
                                  Index Cond: (c = p1_2.c)
                ->  Nested Loop
@@ -5369,9 +5374,10 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100;
                      ->  Memoize
                            Cache Key: p1_3.c
                            Cache Mode: logical
+                           Estimated Capacity: 12  Estimated Distinct Lookup Keys: 12
                            ->  Index Scan using pht1_p3_c_idx on pht1_p3 p2_3
                                  Index Cond: (c = p1_3.c)
-(26 rows)
+(29 rows)
 
 RESET debug_parallel_query;
 -- Remove indexes from the partitioned table and its partitions
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index 40d8056fcea..f5575c7d332 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -1316,8 +1316,8 @@ select sum(o.four), sum(ss.a) from
     select * from x
   ) ss
 where o.ten = 1;
-                       QUERY PLAN                        
----------------------------------------------------------
+                               QUERY PLAN                               
+------------------------------------------------------------------------
  Aggregate
    ->  Nested Loop
          ->  Seq Scan on onek o
@@ -1325,13 +1325,14 @@ where o.ten = 1;
          ->  Memoize
                Cache Key: o.four
                Cache Mode: binary
+               Estimated Capacity: 4  Estimated Distinct Lookup Keys: 4
                ->  CTE Scan on x
                      CTE x
                        ->  Recursive Union
                              ->  Result
                              ->  WorkTable Scan on x x_1
                                    Filter: (a < 10)
-(13 rows)
+(14 rows)
 
 select sum(o.four), sum(ss.a) from
   onek o cross join lateral (
@@ -2642,6 +2643,7 @@ ON B.hundred in (SELECT min(c.hundred) FROM tenk2 C WHERE c.odd = b.odd);
                ->  Memoize
                      Cache Key: b.hundred, b.odd
                      Cache Mode: binary
+                     Estimated Capacity: 1000  Estimated Distinct Lookup Keys: 1000
                      ->  Subquery Scan on "ANY_subquery"
                            Filter: (b.hundred = "ANY_subquery".min)
                            ->  Result
@@ -2650,7 +2652,7 @@ ON B.hundred in (SELECT min(c.hundred) FROM tenk2 C WHERE c.odd = b.odd);
                                          ->  Index Scan using tenk2_hundred on tenk2 c
                                                Index Cond: (hundred IS NOT NULL)
                                                Filter: (odd = b.odd)
-(16 rows)
+(17 rows)
 
 --
 -- Test VALUES to ARRAY (VtA) transformation
-- 
2.34.1

From b2dc58e271075909d5136b09259f2f6b83d4d112 Mon Sep 17 00:00:00 2001
From: Evdokimov Ilia <[email-masked]>
Date: Tue, 15 Apr 2025 01:53:27 +0300
Subject: [PATCH v7 2/2] Add Estimated Hit Ratio for Memoize plan nodes in
 EXPLAIN

---
 src/backend/commands/explain.c               |  4 +-
 src/backend/optimizer/path/costsize.c        |  3 ++
 src/backend/optimizer/plan/createplan.c      |  7 +--
 src/backend/optimizer/util/pathnode.c        |  5 +++
 src/include/nodes/pathnodes.h                |  1 +
 src/include/nodes/plannodes.h                |  3 ++
 src/test/regress/expected/create_index.out   |  3 +-
 src/test/regress/expected/join.out           | 28 ++++++++----
 src/test/regress/expected/memoize.out        | 46 +++++++++++++-------
 src/test/regress/expected/partition_join.out | 10 ++++-
 src/test/regress/expected/subselect.out      |  6 ++-
 11 files changed, 83 insertions(+), 33 deletions(-)

diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 0a4ce5ee7d7..d8526b7d06d 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -3634,11 +3634,13 @@ show_memoize_info(MemoizeState *mstate, List *ancestors, ExplainState *es)
 		appendStringInfo(es->str, "Estimated Capacity: %u  Estimated Distinct Lookup Keys: %0.0f\n",
 						((Memoize *) plan)->est_entries,
 						((Memoize *) plan)->est_unique_keys);
-	}
+		ExplainIndentText(es);
+		appendStringInfo(es->str, "Estimated Hit Ratio: %0.2f\n", ((Memoize *) plan)->hit_ratio);	}
 	else
 	{
 		ExplainPropertyUInteger("Estimated Capacity", "", ((Memoize *) plan)->est_entries, es);
 		ExplainPropertyFloat("Estimated Distinct Lookup Keys", "", ((Memoize *) plan)->est_unique_keys, 0, es);
+		ExplainPropertyFloat("Estimated Hit Ratio", "", ((Memoize *) plan)->hit_ratio, 2, es);
 	}
 
 	pfree(keystr.data);
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index f72319d903c..3e99214501b 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -2624,6 +2624,9 @@ cost_memoize_rescan(PlannerInfo *root, MemoizePath *mpath,
 	hit_ratio = ((calls - ndistinct) / calls) *
 		(est_cache_entries / Max(ndistinct, est_cache_entries));
 
+	/* Remember cache hit ratio for a potential EXPLAIN later */
+	mpath->hit_ratio = hit_ratio;
+
 	Assert(hit_ratio >= 0 && hit_ratio <= 1.0);
 
 	/*
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index a1456c9014d..ccb880158fe 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -285,7 +285,7 @@ static Memoize *make_memoize(Plan *lefttree, Oid *hashoperators,
 							 Oid *collations, List *param_exprs,
 							 bool singlerow, bool binary_mode,
 							uint32 est_entries, Bitmapset *keyparamids,
-							double est_unique_keys);
+							double est_unique_keys, double hit_ratio);
 static WindowAgg *make_windowagg(List *tlist, WindowClause *wc,
 								 int partNumCols, AttrNumber *partColIdx, Oid *partOperators, Oid *partCollations,
 								 int ordNumCols, AttrNumber *ordColIdx, Oid *ordOperators, Oid *ordCollations,
@@ -1705,7 +1705,7 @@ create_memoize_plan(PlannerInfo *root, MemoizePath *best_path, int flags)
 	plan = make_memoize(subplan, operators, collations, param_exprs,
 						best_path->singlerow, best_path->binary_mode,
 						best_path->est_entries, keyparamids,
-						best_path->est_unique_keys);
+						best_path->est_unique_keys, best_path->hit_ratio);

@@ -6639,7 +6639,7 @@ static Memoize *
 make_memoize(Plan *lefttree, Oid *hashoperators, Oid *collations,
 			 List *param_exprs, bool singlerow, bool binary_mode,
 			uint32 est_entries, Bitmapset *keyparamids,
-			double est_unique_keys)
+			double est_unique_keys, double hit_ratio)
 {
 	Memoize    *node = makeNode(Memoize);
 	Plan	   *plan = &node->plan;
@@ -6658,6 +6658,7 @@ make_memoize(Plan *lefttree, Oid *hashoperators, Oid *collations,
 	node->est_entries = est_entries;
 	node->keyparamids = keyparamids;
 	node->est_unique_keys = est_unique_keys;
+	node->hit_ratio = hit_ratio;
 
 	return node;
 }
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index 1fbcda99067..5971f67b77a 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -1704,6 +1704,11 @@ create_memoize_path(PlannerInfo *root, RelOptInfo *rel, Path *subpath,
 	/* Estimated number of distinct memoization keys, computed using estimate_num_groups() */
 	pathnode->est_unique_keys = 0;
 
+	/*
+	 * The estimated cache hit ratio will calculated later by cost_memoize_rescan()
+	 */
+	pathnode->hit_ratio = 0;
+
 	/*
 	 * Add a small additional charge for caching the first entry.  All the
 	 * harder calculations for rescans are performed in cost_memoize_rescan().
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 07d97dc0b5b..9bfc35cd4f6 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -2140,6 +2140,7 @@ typedef struct MemoizePath
 								 * if unknown */
 	double		est_unique_keys;	/* Estimated number of distinct memoization keys,
 								 * used for cache size evaluation. Kept for EXPLAIN */
+	double		hit_ratio;		/* Estimated cache hit ratio, kept for EXPLAIN */
 } MemoizePath;
 
 /*
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 3d9d3a1159d..a7d350ce9ca 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -1069,6 +1069,9 @@ typedef struct Memoize
 	 * used for cache size evaluation. Kept for EXPLAIN
 	 */
 	double		est_unique_keys;
+
+	/* Estimated cache hit ratio, kept for EXPLAIN */
+	double		hit_ratio;
 } Memoize;
 
 /* ----------------
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index 826018a8a9f..0fa4359e23c 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -2246,10 +2246,11 @@ SELECT count(*) FROM tenk1 LEFT JOIN tenk2 ON
                Cache Key: tenk1.hundred
                Cache Mode: logical
                Estimated Capacity: 100  Estimated Distinct Lookup Keys: 100
+               Estimated Hit Ratio: 0.99
                ->  Index Scan using tenk2_hundred on tenk2
                      Index Cond: (hundred = tenk1.hundred)
                      Filter: ((thousand = 42) OR (thousand = 41) OR (tenthous = 2))
-(11 rows)
+(12 rows)
 
 --
 -- Check behavior with duplicate index column contents
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index eca6ce2c6f0..212b7897919 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -2741,9 +2741,10 @@ select * from onek t1
                      Cache Key: t2.two
                      Cache Mode: binary
                      Estimated Capacity: 2  Estimated Distinct Lookup Keys: 2
+                     Estimated Hit Ratio: 1.00
                      ->  Seq Scan on onek t3
                            Filter: (two = t2.two)
-(12 rows)
+(13 rows)
 
 --
 -- check a case where we formerly got confused by conflicting sort orders
@@ -5148,11 +5149,12 @@ where t1.f1 = ss.f1;
          Cache Key: i8.q1
          Cache Mode: binary
          Estimated Capacity: 1  Estimated Distinct Lookup Keys: 1
+         Estimated Hit Ratio: 0.50
          ->  Limit
                Output: (i8.q1), t2.f1
                ->  Seq Scan on public.text_tbl t2
                      Output: i8.q1, t2.f1
-(21 rows)
+(22 rows)
 
 select * from
   text_tbl t1
@@ -5194,6 +5196,7 @@ where t1.f1 = ss2.f1;
                Cache Key: i8.q1
                Cache Mode: binary
                Estimated Capacity: 1  Estimated Distinct Lookup Keys: 1
+               Estimated Hit Ratio: 0.50
                ->  Limit
                      Output: (i8.q1), t2.f1
                      ->  Seq Scan on public.text_tbl t2
@@ -5203,11 +5206,12 @@ where t1.f1 = ss2.f1;
          Cache Key: (i8.q1), t2.f1
          Cache Mode: binary
          Estimated Capacity: 1  Estimated Distinct Lookup Keys: 1
+         Estimated Hit Ratio: 0.50
          ->  Limit
                Output: ((i8.q1)), (t2.f1)
                ->  Seq Scan on public.text_tbl t3
                      Output: (i8.q1), t2.f1
-(32 rows)
+(34 rows)
 
 select * from
   text_tbl t1
@@ -5257,6 +5261,7 @@ where tt1.f1 = ss1.c0;
          Cache Key: tt4.f1
          Cache Mode: binary
          Estimated Capacity: 1  Estimated Distinct Lookup Keys: 1
+         Estimated Hit Ratio: 0.50
          ->  Subquery Scan on ss1
                Output: ss1.c0
                Filter: (ss1.c0 = 'foo'::text)
@@ -5264,7 +5269,7 @@ where tt1.f1 = ss1.c0;
                      Output: (tt4.f1)
                      ->  Seq Scan on public.text_tbl tt5
                            Output: tt4.f1
-(33 rows)
+(34 rows)
 
 select 1 from
   text_tbl as tt1
@@ -6475,10 +6480,11 @@ select * from sj t1
          Cache Key: t1.a, t1.b
          Cache Mode: binary
          Estimated Capacity: 2  Estimated Distinct Lookup Keys: 2
+         Estimated Hit Ratio: 0.33
          ->  Sample Scan on sj
                Sampling: system (t1.b)
                Filter: (t1.a = a)
-(9 rows)
+(10 rows)
 
 -- Ensure that SJE does not form a self-referential lateral dependency
 explain (costs off)
@@ -7629,8 +7635,9 @@ explain (costs off)
                Cache Key: a.two
                Cache Mode: binary
                Estimated Capacity: 2  Estimated Distinct Lookup Keys: 2
+               Estimated Hit Ratio: 1.00
                ->  Function Scan on generate_series g
-(8 rows)
+(9 rows)
 
 explain (costs off)
   select count(*) from tenk1 a cross join lateral generate_series(1,two) g;
@@ -7643,8 +7650,9 @@ explain (costs off)
                Cache Key: a.two
                Cache Mode: binary
                Estimated Capacity: 2  Estimated Distinct Lookup Keys: 2
+               Estimated Hit Ratio: 1.00
                ->  Function Scan on generate_series g
-(8 rows)
+(9 rows)
 
 -- don't need the explicit LATERAL keyword for functions
 explain (costs off)
@@ -7658,8 +7666,9 @@ explain (costs off)
                Cache Key: a.two
                Cache Mode: binary
                Estimated Capacity: 2  Estimated Distinct Lookup Keys: 2
+               Estimated Hit Ratio: 1.00
                ->  Function Scan on generate_series g
-(8 rows)
+(9 rows)
 
 -- lateral with UNION ALL subselect
 explain (costs off)
@@ -7722,9 +7731,10 @@ explain (costs off)
                Cache Key: "*VALUES*".column1
                Cache Mode: logical
                Estimated Capacity: 2  Estimated Distinct Lookup Keys: 2
+               Estimated Hit Ratio: 1.00
                ->  Index Only Scan using tenk1_unique2 on tenk1 b
                      Index Cond: (unique2 = "*VALUES*".column1)
-(11 rows)
+(12 rows)
 
 select count(*) from tenk1 a,
   tenk1 b join lateral (values(a.unique1),(-1)) ss(x) on b.unique2 = ss.x;
diff --git a/src/test/regress/expected/memoize.out b/src/test/regress/expected/memoize.out
index 39c76aaa1a4..1e1b3419991 100644
--- a/src/test/regress/expected/memoize.out
+++ b/src/test/regress/expected/memoize.out
@@ -47,12 +47,13 @@ WHERE t2.unique1 < 1000;', false);
                Cache Key: t2.twenty
                Cache Mode: logical
                Estimated Capacity: 20  Estimated Distinct Lookup Keys: 20
+               Estimated Hit Ratio: 0.98
                Hits: 980  Misses: 20  Evictions: Zero  Overflows: 0  Memory Usage: NkB
                ->  Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1.00 loops=N)
                      Index Cond: (unique1 = t2.twenty)
                      Heap Fetches: N
                      Index Searches: N
-(14 rows)
+(15 rows)
 
 -- And check we get the expected results.
 SELECT COUNT(*),AVG(t1.unique1) FROM tenk1 t1
@@ -80,12 +81,13 @@ WHERE t1.unique1 < 1000;', false);
                Cache Key: t1.twenty
                Cache Mode: binary
                Estimated Capacity: 20  Estimated Distinct Lookup Keys: 20
+               Estimated Hit Ratio: 0.98
                Hits: 980  Misses: 20  Evictions: Zero  Overflows: 0  Memory Usage: NkB
                ->  Index Only Scan using tenk1_unique1 on tenk1 t2 (actual rows=1.00 loops=N)
                      Index Cond: (unique1 = t1.twenty)
                      Heap Fetches: N
                      Index Searches: N
-(14 rows)
+(15 rows)
 
 -- And check we get the expected results.
 SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1,
@@ -116,6 +118,7 @@ WHERE t1.unique1 < 10;', false);
                Cache Key: t1.two
                Cache Mode: binary
                Estimated Capacity: 2  Estimated Distinct Lookup Keys: 2
+               Estimated Hit Ratio: 0.80
                Hits: 8  Misses: 2  Evictions: Zero  Overflows: 0  Memory Usage: NkB
                ->  Subquery Scan on t2 (actual rows=2.00 loops=N)
                      Filter: (t1.two = t2.two)
@@ -123,7 +126,7 @@ WHERE t1.unique1 < 10;', false);
                      ->  Index Scan using tenk1_unique1 on tenk1 t2_1 (actual rows=4.00 loops=N)
                            Index Cond: (unique1 < 4)
                            Index Searches: N
-(16 rows)
+(17 rows)
 
 -- And check we get the expected results.
 SELECT COUNT(*),AVG(t2.t1two) FROM tenk1 t1 LEFT JOIN
@@ -153,13 +156,14 @@ WHERE s.c1 = s.c2 AND t1.unique1 < 1000;', false);
                Cache Key: (t1.two + 1)
                Cache Mode: binary
                Estimated Capacity: 2  Estimated Distinct Lookup Keys: 2
+               Estimated Hit Ratio: 1.00
                Hits: 998  Misses: 2  Evictions: Zero  Overflows: 0  Memory Usage: NkB
                ->  Index Only Scan using tenk1_unique1 on tenk1 t2 (actual rows=1.00 loops=N)
                      Filter: ((t1.two + 1) = unique1)
                      Rows Removed by Filter: 9999
                      Heap Fetches: N
                      Index Searches: N
-(15 rows)
+(16 rows)
 
 -- And check we get the expected results.
 SELECT COUNT(*), AVG(t1.twenty) FROM tenk1 t1 LEFT JOIN
@@ -187,11 +191,12 @@ WHERE s.c1 = s.c2 AND t1.unique1 < 1000;', false);
                Cache Key: t1.two, t1.twenty
                Cache Mode: binary
                Estimated Capacity: 40  Estimated Distinct Lookup Keys: 40
+               Estimated Hit Ratio: 0.96
                Hits: 980  Misses: 20  Evictions: Zero  Overflows: 0  Memory Usage: NkB
                ->  Seq Scan on tenk1 t2 (actual rows=1.00 loops=N)
                      Filter: ((t1.twenty = unique1) AND (t1.two = two))
                      Rows Removed by Filter: 9999
-(13 rows)
+(14 rows)
 
 -- And check we get the expected results.
 SELECT COUNT(*), AVG(t1.twenty) FROM tenk1 t1 LEFT JOIN
@@ -226,13 +231,14 @@ ON t1.x = t2.t::numeric AND t1.t::numeric = t2.x;', false);
          Cache Key: t1.x, (t1.t)::numeric
          Cache Mode: logical
          Estimated Capacity: 20  Estimated Distinct Lookup Keys: 20
+         Estimated Hit Ratio: 0.50
          Hits: 20  Misses: 20  Evictions: Zero  Overflows: 0  Memory Usage: NkB
          ->  Index Only Scan using expr_key_idx_x_t on expr_key t2 (actual rows=2.00 loops=N)
                Index Cond: (x = (t1.t)::numeric)
                Filter: (t1.x = (t)::numeric)
                Heap Fetches: N
                Index Searches: N
-(12 rows)
+(13 rows)
 
 DROP TABLE expr_key;
 -- Reduce work_mem and hash_mem_multiplier so that we see some cache evictions
@@ -256,12 +262,13 @@ WHERE t2.unique1 < 1200;', true);
                Cache Key: t2.thousand
                Cache Mode: logical
                Estimated Capacity: 655  Estimated Distinct Lookup Keys: 721
+               Estimated Hit Ratio: 0.36
                Hits: N  Misses: N  Evictions: N  Overflows: 0  Memory Usage: NkB
                ->  Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1.00 loops=N)
                      Index Cond: (unique1 = t2.thousand)
                      Heap Fetches: N
                      Index Searches: N
-(14 rows)
+(15 rows)
 
 CREATE TABLE flt (f float);
 CREATE INDEX flt_f_idx ON flt (f);
@@ -281,12 +288,13 @@ SELECT * FROM flt f1 INNER JOIN flt f2 ON f1.f = f2.f;', false);
          Cache Key: f1.f
          Cache Mode: logical
          Estimated Capacity: 1  Estimated Distinct Lookup Keys: 1
+         Estimated Hit Ratio: 0.50
          Hits: 1  Misses: 1  Evictions: Zero  Overflows: 0  Memory Usage: NkB
          ->  Index Only Scan using flt_f_idx on flt f2 (actual rows=2.00 loops=N)
                Index Cond: (f = f1.f)
                Heap Fetches: N
                Index Searches: N
-(13 rows)
+(14 rows)
 
 -- Ensure memoize operates in binary mode
 SELECT explain_memoize('
@@ -301,12 +309,13 @@ SELECT * FROM flt f1 INNER JOIN flt f2 ON f1.f >= f2.f;', false);
          Cache Key: f1.f
          Cache Mode: binary
          Estimated Capacity: 1  Estimated Distinct Lookup Keys: 1
+         Estimated Hit Ratio: 0.50
          Hits: 0  Misses: 2  Evictions: Zero  Overflows: 0  Memory Usage: NkB
          ->  Index Only Scan using flt_f_idx on flt f2 (actual rows=2.00 loops=N)
                Index Cond: (f <= f1.f)
                Heap Fetches: N
                Index Searches: N
-(13 rows)
+(14 rows)
 
 DROP TABLE flt;
 -- Exercise Memoize in binary mode with a large fixed width type and a
@@ -330,11 +339,12 @@ SELECT * FROM strtest s1 INNER JOIN strtest s2 ON s1.n >= s2.n;', false);
          Cache Key: s1.n
          Cache Mode: binary
          Estimated Capacity: 3  Estimated Distinct Lookup Keys: 3
+         Estimated Hit Ratio: 0.50
          Hits: 3  Misses: 3  Evictions: Zero  Overflows: 0  Memory Usage: NkB
          ->  Index Scan using strtest_n_idx on strtest s2 (actual rows=4.00 loops=N)
                Index Cond: (n <= s1.n)
                Index Searches: N
-(11 rows)
+(12 rows)
 
 -- Ensure we get 3 hits and 3 misses
 SELECT explain_memoize('
@@ -348,11 +358,12 @@ SELECT * FROM strtest s1 INNER JOIN strtest s2 ON s1.t >= s2.t;', false);
          Cache Key: s1.t
          Cache Mode: binary
          Estimated Capacity: 4  Estimated Distinct Lookup Keys: 4
+         Estimated Hit Ratio: 0.33
          Hits: 3  Misses: 3  Evictions: Zero  Overflows: 0  Memory Usage: NkB
          ->  Index Scan using strtest_t_idx on strtest s2 (actual rows=4.00 loops=N)
                Index Cond: (t <= s1.t)
                Index Searches: N
-(11 rows)
+(12 rows)
 
 DROP TABLE strtest;
 -- Ensure memoize works with partitionwise join
@@ -378,6 +389,7 @@ SELECT * FROM prt t1 INNER JOIN prt t2 ON t1.a = t2.a;', false);
                Cache Key: t1_1.a
                Cache Mode: logical
                Estimated Capacity: 2  Estimated Distinct Lookup Keys: 2
+               Estimated Hit Ratio: 0.50
                Hits: 3  Misses: 1  Evictions: Zero  Overflows: 0  Memory Usage: NkB
                ->  Index Only Scan using iprt_p1_a on prt_p1 t2_1 (actual rows=4.00 loops=N)
                      Index Cond: (a = t1_1.a)
@@ -391,12 +403,13 @@ SELECT * FROM prt t1 INNER JOIN prt t2 ON t1.a = t2.a;', false);
                Cache Key: t1_2.a
                Cache Mode: logical
                Estimated Capacity: 2  Estimated Distinct Lookup Keys: 2
+               Estimated Hit Ratio: 0.50
                Hits: 3  Misses: 1  Evictions: Zero  Overflows: 0  Memory Usage: NkB
                ->  Index Only Scan using iprt_p2_a on prt_p2 t2_2 (actual rows=4.00 loops=N)
                      Index Cond: (a = t1_2.a)
                      Heap Fetches: N
                      Index Searches: N
-(27 rows)
+(29 rows)
 
 -- Ensure memoize works with parameterized union-all Append path
 SET enable_partitionwise_join TO off;
@@ -414,6 +427,7 @@ ON t1.a = t2.a;', false);
          Cache Key: t1.a
          Cache Mode: logical
          Estimated Capacity: 1  Estimated Distinct Lookup Keys: 1
+         Estimated Hit Ratio: 0.75
          Hits: 3  Misses: 1  Evictions: Zero  Overflows: 0  Memory Usage: NkB
          ->  Append (actual rows=4.00 loops=N)
                ->  Index Only Scan using iprt_p1_a on prt_p1 (actual rows=4.00 loops=N)
@@ -424,7 +438,7 @@ ON t1.a = t2.a;', false);
                      Index Cond: (a = t1.a)
                      Heap Fetches: N
                      Index Searches: N
-(18 rows)
+(19 rows)
 
 DROP TABLE prt;
 RESET enable_partitionwise_join;
@@ -451,10 +465,11 @@ WHERE unique1 < 3
                  Cache Key: t2.hundred
                  Cache Mode: logical
                  Estimated Capacity: 100  Estimated Distinct Lookup Keys: 100
+                 Estimated Hit Ratio: 0.99
                  ->  Index Scan using tenk1_unique1 on tenk1 t1
                        Index Cond: (unique1 = t2.hundred)
                        Filter: (t0.ten = twenty)
-(14 rows)
+(15 rows)
 
 -- Ensure the above query returns the correct result
 SELECT unique1 FROM tenk1 t0
@@ -499,9 +514,10 @@ WHERE t1.unique1 < 1000;
                            Cache Key: t1.twenty
                            Cache Mode: logical
                            Estimated Capacity: 20  Estimated Distinct Lookup Keys: 20
+                           Estimated Hit Ratio: 0.95
                            ->  Index Only Scan using tenk1_unique1 on tenk1 t2
                                  Index Cond: (unique1 = t1.twenty)
-(15 rows)
+(16 rows)
 
 -- And ensure the parallel plan gives us the correct results.
 SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1,
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index d3c37ffe22b..15a6d94d7c0 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -5299,6 +5299,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100;
                      Cache Key: p1_1.c
                      Cache Mode: logical
                      Estimated Capacity: 12  Estimated Distinct Lookup Keys: 12
+                     Estimated Hit Ratio: 0.76
                      ->  Index Scan using pht1_p1_c_idx on pht1_p1 p2_1
                            Index Cond: (c = p1_1.c)
          ->  Nested Loop
@@ -5307,6 +5308,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100;
                      Cache Key: p1_2.c
                      Cache Mode: logical
                      Estimated Capacity: 12  Estimated Distinct Lookup Keys: 12
+                     Estimated Hit Ratio: 0.90
                      ->  Index Scan using pht1_p2_c_idx on pht1_p2 p2_2
                            Index Cond: (c = p1_2.c)
          ->  Nested Loop
@@ -5315,9 +5317,10 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100;
                      Cache Key: p1_3.c
                      Cache Mode: logical
                      Estimated Capacity: 12  Estimated Distinct Lookup Keys: 12
+                     Estimated Hit Ratio: 0.90
                      ->  Index Scan using pht1_p3_c_idx on pht1_p3 p2_3
                            Index Cond: (c = p1_3.c)
-(26 rows)
+(29 rows)
 
 -- If almost all the data should be fetched - prefer SeqScan
 EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 1000;
@@ -5359,6 +5362,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100;
                            Cache Key: p1_1.c
                            Cache Mode: logical
                            Estimated Capacity: 12  Estimated Distinct Lookup Keys: 12
+                           Estimated Hit Ratio: 0.76
                            ->  Index Scan using pht1_p1_c_idx on pht1_p1 p2_1
                                  Index Cond: (c = p1_1.c)
                ->  Nested Loop
@@ -5367,6 +5371,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100;
                            Cache Key: p1_2.c
                            Cache Mode: logical
                            Estimated Capacity: 12  Estimated Distinct Lookup Keys: 12
+                           Estimated Hit Ratio: 0.90
                            ->  Index Scan using pht1_p2_c_idx on pht1_p2 p2_2
                                  Index Cond: (c = p1_2.c)
                ->  Nested Loop
@@ -5375,9 +5380,10 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100;
                            Cache Key: p1_3.c
                            Cache Mode: logical
                            Estimated Capacity: 12  Estimated Distinct Lookup Keys: 12
+                           Estimated Hit Ratio: 0.90
                            ->  Index Scan using pht1_p3_c_idx on pht1_p3 p2_3
                                  Index Cond: (c = p1_3.c)
-(29 rows)
+(32 rows)
 
 RESET debug_parallel_query;
 -- Remove indexes from the partitioned table and its partitions
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index f5575c7d332..81e3e07d968 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -1326,13 +1326,14 @@ where o.ten = 1;
                Cache Key: o.four
                Cache Mode: binary
                Estimated Capacity: 4  Estimated Distinct Lookup Keys: 4
+               Estimated Hit Ratio: 0.96
                ->  CTE Scan on x
                      CTE x
                        ->  Recursive Union
                              ->  Result
                              ->  WorkTable Scan on x x_1
                                    Filter: (a < 10)
-(14 rows)
+(15 rows)
 
 select sum(o.four), sum(ss.a) from
   onek o cross join lateral (
@@ -2644,6 +2645,7 @@ ON B.hundred in (SELECT min(c.hundred) FROM tenk2 C WHERE c.odd = b.odd);
                      Cache Key: b.hundred, b.odd
                      Cache Mode: binary
                      Estimated Capacity: 1000  Estimated Distinct Lookup Keys: 1000
+                     Estimated Hit Ratio: 0.90
                      ->  Subquery Scan on "ANY_subquery"
                            Filter: (b.hundred = "ANY_subquery".min)
                            ->  Result
@@ -2652,7 +2654,7 @@ ON B.hundred in (SELECT min(c.hundred) FROM tenk2 C WHERE c.odd = b.odd);
                                          ->  Index Scan using tenk2_hundred on tenk2 c
                                                Index Cond: (hundred IS NOT NULL)
                                                Filter: (odd = b.odd)
-(17 rows)
+(18 rows)
 
 --
 -- Test VALUES to ARRAY (VtA) transformation
-- 
2.34.1

---

From: David Rowley <[email-masked]>
Date: Tue, 15 Apr 2025 12:23:11 +1200

On Tue, 15 Apr 2025 at 11:14, Ilia Evdokimov
<[email-masked]> wrote:
> Agreed - I’ll split the patch into two: one adds Estimated Capacity and Estimated Distinct Keys, and the second adds Estimated Hit Ratio. I’ll also add regression test differences to make it easier to evaluate the usefulness of each part.

Thanks for the patches.

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.

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

---

From: Ilia Evdokimov <[email-masked]>
Date: Tue, 15 Apr 2025 12:44:13 +0300

> isn't obvious if the planner expects every lookup to be a cache hit or

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

> the expected output files.

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.

---

From: Robert Haas <[email-masked]>
Date: Tue, 15 Apr 2025 12:24:57 -0400

> "Estimates: capacity=N distinct keys=N hit ratio=N.N%"

Sure, that works for me.

> "Estimates: capacity=N distinct keys=N lookups=N hit ratio=N.N%"

Is lookups=N here the estimated number of lookups i.e. what we think
nloops will end up being?

---

From: David Rowley <[email-masked]>
Date: Wed, 16 Apr 2025 08:20:00 +1200

On Tue, 15 Apr 2025 at 21:44, Ilia Evdokimov
<[email-masked]> wrote:
> 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?

Disregard what I said about the explain_memoize() function. The new
output just shouldn't be shown with EXPLAIN (costs off).

David

---

From: David Rowley <[email-masked]>
Date: Wed, 16 Apr 2025 08:49:46 +1200

> nloops will end up being?

Yes. The estimate is the "calls" variable in cost_memoize_rescan(),
which is fairly critical in the hit ratio estimate calculation.

Technically this is just the Nested Loop's outer_path->rows. There was
an argument earlier in the thread for putting this in along with the
other fields to make things easier to read. I did argue that it was
redundant due to the fact that the reader can look at the row estimate
for the outer side of the Nest Loop, but maybe it's small enough to go
in using the above format.

David

---

From: Ilia Evdokimov <[email-masked]>
Date: Wed, 16 Apr 2025 01:09:20 +0300

I've prepared the updated patches as discussed, including the addition 
of estimated lookups in the EXPLAIN output for Memoize. Please let me 
know if you have any feedback or further suggestions.

--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC.
From 021cc6f5629a607cab3a0ec1021fc5f102dc0439 Mon Sep 17 00:00:00 2001
From: Evdokimov Ilia <[email-masked]>
Date: Tue, 15 Apr 2025 23:47:57 +0300
Subject: [PATCH v8 1/3] Show ndistinct and est_entries in EXPLAIN for Memoize

Reviewed-by: David Rowley <[email-masked]>
Reviewed-by: Robert Haas <[email-masked]>
Reviewed-by: Andrei Lepikhov <[email-masked]>
Reviewed-by: Tom Lane <[email-masked]>
---
 src/backend/commands/explain.c          | 16 ++++++++++++++++
 src/backend/optimizer/path/costsize.c   |  3 +++
 src/backend/optimizer/plan/createplan.c | 10 +++++++---
 src/backend/optimizer/util/pathnode.c   |  6 ++++++
 src/include/nodes/pathnodes.h           |  2 ++
 src/include/nodes/plannodes.h           |  6 ++++++
 6 files changed, 40 insertions(+), 3 deletions(-)

diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 786ee865f14..0634d0a982e 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -3628,6 +3628,22 @@ show_memoize_info(MemoizeState *mstate, List *ancestors, ExplainState *es)
 	ExplainPropertyText("Cache Key", keystr.data, es);
 	ExplainPropertyText("Cache Mode", mstate->binary_mode ? "binary" : "logical", es);
 
+	if (es->costs)
+	{
+			appendStringInfo(es->str, "Estimates: capacity=%u distinct keys=%.0f\n",
 
 	if (!es->analyze)

 

 	return node;
 }
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index 93e73cb44db..76bdea52127 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -1701,6 +1701,12 @@ create_memoize_path(PlannerInfo *root, RelOptInfo *rel, Path *subpath,
 	Assert(enable_memoize);
 	pathnode->path.disabled_nodes = subpath->disabled_nodes;
 
+	/*
+	 * computed using estimate_num_groups()
 
 /*

 /* ----------------
-- 
2.34.1

From 18052d2d3ac54e12042673e35ca357ae11f99093 Mon Sep 17 00:00:00 2001
From: Evdokimov Ilia <[email-masked]>
Date: Wed, 16 Apr 2025 00:20:49 +0300
Subject: [PATCH v8 2/3] Add Estimated Hit Ratio for Memoize plan nodes in
 EXPLAIN

Reviewed-by: David Rowley <[email-masked]>
Reviewed-by: Robert Haas <[email-masked]>
Reviewed-by: Andrei Lepikhov <[email-masked]>
Reviewed-by: Tom Lane <[email-masked]>
---
 src/backend/commands/explain.c          | 6 ++++--
 src/backend/optimizer/path/costsize.c   | 3 +++
 src/backend/optimizer/plan/createplan.c | 7 ++++---
 src/backend/optimizer/util/pathnode.c   | 6 ++++++
 src/include/nodes/pathnodes.h           | 1 +
 src/include/nodes/plannodes.h           | 3 +++
 6 files changed, 21 insertions(+), 5 deletions(-)

diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 0634d0a982e..85b22561aa6 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -3633,14 +3633,16 @@ show_memoize_info(MemoizeState *mstate, List *ancestors, ExplainState *es)
 		if (es->format == EXPLAIN_FORMAT_TEXT)
 		{
 			ExplainIndentText(es);
-			appendStringInfo(es->str, "Estimates: capacity=%u distinct keys=%.0f\n",
+			appendStringInfo(es->str, "Estimates: capacity=%u distinct keys=%.0f hit ratio=%.2f%%\n",
 							((Memoize *) plan)->est_entries,
-							((Memoize *) plan)->est_unique_keys);
+							((Memoize *) plan)->est_unique_keys,
+							((Memoize *) plan)->hit_ratio * 100.0);
 		}
 		else
 		{
 			ExplainPropertyUInteger("Estimated Capacity", "", ((Memoize *) plan)->est_entries, es);
 			ExplainPropertyFloat("Estimated Distinct Lookup Keys", "", ((Memoize *) plan)->est_unique_keys, 0, es);
+			ExplainPropertyFloat("Estimated Hit Ratio", "", ((Memoize *) plan)->hit_ratio * 100.0, 2, es);
 		}
 	}

 
 	/*

 
 	return node;
 }
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index 76bdea52127..40674027f9f 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -1707,6 +1707,12 @@ create_memoize_path(PlannerInfo *root, RelOptInfo *rel, Path *subpath,
 	 */
 	pathnode->est_unique_keys = 0;
 
+	/*
+	 * The estimated cache hit ratio will calculated later
+	 * by cost_memoize_rescan().
+	 */
+	pathnode->hit_ratio = 0;
+
 	/*
 	 * Add a small additional charge for caching the first entry.  All the
 	 * harder calculations for rescans are performed in cost_memoize_rescan().
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 07d97dc0b5b..e17da6f8f02 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -2140,6 +2140,7 @@ typedef struct MemoizePath
 								 * if unknown */
 	double		est_unique_keys;	/* Estimated number of distinct memoization keys,
 								 * used for cache size evaluation. Kept for EXPLAIN */
+	double		hit_ratio;		/* Estimated cache hit ratio. Kept for EXPLAIN */
 } MemoizePath;
 
 /*
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 3d9d3a1159d..4354d4f66d3 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -1069,6 +1069,9 @@ typedef struct Memoize
 	 * used for cache size evaluation. Kept for EXPLAIN
 	 */
 	double		est_unique_keys;
+
+	/* Estimated cache hit ratio. Kept for EXPLAIN */
+	double		hit_ratio;
 } Memoize;
 
 /* ----------------
-- 
2.34.1

From bbce295626e45d899e93a572d38074c40e8c9335 Mon Sep 17 00:00:00 2001
From: Evdokimov Ilia <[email-masked]>
Date: Wed, 16 Apr 2025 00:53:50 +0300
Subject: [PATCH v8 3/3] Show estimated lookups in EXPLAIN ouput for Memoize

Reviewed-by: David Rowley <[email-masked]>
Reviewed-by: Robert Haas <[email-masked]>
Reviewed-by: Andrei Lepikhov <[email-masked]>
Reviewed-by: Tom Lane <[email-masked]>
---
 src/backend/commands/explain.c          |  4 +++-
 src/backend/optimizer/plan/createplan.c | 10 +++++++---
 src/include/nodes/plannodes.h           |  3 +++
 3 files changed, 13 insertions(+), 4 deletions(-)

diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 85b22561aa6..6fcfa53a166 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -3633,15 +3633,17 @@ show_memoize_info(MemoizeState *mstate, List *ancestors, ExplainState *es)
 		if (es->format == EXPLAIN_FORMAT_TEXT)
 		{
 			ExplainIndentText(es);
-			appendStringInfo(es->str, "Estimates: capacity=%u distinct keys=%.0f hit ratio=%.2f%%\n",
+			appendStringInfo(es->str, "Estimates: capacity=%u distinct keys=%.0f lookups=%.0f hit ratio=%.2f%%\n",
 							((Memoize *) plan)->est_entries,
 							((Memoize *) plan)->est_unique_keys,
+							((Memoize *) plan)->lookups,
 							((Memoize *) plan)->hit_ratio * 100.0);
 		}
 		else
 		{
 			ExplainPropertyUInteger("Estimated Capacity", "", ((Memoize *) plan)->est_entries, es);
 			ExplainPropertyFloat("Estimated Distinct Lookup Keys", "", ((Memoize *) plan)->est_unique_keys, 0, es);
+			ExplainPropertyFloat("Estimated Lookups", "", ((Memoize *) plan)->lookups, 0, es);
 			ExplainPropertyFloat("Estimated Hit Ratio", "", ((Memoize *) plan)->hit_ratio * 100.0, 2, es);
 		}
 	}
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index ccb880158fe..47a092747d5 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -285,7 +285,8 @@ static Memoize *make_memoize(Plan *lefttree, Oid *hashoperators,
 							 Oid *collations, List *param_exprs,
 							 bool singlerow, bool binary_mode,
 							uint32 est_entries, Bitmapset *keyparamids,
-							double est_unique_keys, double hit_ratio);
+							double est_unique_keys, double hit_ratio,
+							double lookups);
 static WindowAgg *make_windowagg(List *tlist, WindowClause *wc,
 								 int partNumCols, AttrNumber *partColIdx, Oid *partOperators, Oid *partCollations,
 								 int ordNumCols, AttrNumber *ordColIdx, Oid *ordOperators, Oid *ordCollations,
@@ -1705,7 +1706,8 @@ create_memoize_plan(PlannerInfo *root, MemoizePath *best_path, int flags)
 	plan = make_memoize(subplan, operators, collations, param_exprs,
 						best_path->singlerow, best_path->binary_mode,
 						best_path->est_entries, keyparamids,
-						best_path->est_unique_keys, best_path->hit_ratio);
+						best_path->est_unique_keys, best_path->hit_ratio,
+						best_path->calls);

@@ -6639,7 +6641,8 @@ static Memoize *
 make_memoize(Plan *lefttree, Oid *hashoperators, Oid *collations,
 			 List *param_exprs, bool singlerow, bool binary_mode,
 			uint32 est_entries, Bitmapset *keyparamids,
-			double est_unique_keys, double hit_ratio)
+			double est_unique_keys, double hit_ratio,
+			double lookups)
 {
 	Memoize    *node = makeNode(Memoize);
 	Plan	   *plan = &node->plan;
@@ -6659,6 +6662,7 @@ make_memoize(Plan *lefttree, Oid *hashoperators, Oid *collations,
 	node->keyparamids = keyparamids;
 	node->est_unique_keys = est_unique_keys;
 	node->hit_ratio = hit_ratio;
+	node->lookups = lookups;
 
 	return node;
 }
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 4354d4f66d3..6eb08866304 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -1072,6 +1072,9 @@ typedef struct Memoize
 
 	/* Estimated cache hit ratio. Kept for EXPLAIN */
 	double		hit_ratio;
+
+	/* Estimated number of lookups. Kept for EXPLAIN */
+	double		lookups;
 } Memoize;
 
 /* ----------------
-- 
2.34.1

---

From: Maciek Sakrejda <[email-masked]>
Date: Wed, 16 Apr 2025 09:09:41 -0700

> in using the above format.

This kind of thing is nice to have in the text format, but it's really
nice to have when working with structured formats. Currently, to get a
loops estimate for the inner node, I need to find the parent, find the
outer node in the parent's children, and get that sibling's Plan Rows
(I think). It'd be nice to make things like that easier.

Thanks,
Maciek

---

From: David Rowley <[email-masked]>
Date: Fri, 18 Apr 2025 09:29:37 +1200

> know if you have any feedback or further suggestions.

While this is fresh, as I might forget before the July CF...

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.

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.

David

---

From: Ilia Evdokimov <[email-masked]>
Date: Sun, 27 Apr 2025 22:58:52 +0300

Sorry for the delayed reply.

> had was to only * 100 for text format, but that might be more

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?

> unnecessary and just means more brain space being used to maintain a

Agree.

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

---

From: Ilia Evdokimov <[email-masked]>
Date: Thu, 1 May 2025 15:22:34 +0300

> context. What do you think about this approach?

I attached updated v9 patch with the suggested changes. The updated line 
in the EXPLAIN looks like this:

Text format:
     Estimates: capacity=1 distinct keys=1 lookups=2 hit percent=50.00%

Non-text format:
     Estimated Capacity: 1
     Estimated Distinct Lookup Keys: 1
     Estimated Lookups: 2
     Estimated Hit Percent: 50.00

Any suggestions?

--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC.
From eff0cb420c922343af9e09e58cec7b5be19c35e4 Mon Sep 17 00:00:00 2001
From: Evdokimov Ilia <[email-masked]>
Date: Thu, 1 May 2025 15:20:07 +0300
Subject: [PATCH v9] Expose cache hit statistics in Memoize node EXPLAIN
 output.

This patch adds additional information to the Memoize node's EXPLAIN output:
estimated cache capacity, number of distinct keys, total lookups, and
cache hit percentage.

Reviewed-by: David Rowley <[email-masked]>
Reviewed-by: Robert Haas <[email-masked]>
Reviewed-by: Andrei Lepikhov <[email-masked]>
Reviewed-by: Tom Lane <[email-masked]>
---
 src/backend/commands/explain.c          | 20 ++++++++++++++++++++
 src/backend/optimizer/path/costsize.c   |  6 ++++++
 src/backend/optimizer/plan/createplan.c | 15 ++++++++++++---
 src/backend/optimizer/util/pathnode.c   | 12 ++++++++++++
 src/include/nodes/pathnodes.h           |  3 +++
 src/include/nodes/plannodes.h           | 12 ++++++++++++
 6 files changed, 65 insertions(+), 3 deletions(-)

diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 786ee865f14..3a5b436b561 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -3628,6 +3628,26 @@ show_memoize_info(MemoizeState *mstate, List *ancestors, ExplainState *es)
 	ExplainPropertyText("Cache Key", keystr.data, es);
 	ExplainPropertyText("Cache Mode", mstate->binary_mode ? "binary" : "logical", es);
 
+	if (es->costs)
+	{
+		if (es->format == EXPLAIN_FORMAT_TEXT)
+		{
+		 	ExplainIndentText(es);
+		 	appendStringInfo(es->str, "Estimates: capacity=%u distinct keys=%.0f lookups=%.0f hit percent=%.2f%%\n",
+		 					((Memoize *) plan)->est_entries,
+		 					((Memoize *) plan)->est_unique_keys,
+		 					((Memoize *) plan)->est_calls,
+		 					((Memoize *) plan)->est_hit_ratio * 100.0);
+		}
+		else
+		{
+			ExplainPropertyUInteger("Estimated Capacity", NULL, ((Memoize *) plan)->est_entries, es);
+			ExplainPropertyFloat("Estimated Distinct Lookup Keys", NULL, ((Memoize *) plan)->est_unique_keys, 0, es);
+			ExplainPropertyFloat("Estimated Lookups", NULL, ((Memoize *) plan)->est_calls, 0, es);
+			ExplainPropertyFloat("Estimated Hit Percent", NULL, ((Memoize *) plan)->est_hit_ratio * 100.0, 2, es);
+		}
+	}
+
 	pfree(keystr.data);
 
 	if (!es->analyze)
index 60b0fcfb6be..226a7861543 100644
 
@@ -2621,6 +2624,9 @@ cost_memoize_rescan(PlannerInfo *root, MemoizePath *mpath,
 
+	/* Remember cache hit ratio for a potential EXPLAIN later */
+	mpath->est_hit_ratio = hit_ratio;
+
 	Assert(hit_ratio >= 0 && hit_ratio <= 1.0);
 
 	/*
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index a8f22a8c154..b3d3fac187f 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -284,7 +284,9 @@ static Material *make_material(Plan *lefttree);
 static Memoize *make_memoize(Plan *lefttree, Oid *hashoperators,
 							 Oid *collations, List *param_exprs,
 							 bool singlerow, bool binary_mode,
-							 uint32 est_entries, Bitmapset *keyparamids);
+							uint32 est_entries, Bitmapset *keyparamids,
+							double est_unique_keys, double est_hit_ratio,
+							double est_calls);
 static WindowAgg *make_windowagg(List *tlist, WindowClause *wc,
 								 int partNumCols, AttrNumber *partColIdx, Oid *partOperators, Oid *partCollations,
 								 int ordNumCols, AttrNumber *ordColIdx, Oid *ordOperators, Oid *ordCollations,
@@ -1703,7 +1705,9 @@ create_memoize_plan(PlannerInfo *root, MemoizePath *best_path, int flags)
 
+						best_path->est_unique_keys, best_path->est_hit_ratio,
+						best_path->calls);

@@ -6636,7 +6640,9 @@ materialize_finished_plan(Plan *subplan)
 static Memoize *
 make_memoize(Plan *lefttree, Oid *hashoperators, Oid *collations,
 			 List *param_exprs, bool singlerow, bool binary_mode,
-			 uint32 est_entries, Bitmapset *keyparamids)
+			uint32 est_entries, Bitmapset *keyparamids,
+			double est_unique_keys, double est_hit_ratio,
+			double est_calls)
 {
 	Memoize    *node = makeNode(Memoize);
 	Plan	   *plan = &node->plan;
@@ -6654,6 +6660,9 @@ make_memoize(Plan *lefttree, Oid *hashoperators, Oid *collations,
 	node->binary_mode = binary_mode;
 	node->est_entries = est_entries;
 	node->keyparamids = keyparamids;
+	node->est_unique_keys = est_unique_keys;
+	node->est_hit_ratio = est_hit_ratio;
+	node->est_calls = est_calls;
 
 	return node;
 }
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index 93e73cb44db..7e35421f410 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -1701,6 +1701,18 @@ create_memoize_path(PlannerInfo *root, RelOptInfo *rel, Path *subpath,
 	Assert(enable_memoize);
 	pathnode->path.disabled_nodes = subpath->disabled_nodes;
 
+	/*
+	 * Estimated number of distinct memoization keys,
+	 * computed using estimate_num_groups()
+	 */
+	pathnode->est_unique_keys = 0;
+
+	/*
+	 * The estimated cache hit ratio will calculated later
+	 * by cost_memoize_rescan().
+	 */
+	pathnode->est_hit_ratio = 0;
+
 	/*
 	 * Add a small additional charge for caching the first entry.  All the
 	 * harder calculations for rescans are performed in cost_memoize_rescan().
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 011e5a811c3..938e4c43a81 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -2138,6 +2138,9 @@ typedef struct MemoizePath
 	uint32		est_entries;	/* The maximum number of entries that the
 								 * planner expects will fit in the cache, or 0
 								 * if unknown */
+	double		est_unique_keys;	/* Estimated number of distinct memoization keys,
+								 * used for cache size evaluation. Kept for EXPLAIN */
+	double		est_hit_ratio;	/* Estimated cache hit ratio. Kept for EXPLAIN */
 } MemoizePath;
 
 /*
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 658d76225e4..26a4c78afe8 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -1063,6 +1063,18 @@ typedef struct Memoize
 
+	double		est_hit_ratio;
+
+	/* Estimated number of rescans. Kept for EXPLAIN */
+	double		est_calls;
 } Memoize;
 
 /* ----------------
-- 
2.34.1

---

From: Andrei Lepikhov <[email-masked]>
Date: Thu, 1 May 2025 15:37:52 +0200

>      Estimated Hit Percent: 50.00
I still think that two meaningful digits are enough for an EXPLAIN. We 
usually need to estimate the tuple set's size, not a precise number of 
tuples or groups. And definitely, not an arbitrarily chosen two digits 
after the point. So, IMO, good examples may look like the following:
50%, 5.0%, 0.00051%.

-- 
regards, Andrei Lepikhov

---

From: Ilia Evdokimov <[email-masked]>
Date: Thu, 1 May 2025 22:10:56 +0300

> after the point. So, IMO, good examples may look like the following:

The idea is not bad. But I think it might be better to raise this in a 
new thread, because this is not the only place [0] where we might want 
to output numbers like that. We could also discuss there what else might 
benefit from such formatting.

[0]: 
https://www.postgresql.org/message-id/3365160.1739385615%40sss.pgh.pa.us

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

---

From: Ilia Evdokimov <[email-masked]>
Date: Fri, 4 Jul 2025 11:30:53 +0300

On 01.05.2025 15:22, Ilia Evdokimov wrote:

> I attached updated v9 patch with the suggested changes. The updated 
> line in the EXPLAIN looks like this:
>
> Text format:

I attached rebased v10 patch on 5a6c39b.

--
Best Regards,
Ilia Evdokimov,
Tantor Labs LLC.
From 48cacc9b59eaa73c69a891e0cf6967f1a3f3c398 Mon Sep 17 00:00:00 2001
From: Ilia Evdokimov <[email-masked]>
Date: Fri, 4 Jul 2025 11:24:56 +0300
Subject: [PATCH v10] Expose cache hit statistics in Memoize node EXPLAIN
 output.

diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 7e2792ead71..561eb8957b8 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -3616,6 +3616,26 @@ show_memoize_info(MemoizeState *mstate, List *ancestors, ExplainState *es)
 	ExplainPropertyText("Cache Key", keystr.data, es);
 	ExplainPropertyText("Cache Mode", mstate->binary_mode ? "binary" : "logical", es);
 
+	if (es->costs)
+	{
 
 	if (!es->analyze)
index 3d44815ed5a..d5f2cc15817 100644

 
 	/*
index 0b61aef962c..4f28a2d906c 100644

 
@@ -6699,7 +6703,9 @@ materialize_finished_plan(Plan *subplan)
 static Memoize *
@@ -6717,6 +6723,9 @@ make_memoize(Plan *lefttree, Oid *hashoperators, Oid *collations,
 
 	return node;
 }
index e0192d4a491..a68a32c89cf 100644
 
+	/*
index 6567759595d..5bb7451e160 100644
@@ -2135,6 +2135,9 @@ typedef struct MemoizePath
 
 /*
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 4f59e30d62d..598c0d5eb1f 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -1056,6 +1056,18 @@ typedef struct Memoize

 /* ----------------
-- 
2.34.1

---

From: Andrei Lepikhov <[email-masked]>
Date: Mon, 7 Jul 2025 15:49:58 +0200

> I attached rebased v10 patch on 5a6c39b.
Exposing internal information about the estimation of the number of 
groups in the Memoise node, shouldn't we do the same in even more vague 
cases, such as IncrementalSort? For example, in [1] (see its 
attachment), I observe that IncrementalSort is considerably better than 
Sort, but has a larger cost. It would be helpful to understand if an 
incorrect ngroups estimation causes this.

[1] 
https://www.postgresql.org/message-id/[email-masked]

-- 
regards, Andrei Lepikhov

---

From: Ilia Evdokimov <[email-masked]>
Date: Tue, 8 Jul 2025 16:06:05 +0300

On 07.07.2025 16:49, Andrei Lepikhov wrote:

> groups in the Memoise node, shouldn't we do the same in even more 
> vague cases, such as IncrementalSort? For example, in [1] (see its 
> attachment), I observe that IncrementalSort is considerably better 
> than Sort, but has a larger cost. It would be helpful to understand if 
> an incorrect ngroups estimation causes this.

LGTM.

If we still don't expose any information for nodes like IncrementalSort 
that would help explain why the planner chose them, then I fully agree - 
we definitely should add it.

I suggest discussing that in a separate thread.

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

---

From: David Rowley <[email-masked]>
Date: Tue, 22 Jul 2025 10:17:36 +1200

> I attached rebased v10 patch on 5a6c39b.

I've gone over this and made some cosmetic adjustments. A few
adjustments to the comments and used Cardinality rather than double
for some data types. I also moved the MemoizePath.calls field down
below est_entries so that est_entries would take up some padding
space. This saves 8 bytes of struct, and IMO, improves the logical
order of fields. I renamed "calls" to "est_calls" so it's more aligned
with the new fields being added by this patch.

The final thing which I'm not so sure about is the EXPLAIN format.
Currently it looks like:

   ->  Memoize  (cost=0.43..0.46 rows=1 width=4) (actual
time=0.000..0.000 rows=1.00 loops=1000000)
         Cache Key: t.a
         Cache Mode: logical
         Estimates: capacity=10010 distinct keys=10010 lookups=1000000
hit ratio=99.00%
         Hits: 989999  Misses: 10001  Evictions: 0  Overflows: 0
Memory Usage: 1016kB

This format for the Estimates copies the "Buffers:" format for putting
multiple sub-values under a single heading.  However, we don't seem
very consistent with this as JIT has a similar need but formats things
another way, i.e.:

 JIT:
   Functions: 6
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 0.473 ms (Deform 0.112 ms), Inlining 0.000 ms,
Optimization 0.701 ms, Emission 5.778 ms, Total 6.952 ms

We could get rid of the "Estimates" heading and just prefix each value
with "Estimated", as in:

   ->  Memoize  (cost=0.43..0.46 rows=1 width=4) (actual
time=0.000..0.000 rows=1.00 loops=1000000)
         Cache Key: t.a
         Cache Mode: logical
         Estimated capacity: 10010  Estimated distinct keys: 10010
Estimated lookups: 1000000  Estimated hit ratio: 99.00%
         Hits: 989999  Misses: 10001  Evictions: 0  Overflows: 0
Memory Usage: 1016kB
         Buffers: shared hit=30004

That removes the dilemma about which example to follow, but it's more verbose.

Does anyone have any opinions on this?

v11 patch attached.

David

---

From: Andrei Lepikhov <[email-masked]>
Date: Tue, 22 Jul 2025 16:35:38 +0200

> with the new fields being added by this patch.
Looks good

> That removes the dilemma about which example to follow, but it's more verbose.
The 'Buffers:' way looks more natural to me. I don't like duplicated 
text in the explain format - it is already cluttered by multiple 
unnecessary elements that distract attention from the problematic plan 
elements, such as unplaggable costs output if we only need row 
predictions, '.00' in estimations, etc.
However, at first, I'd consider how it could be added to the 
IncrementalSort and HashJoin. The number of estimated groups/buckets may 
also provide some insights into the planner's decision.

Will you add the ExplainOpenGroup call to the final version of the patch?

-- 
regards, Andrei Lepikhov

---

From: David Rowley <[email-masked]>
Date: Wed, 23 Jul 2025 12:11:09 +1200

> predictions, '.00' in estimations, etc.

Seems logical.

> also provide some insights into the planner's decision.

Sounds like another patch for another thread.

> Will you add the ExplainOpenGroup call to the final version of the patch?

I'm leaning towards not doing that as a reader might expect all the
"Estimates" to be within that group, but the estimated cost and row
counts won't be in there. Maybe it's possible to circumvent that
expectation by naming the group something like "MemoizeEstimates", but
IMO, that seems excessive for 4 properties.

Do you have an argument in mind to support adding the group?

David

---

From: Andrei Lepikhov <[email-masked]>
Date: Wed, 23 Jul 2025 09:00:44 +0200

> Sounds like another patch for another thread.
Maybe, it is up to you

> Do you have an argument in mind to support adding the group?
I have not. I just thought that grouping would be helpful for an explain
parser.

--
regards, Andrei Lepikhov

---

From: Ilia Evdokimov <[email-masked]>
Date: Wed, 23 Jul 2025 14:07:56 +0300

>> predictions, '.00' in estimations, etc.

+1

> IMO, that seems excessive for 4 properties.

I agree. I would consider adding a group if we displayed information in 
a looped format, like for Workers, or if we had some particularly useful 
data for parsers - for example, timings or memory usage. But for four 
estimates, grouping seems unnesseray.

Given that, patch v11 still looks like the most appropriate version to me.

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

---

From: David Rowley <[email-masked]>
Date: Tue, 29 Jul 2025 15:25:50 +1200

> Given that, patch v11 still looks like the most appropriate version to me.

I spent a bit more time cleaning a few things up in this. With all the
complexity from the extra fields, I felt that a round of adjusting the
"double" types so they use the "Cardinality" typedef where appropriate
was in order. costsize.c isn't very good at doing that, but I didn't
see that as a reason to make the problem worse. The only other change
was in explain.c where I added a local "Memoize *" variable rather
than casting the "plan" variable to the subclass type in each usage.

I understand not everyone got what they wanted here, but I hope what's
been added is ok for the majority of people.

Thanks for working on this Ilia and Lukas.

David

---

From: Lukas Fittl <[email-masked]>
Date: Mon, 28 Jul 2025 20:59:35 -0700

> been added is ok for the majority of people.

For what its worth, the last version you posted (v11) looks good to me as
well, and +1 on the "Estimates:" style that puts them all on one line in
text mode.

I think this will help a lot in understanding Memoize's impact on plans
better, thanks everyone for pushing this forward!

Thanks,
Lukas

-- 
Lukas Fittl