On Tue, 28 May 2024 at 19:31, Pavel Stehule <pavel.stehule@gmail.com> wrote: > My question is - does memoize support subqueries? And can be enhanced to support this exercise without LATERAL and optimization fences?
It's only currently considered for parameterized nested loop joins, not for subplans.
I wrote a bit about this in [1] and there's even a patch. The problem with it is that we plan subqueries and generate an actual plan before planning the outer query. This means we don't have an ndistinct estimate for the parameters to the subquery when we plan it, therefore we can't tell if Memoize is a good choice or not. It isn't a good choice if each set of parameters the subplan is called with is unique. That would always be a cache miss and would only result in making the query run more slowly.
I imagined making this work by delaying the plan creation for subqueries until the same time as create_plan() for the outer query. If we have a Path with and without a Memoize node, at some point after planning the outer query, we can choose which Path is the cheapest based on the ndistinct estimate for the parameters.