v8-0002-Add-Estimated-Hit-Ratio-for-Memoize-plan-nodes-in.patch

From 18052d2d3ac54e12042673e35ca357ae11f99093 Mon Sep 17 00:00:00 2001
From: Evdokimov Ilia <ilya.evdokimov@tantorlabs.com>
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 <dgrowleyml@gmail.com>
Reviewed-by: Robert Haas <robertmhaas@gmail.com>
Reviewed-by: Andrei Lepikhov <lepihov@gmail.com>
Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
---
 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);
 		}
 	}
 
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);
 
 	copy_generic_path_info(&plan->plan, (Path *) best_path);
 
@@ -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 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