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 <ilya.evdoki...@tantorlabs.com>
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 <dgrowle...@gmail.com>
Reviewed-by: Robert Haas <robertmh...@gmail.com>
Reviewed-by: Andrei Lepikhov <lepi...@gmail.com>
Reviewed-by: Tom Lane <t...@sss.pgh.pa.us>
---
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)
+ {
+ if (es->format == EXPLAIN_FORMAT_TEXT)
+ {
+ ExplainIndentText(es);
+ appendStringInfo(es->str, "Estimates: capacity=%u distinct keys=%.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);
+ }
+ }
+
pfree(keystr.data);
if (!es->analyze)
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index 60b0fcfb6be..f72319d903c 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 a8f22a8c154..a1456c9014d 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, Bitmapset *keyparamids,
+ double est_unique_keys);
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, keyparamids,
+ best_path->est_unique_keys);
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, Bitmapset *keyparamids,
+ double est_unique_keys)
{
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..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;
+ /*
+ * 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 bb678bdcdcd..07d97dc0b5b 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -2138,6 +2138,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 18052d2d3ac54e12042673e35ca357ae11f99093 Mon Sep 17 00:00:00 2001
From: Evdokimov Ilia <ilya.evdoki...@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 <dgrowle...@gmail.com>
Reviewed-by: Robert Haas <robertmh...@gmail.com>
Reviewed-by: Andrei Lepikhov <lepi...@gmail.com>
Reviewed-by: Tom Lane <t...@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
From bbce295626e45d899e93a572d38074c40e8c9335 Mon Sep 17 00:00:00 2001
From: Evdokimov Ilia <ilya.evdoki...@tantorlabs.com>
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 <dgrowle...@gmail.com>
Reviewed-by: Robert Haas <robertmh...@gmail.com>
Reviewed-by: Andrei Lepikhov <lepi...@gmail.com>
Reviewed-by: Tom Lane <t...@sss.pgh.pa.us>
---
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);
copy_generic_path_info(&plan->plan, (Path *) best_path);
@@ -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
