public inbox for [email protected]  

help / color / mirror / Atom feed
From: Richard Guo <[email protected]>
To: David Rowley <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: nikhil raj <[email protected]>
Cc: [email protected]
Subject: Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables.
Date: Wed, 28 Aug 2024 15:31:27 +0800
Message-ID: <CAMbWs49gVdBxrjvz82Tqs9UL+ZDNV5cx85ZURhfxUaz-0wz-pQ@mail.gmail.com> (raw)
In-Reply-To: <CAApHDvr2v7iwMUsCQKxUPSSzTGuBOx1_e0hyEA8VLy1or35JVA@mail.gmail.com>
References: <CALNXOfqDr5jVRZHThyXyNftDuezjxOJB1mqw=4x1T8YBibi6uw@mail.gmail.com>
	<CAG1ps1w61XD1LxUf3EBCZ=VR3S_orRj_dk6cxmO5KusBVyrgMg@mail.gmail.com>
	<CAAvG1pChk3W9MWkK-_JPyUdThSoJhv4MueH5i8cZS2T_HUP5dA@mail.gmail.com>
	<CAG1ps1xvnTZceKK24OUfMKLPvDP2vjT-d+F2AOCWbw_v3KeEgg@mail.gmail.com>
	<[email protected]>
	<CAApHDvpRxyw1G2HVkA=PM5Uqh59moOFqPXyMTsEDUadKo2fQTA@mail.gmail.com>
	<[email protected]>
	<CAApHDvoQRoKHNfTaw+Sn=bZGKVmn8W-5yBPGP7+98mWJz5A9uQ@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<CAApHDvp1mue1a5fdfsa2z2mAS6v2d9+kxMzxH7GhAiC0Z7o8Mw@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<CAApHDvr2v7iwMUsCQKxUPSSzTGuBOx1_e0hyEA8VLy1or35JVA@mail.gmail.com>

On Wed, Aug 28, 2024 at 7:58 AM David Rowley <[email protected]> wrote:
> On Wed, 28 Aug 2024 at 11:37, Tom Lane <[email protected]> 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






view thread (22+ messages)  latest in thread

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables.
  In-Reply-To: <CAMbWs49gVdBxrjvz82Tqs9UL+ZDNV5cx85ZURhfxUaz-0wz-pQ@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox