From 18052d2d3ac54e12042673e35ca357ae11f99093 Mon Sep 17 00:00:00 2001 From: Evdokimov Ilia 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 Reviewed-by: Robert Haas Reviewed-by: Andrei Lepikhov Reviewed-by: Tom Lane --- 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