Re: why memoize is not used for correlated subquery

út 28. 5. 2024 v 9:48 odesílatel David Rowley <dgrowleyml(at)gmail(dot)com> napsal:

> On Tue, 28 May 2024 at 19:31, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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.
>

Thank you for explanation

Pavel

>
> David
>
> [1]
> https://www.postgresql.org/message-id/CAApHDvpGX7RN%2Bsh7Hn9HWZQKp53SjKaL%3DGtDzYheHWiEd-8moQ%40mail.gmail.com
>