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 <[email protected]> Reviewed-by: David Rowley <[email protected]> Reviewed-by: Yilin Zhang <[email protected]> Discussion: https://postgr.es/m/CA+COZaAHZT7T2G2F0-drdQB+StWJxOzqeDxp=esp+yuiy-k...@mail.gmail.com Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/f8da64e6ddfd5f33f1d70b3ae6f8561f1de09368 Modified Files -------------- 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(-)
