Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qqqVY-001K6G-SZ for pgsql-hackers@arkaria.postgresql.org; Thu, 12 Oct 2023 07:53:24 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qqqUY-004Omw-D8 for pgsql-hackers@arkaria.postgresql.org; Thu, 12 Oct 2023 07:52:23 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qqqUY-004Omo-2C for pgsql-hackers@lists.postgresql.org; Thu, 12 Oct 2023 07:52:22 +0000 Received: from mail-ot1-x32f.google.com ([2607:f8b0:4864:20::32f]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qqqUU-00082d-Sk for pgsql-hackers@postgresql.org; Thu, 12 Oct 2023 07:52:22 +0000 Received: by mail-ot1-x32f.google.com with SMTP id 46e09a7af769-6c7c2c428c1so913949a34.0 for ; Thu, 12 Oct 2023 00:52:19 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1697097138; x=1697701938; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=3z+zbUpQenVaVDb5uh1RgkKxT1JZsUqoUW6CsfsAzl0=; b=O7GBl07dUp4+HJlOjtXuI5vAVZRZKlFBxAriSp+1s9bIjfrLn6NUGysZa7TD2FcQqT 2IWclXAzl+e3gZb8vSJva6z0umTzjpN3GN+hurdnGXmhzQQenEyH05sl0xqLZ7ppccGf V3WM2kFCBGVEB7MRELalrhc3YUbC0u9WPuBb5uW4HDavHi/W6Sl7XqfYSYU37w88tOb/ HV45TZtYixzAOvyNth7PVnVTqkwYswZ5ztWn18pX7R31ppfZlP5OO5cafip5noY1Gnul VjyJXQ3eRB6Fpcp33nD/osBV2I/lJh5VEav5Bl0n+/S8JLl/DH10gVF3T9wuIScPJIxv h+kw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1697097138; x=1697701938; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=3z+zbUpQenVaVDb5uh1RgkKxT1JZsUqoUW6CsfsAzl0=; b=SbHrJHrrEN/XLI34vSYoiDMD8irapfAFUYLFDRiMgZb1qLoXSHJGnN2P3nmNYy8o5V 3thViOuxE0l82wyDJiwlIbn5oTslj1oGmZCj+22ax90jKYQ4wMZuOXPLarsQzOHbW/Ul A9W9txtkeWP5KaSYZ5UxaQQCD2u/NMn06Xu+GsoGPupv+H4wWqFhQJYzaW9snW2R9Rdf uhYB3P4UdUlbgtag1AHa8qBzhDaPZflA0KEXxtlwxRFB+iv1OesZ78sYtyOBHD7SluiU uYf9zbb9jsdRr4oOrDH80JgQPJFhBygQZBhg8FpVXzDinN+xfWKUvAJYnTUXFowSTi// fgEA== X-Gm-Message-State: AOJu0YyP3J+wpM/lqLs7t4iD9Km2c/LdZwMjpr9M6SXqmdS6oMQ7zuKt Bg2cuE+CwCc6PKENMInPPQ/G4XaNCjs2kGRp49g= X-Google-Smtp-Source: AGHT+IHcql6y5JdtECiZ11lTYr3dRX3xzQXD3me3+xihuspz2wRv4pwKaoGetHVf0Cjg+eebob86g1RWUbEhw5x0KLI= X-Received: by 2002:a54:4593:0:b0:3a7:3791:706a with SMTP id z19-20020a544593000000b003a73791706amr9492162oib.5.1697097138313; Thu, 12 Oct 2023 00:52:18 -0700 (PDT) MIME-Version: 1.0 References: <247851.1668293143@sss.pgh.pa.us> In-Reply-To: From: Andy Fan Date: Thu, 12 Oct 2023 15:52:07 +0800 Message-ID: Subject: Re: A new strategy for pull-up correlated ANY_SUBLINK To: Alena Rybakina Cc: pgsql-hackers , Tom Lane , vignesh C , Richard Guo , Andrey Lepikhov Content-Type: multipart/alternative; boundary="000000000000143ff10607803830" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000143ff10607803830 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi Alena, On Thu, Oct 12, 2023 at 5:01=E2=80=AFAM Alena Rybakina wrote: > Hi! > > I reviewed your patch and it was interesting for me! > > Thank you for the explanation. It was really informative for me! > Thanks for your interest in this, and I am glad to know it is informative. > Unfortunately, I found a request when sublink did not pull-up, as in the > examples above. I couldn't quite figure out why. > I'm not sure what you mean with the "above", I guess it should be the "below"? > explain (analyze, costs off, buffers) > select b.x, b.x, a.y > from b > left join a > on b.x=3Da.x and > > *b.t in (select max(a0.t) * > from a a0 > where a0.x =3D b.x and > a0.t =3D b.t); > ... > SubPlan 2 > Here the sublink can't be pulled up because of its reference to the LHS of left join, the original logic is that no matter the 'b.t in ..' returns the true or false, the rows in LHS will be returned. If we pull it up to LHS, some rows in LHS will be filtered out, which breaks its original semantics. I thought it would be: > > explain (analyze, costs off, buffers) > select b.x, b.x, a.y > from b > left join a on > b.x=3Da.x and > > *b.t =3D (select max(a0.t) * > from a a0 > where a0.x =3D b.x and > a0.t <=3D b.t); > QUERY > PLAN > > -------------------------------------------------------------------------= -------------------------------------------- > Hash Right Join (actual time=3D1.181..67.927 rows=3D1000 loops=3D1) > Hash Cond: (a.x =3D b.x) > *Join Filter: (b.t =3D (SubPlan 2))* > Buffers: shared hit=3D3546 > -> Seq Scan on a (actual time=3D0.022..17.109 rows=3D100000 loops=3D1= ) > Buffers: shared hit=3D541 > -> Hash (actual time=3D1.065..1.068 rows=3D1000 loops=3D1) > Buckets: 4096 Batches: 1 Memory Usage: 72kB > Buffers: shared hit=3D5 > -> Seq Scan on b (actual time=3D0.049..0.401 rows=3D1000 loops= =3D1) > Buffers: shared hit=3D5 > SubPlan 2 > -> Result (actual time=3D0.025..0.025 rows=3D1 loops=3D1000) > Buffers: shared hit=3D3000 > InitPlan 1 (returns $2) > -> Limit (actual time=3D0.024..0.024 rows=3D1 loops=3D1000) > Buffers: shared hit=3D3000 > -> Index Only Scan Backward using a_t_x_idx on a a0 > (actual time=3D0.023..0.023 rows=3D1 loops=3D1000) > Index Cond: ((t IS NOT NULL) AND (t <=3D b.t) AN= D > (x =3D b.x)) > Heap Fetches: 1000 > Buffers: shared hit=3D3000 > Planning Time: 0.689 ms > Execution Time: 68.220 ms > (23 rows) > > If you noticed, it became possible after replacing the "in" operator with > "=3D". > I didn't notice much difference between the 'in' and '=3D', maybe I missed something? > I took the liberty of adding this to your patch and added myself as > reviewer, if you don't mind. > Sure, the patch after your modification looks better than the original. I'm not sure how the test case around "because of got one row" is relevant to the current changes. After we reach to some agreement on the above discussion, I think v4 is good for committer to review! --=20 Best Regards Andy Fan --000000000000143ff10607803830 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Alena,

On Thu, Oct 12, 2023 at 5:01=E2= =80=AFAM Alena Rybakina <len= a.ribackina@yandex.ru> wrote:
=20 =20 =20

Hi!

I reviewed your patch and it was interesting for me!

Thank you for the explanation. It was really informative for me!

=
Thanks for your interest in this,=C2=A0 and I am gl= ad to know it is informative.=C2=A0

Unfortunately, I found a request when sublink did n= ot pull-up, as in the=C2=A0

examples above. I couldn't quite figure out wh= y.

I'm not sure what you mean with the "= ;above", I guess it should be the "below"?
=C2=A0<= br>

explain (analyze, costs off, buffers)
select b.x, b.x, a.y
from b
=C2=A0=C2=A0=C2=A0 left join a
=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 on b.x=3Da.x and
=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 b.t in
=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 (select = max(a0.t)
=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 from= a a0
=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 wher= e a0.x =3D b.x and
=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2= =A0=C2=A0 =C2=A0=C2=A0=C2=A0 a0.t =3D b.t);

...=C2=A0
=C2=A0=C2=A0 SubPlan 2

Here the sublink can't be pulled up because of its reference to=C2= =A0
the=C2=A0 LHS of left join, the original logic is that no mat= ter the 'b.t in ..'=C2=A0
returns the true or false,=C2= =A0 the rows in LHS will be returned.=C2=A0 If we
pull it up to L= HS, some rows in LHS will be filtered out, which=C2=A0
breaks its= original=C2=A0semantics.=C2=A0

I though= t it would be:

explain (analyze, costs off, buffers)
select b.x, b.x, a.y
from b
=C2=A0=C2=A0=C2=A0 left join a on
=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 b.x=3Da.x and
=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 b.t =3D
=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 (select = max(a0.t)
=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0=C2=A0 from= a a0
=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0=C2=A0 wher= e a0.x =3D b.x and
=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2= =A0=C2=A0 =C2=A0=C2=A0 a0.t <=3D b.t);

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 QUERY PLAN=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0
---------------------------------------------------------------------------= ------------------------------------------
=C2=A0Hash Right Join (actual time=3D1.181..67.927 rows=3D1000 loops= =3D1)
=C2=A0=C2=A0 Hash Cond: (a.x =3D b.x)
=C2=A0=C2=A0 Join Filter: (b.t =3D (SubPlan 2))
=C2=A0=C2=A0 Buffers: shared hit=3D3546
=C2=A0=C2=A0 ->=C2=A0 Seq Scan on a (actual time=3D0.022..17.109 r= ows=3D100000 loops=3D1)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Buffers: shared hit= =3D541
=C2=A0=C2=A0 ->=C2=A0 Hash (actual time=3D1.065..1.068 rows=3D1000= loops=3D1)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Buckets: 4096=C2=A0 = Batches: 1=C2=A0 Memory Usage: 72kB
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Buffers: shared hit= =3D5
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ->=C2=A0 Seq Scan= on b (actual time=3D0.049..0.401 rows=3D1000 loops=3D1)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 Buffers: shared hit=3D5
=C2=A0=C2=A0 SubPlan 2
=C2=A0=C2=A0=C2=A0=C2=A0 ->=C2=A0 Result (actual time=3D0.025..0.0= 25 rows=3D1 loops=3D1000)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Buffers:= shared hit=3D3000
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 InitPlan= 1 (returns $2)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 ->=C2=A0 Limit (actual time=3D0.024..0.024 rows=3D1 loops=3D1000)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Buffers: shared hit=3D3000
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ->=C2=A0 Index Only Scan Backwar= d using a_t_x_idx on a a0 (actual time=3D0.023..0.023 rows=3D1 loops=3D1000)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= Index Cond: ((t IS NOT NULL) AND (t <=3D b.t) AND (x =3D b.x))
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= Heap Fetches: 1000
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= Buffers: shared hit=3D3000
=C2=A0Planning Time: 0.689 ms
=C2=A0Execution Time: 68.220 ms
(23 rows)

If you noticed, it became possible after replacing the "in"= ; operator with "=3D".

I didn'= t notice much difference between the 'in'=C2=A0 and '=3D',= =C2=A0 maybe I=C2=A0
missed something?=C2=A0=C2=A0

I took the liberty of adding this to your patch and added myself as reviewer, if you don't mind.

Sure, = the patch after your modification looks better than the original.=C2=A0
=
I'm not sure how the test case around "because of got one row" is
relevant = to the current changes.=C2=A0 After we reach to some agreement
on= the above discussion, I think v4 is good for committer to review!

--
Best Regards
An= dy Fan
--000000000000143ff10607803830--