postgres.git / summary / log / commit / refs
commit f8da64e6ddfd5f33f1d70b3ae6f8561f1de09368
Author: Richard Guo <rguo@postgresql.org>
Date: Mon Nov 02 01:29:02 2026 +0000
Make cache keys of other Params a Memoize subplan depends on
A Memoize node purges its whole cache whenever a Param changes which
the cache keys don't determine, such as an upper-level Param used in a
base qual of the memoized relation. Under a correlated subquery, such
a Param changes on every invocation of the subplan, so nothing in the
cache survives from one invocation to the next.
To improve that, make cache keys of the upper-level Params found in
the memoized relation's base quals and targetlist, and on the inner
side of the join clauses, as is already done for Params buried inside
a cache key expression. Cache entries then survive across invocations
with the same Param value.
This doesn't pay off when the Param's values don't recur across
invocations. Cache entries for earlier values are then kept rather
than purged, and they only take up memory until the cache fills up and
evicts them. The planner can't tell the two cases apart, as it costs
Memoize per invocation starting from an empty cache. We accept that,
since the cache's memory is bounded by its size limit either way, and
evicting a stale entry costs about the same as purging it.
This is only an optimization, as any Param we don't find here still
purges the cache when it changes. Hence we needn't track down every
last place a Param can appear, and a Param whose type can't be hashed
is simply left to the purging.
Author: Richard Guo <guofenglinux@gmail.com>
Reviewed-by: David Rowley <dgrowleyml@gmail.com>
Reviewed-by: Yilin Zhang <jiezhilove@126.com>
Discussion: https://postgr.es/m/CA+COZaAHZT7T2G2F0-drdQB+StWJxOzqeDxp=ESp+YUiy-kOaQ@mail.gmail.com
src/backend/optimizer/path/joinpath.c | 39 +++++++++++++++++++++++++++++------
src/test/regress/expected/memoize.out | 10 ++++-----
src/test/regress/sql/memoize.sql | 6 +++---
3 files changed, 41 insertions(+), 14 deletions(-)
diff --git a/src/backend/optimizer/path/joinpath.c b/src/backend/optimizer/path/joinpath.c
index 43e4fa56d39..b690deb6275 100644
--- a/src/backend/optimizer/path/joinpath.c
+++ b/src/backend/optimizer/path/joinpath.c
@@ -589,15 +589,42 @@ paraminfo_get_equal_hashops(PlannerInfo *root, ParamPathInfo *param_info,
}
/*
- * A Param buried inside a larger cache key expression isn't determined by
- * the key's value, so create_memoize_plan() arranges for the cache to be
- * purged whenever it changes, which commonly happens on every rescan of
- * the enclosing plan. We're better off making a cache key of the Param
- * by itself so that the cached results survive. This is only an
- * optimization, so we needn't refuse to memoize when one isn't hashable.
+ * A Param which the inner scan uses other than as a cache key by itself,
+ * whether buried inside a larger cache key expression or used elsewhere,
+ * isn't determined by the cache keys, so create_memoize_plan() arranges
+ * for the cache to be purged whenever it changes, which commonly happens
+ * on every rescan of the enclosing plan. We're better off making a cache
+ * key of the Param by itself so that the cached results survive. This is
+ * only an optimization, so we needn't track down every last such Param,
+ * nor refuse to memoize when one isn't hashable.
*/
params = NIL;
(void) pull_exec_params_walker((Node *) *param_exprs, ¶ms);
+ foreach(lc, innerrel->baserestrictinfo)
+ {
+ RestrictInfo *rinfo = (RestrictInfo *) lfirst(lc);
+
+ (void) pull_exec_params_walker((Node *) rinfo->clause, ¶ms);
+ }
+ (void) pull_exec_params_walker((Node *) innerrel->reltarget->exprs, ¶ms);
+
+ /*
+ * Only the outer side of each ppi_clause became a cache key above, so the
+ * inner sides must be examined here.
+ */
+ if (param_info != NULL)
+ {
+ foreach(lc, param_info->ppi_clauses)
+ {
+ RestrictInfo *rinfo = (RestrictInfo *) lfirst(lc);
+ OpExpr *opexpr = (OpExpr *) rinfo->clause;
+
+ (void) pull_exec_params_walker(rinfo->outer_is_left ?
+ (Node *) lsecond(opexpr->args) :
+ (Node *) linitial(opexpr->args),
+ ¶ms);
+ }
+ }
foreach(lc, params)
(void) memoize_add_cache_key((Node *) lfirst(lc), param_exprs,
diff --git a/src/test/regress/expected/memoize.out b/src/test/regress/expected/memoize.out
index f6638431257..819cdf41298 100644
--- a/src/test/regress/expected/memoize.out
+++ b/src/test/regress/expected/memoize.out
@@ -415,9 +415,9 @@ ON t1.a = t2.a;', false);
DROP TABLE prt;
RESET enable_partitionwise_join;
--- Exercise Memoize code that flushes the cache when a parameter changes which
--- is not part of the cache key.
--- Ensure we get a Memoize plan
+-- Ensure Params which are part of the base quals are also added as a cache
+-- key.
+-- Ensure we get a Memoize plan with the Param as a cache key
EXPLAIN (COSTS OFF)
SELECT unique1 FROM tenk1 t0
WHERE unique1 < 3
@@ -435,8 +435,8 @@ WHERE unique1 < 3
-> Index Scan using tenk1_hundred on tenk1 t2
Filter: (t0.two <> four)
-> Memoize
- Cache Key: t2.hundred
- Cache Mode: logical
+ Cache Key: t2.hundred, t0.ten
+ Cache Mode: binary
-> Index Scan using tenk1_unique1 on tenk1 t1
Index Cond: (unique1 = t2.hundred)
Filter: (t0.ten = twenty)
diff --git a/src/test/regress/sql/memoize.sql b/src/test/regress/sql/memoize.sql
index bb5281f5f39..bf7ea5c05af 100644
--- a/src/test/regress/sql/memoize.sql
+++ b/src/test/regress/sql/memoize.sql
@@ -198,10 +198,10 @@ DROP TABLE prt;
RESET enable_partitionwise_join;
--- Exercise Memoize code that flushes the cache when a parameter changes which
--- is not part of the cache key.
+-- Ensure Params which are part of the base quals are also added as a cache
+-- key.
--- Ensure we get a Memoize plan
+-- Ensure we get a Memoize plan with the Param as a cache key
EXPLAIN (COSTS OFF)
SELECT unique1 FROM tenk1 t0
WHERE unique1 < 3
[parent: 708eb515812b]