public inbox for [email protected]
help / color / mirror / Atom feedFrom: Lukas Fittl <[email protected]>
To: PostgreSQL Hackers <[email protected]>
Cc: David Rowley <[email protected]>
Subject: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment
Date: Sat, 4 Mar 2023 16:20:59 -0800
Message-ID: <CAP53Pky29GWAVVk3oBgKBDqhND0BRBN6yTPeguV_qSivFL5N_g@mail.gmail.com> (raw)
Hi,
I was debugging a planner problem on Postgres 14.4 the other day - and the
involved "bad" plan was including Memoize - though I don't necessarily
think that Memoize is to blame (and this isn't any of the problems recently
fixed in Memoize costing).
However, what I noticed whilst trying different ways to fix the plan, is
that the Memoize output was a bit hard to reason about - especially since
the plan involving Memoize was expensive to run, and so I was mostly
running EXPLAIN without ANALYZE to look at the costing.
Here is an example of the output I was looking at:
-> Nested Loop (cost=1.00..971672.56 rows=119623 width=0)
-> Index Only Scan using table1_idx on table1
(cost=0.43..372676.50 rows=23553966 width=8)
-> Memoize (cost=0.57..0.61 rows=1 width=8)
Cache Key: table1.table2_id
Cache Mode: logical
-> Index Scan using table2_idx on table2
(cost=0.56..0.60 rows=1 width=8)
Index Cond: (id = table1.table2_id)
The other plan I was comparing with (that I wanted the planner to choose
instead), had a total cost of 1,451,807.35 -- and so I was trying to figure
out why the Nested Loop was costed as 971,672.56.
Simple math makes me expect the Nested Loop should roughly have a total
cost of14,740,595.76 here (372,676.50 + 23,553,966 * 0.61), ignoring a lot
of the smaller costs. Thus, in this example, it appears Memoize made the
plan cost significantly cheaper (roughly 6% of the regular cost).
Essentially this comes down to the "cost reduction" performed by Memoize
only being implicitly visible in the Nested Loop's total cost - and with
nothing useful on the Memoize node itself - since the rescan costs are not
shown.
I think explicitly adding the estimated cache hit ratio for Memoize nodes
might make this easier to reason about, like this:
-> Memoize (cost=0.57..0.61 rows=1 width=8)
Cache Key: table1.table2_id
Cache Mode: logical
Cache Hit Ratio Estimated: 0.94
Alternatively (or in addition) we could consider showing the "ndistinct"
value that is calculated in cost_memoize_rescan - since that's the most
significant contributor to the cache hit ratio (and you can influence that
directly by improving the ndistinct statistics).
See attached a patch that implements showing the cache hit ratio as a
discussion starter.
I'll park this in the July commitfest for now.
Thanks,
Lukas
--
Lukas Fittl
Attachments:
[application/octet-stream] v1-0001-Add-Estimated-Cache-Hit-Ratio-for-Memoize-plan-no.patch (5.7K, ../CAP53Pky29GWAVVk3oBgKBDqhND0BRBN6yTPeguV_qSivFL5N_g@mail.gmail.com/3-v1-0001-Add-Estimated-Cache-Hit-Ratio-for-Memoize-plan-no.patch)
download | inline diff:
From 7eaed8f1a059bed00dfd4625931ece475b9c3451 Mon Sep 17 00:00:00 2001
From: Lukas Fittl <[email protected]>
Date: Sat, 4 Mar 2023 15:44:29 -0800
Subject: [PATCH v1] Add Estimated Cache Hit Ratio for Memoize plan nodes in
EXPLAIN
Memoize can substantially reduce the costs of certain Nested Loops, but
its hard to see this clearly, since the often significantly discounted
rescan costs are not shown in EXPLAIN.
To help understand Memoize's impact better, remember the calculated
estimated cache hit ratio, and show it in EXPLAIN output.
---
src/backend/commands/explain.c | 7 +++++++
src/backend/optimizer/path/costsize.c | 3 +++
src/backend/optimizer/plan/createplan.c | 10 +++++++---
src/backend/optimizer/util/pathnode.c | 5 +++++
src/include/nodes/pathnodes.h | 1 +
src/include/nodes/plannodes.h | 3 +++
6 files changed, 26 insertions(+), 3 deletions(-)
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index e57bda7b62..58fda99a69 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -3167,6 +3167,8 @@ 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)
+ ExplainPropertyFloat("Cache Hit Ratio Estimated", "", ((Memoize *) plan)->hit_ratio, 2, es);
}
else
{
@@ -3174,6 +3176,11 @@ show_memoize_info(MemoizeState *mstate, List *ancestors, ExplainState *es)
appendStringInfo(es->str, "Cache Key: %s\n", keystr.data);
ExplainIndentText(es);
appendStringInfo(es->str, "Cache Mode: %s\n", mstate->binary_mode ? "binary" : "logical");
+ if (es->costs)
+ {
+ ExplainIndentText(es);
+ appendStringInfo(es->str, "Cache Hit Ratio Estimated: %0.2f\n", ((Memoize *) plan)->hit_ratio);
+ }
}
pfree(keystr.data);
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index 7918bb6f0d..a0fd3f8965 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -2600,6 +2600,9 @@ cost_memoize_rescan(PlannerInfo *root, MemoizePath *mpath,
*/
startup_cost += cpu_tuple_cost;
+ /* Remember cache hit ratio for a potential EXPLAIN later */
+ mpath->hit_ratio = hit_ratio;
+
*rescan_startup_cost = startup_cost;
*rescan_total_cost = total_cost;
}
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index fa09a6103b..4c403cb081 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -280,7 +280,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 hit_ratio);
static WindowAgg *make_windowagg(List *tlist, Index winref,
int partNumCols, AttrNumber *partColIdx, Oid *partOperators, Oid *partCollations,
int ordNumCols, AttrNumber *ordColIdx, Oid *ordOperators, Oid *ordCollations,
@@ -1691,7 +1692,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->hit_ratio);
copy_generic_path_info(&plan->plan, (Path *) best_path);
@@ -6528,7 +6530,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 hit_ratio)
{
Memoize *node = makeNode(Memoize);
Plan *plan = &node->plan;
@@ -6546,6 +6549,7 @@ make_memoize(Plan *lefttree, Oid *hashoperators, Oid *collations,
node->binary_mode = binary_mode;
node->est_entries = est_entries;
node->keyparamids = keyparamids;
+ node->hit_ratio = hit_ratio;
return node;
}
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index d749b50578..d4186784c5 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -1624,6 +1624,11 @@ create_memoize_path(PlannerInfo *root, RelOptInfo *rel, Path *subpath,
*/
pathnode->est_entries = 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 d61a62da19..1b7cab3bc5 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -1962,6 +1962,7 @@ 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 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 659bd05c0c..7966062a32 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -923,6 +923,9 @@ typedef struct Memoize
/* paramids from param_exprs */
Bitmapset *keyparamids;
+
+ /* Estimated cache hit ratio, kept for EXPLAIN */
+ double hit_ratio;
} Memoize;
/* ----------------
--
2.34.0
view thread (29+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment
In-Reply-To: <CAP53Pky29GWAVVk3oBgKBDqhND0BRBN6yTPeguV_qSivFL5N_g@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox