Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables.

On Wed, Aug 28, 2024 at 7:58 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
> On Wed, 28 Aug 2024 at 11:37, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Oh, scratch that, I see you mean this is an additional way to do it
> > not the only way to do it. But I'm confused why it works for
> > t1.two+1 AS c1
> > but not
> > t1.two+t2.two AS c1
> > Those ought to look pretty much the same for this purpose.
>
> The bms_overlap(pull_varnos(rcon->root, newnode), rcon->relids) test
> is false with t1.two+1. Looks like there needs to be a Var from t2
> for the bms_overlap to be true

Exactly. What Tom's patch does is that if the expression contains
Vars/PHVs that belong to the subquery, and does not contain any
non-strict constructs, then it can escape being wrapped.

In expression 't1.two+t2.two', 't2.two' is a Var that belongs to the
subquery, and '+' is strict, so it can escape being wrapped.

The expression 't1.two+1' does not meet these conditions, so it is
wrapped into a PHV, and the PHV contains lateral reference to t1,
which results in a nestloop join with a parameterized inner path.
That's why Memoize can work in this query.

Thanks
Richard