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
optimizationfences?
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.
David
[1] https://www.postgresql.org/message-id/CAApHDvpGX7RN%2Bsh7Hn9HWZQKp53SjKaL%3DGtDzYheHWiEd-8moQ%40mail.gmail.com