MIME-Version: 1.0
References: 
 <CA+3i_M-mo7Of=Pn8WzRfJLt=fc=gDTn1oOdj8v8BEtgXh9ZMCg@mail.gmail.com>
In-Reply-To: 
 <CA+3i_M-mo7Of=Pn8WzRfJLt=fc=gDTn1oOdj8v8BEtgXh9ZMCg@mail.gmail.com>
From: jie wang <jugierwang@gmail.com>
Date: Wed, 8 Apr 2026 15:52:10 +0800
Message-ID: 
 <CAJnZyeAdvzY01rTb_XDN3PfAPD0NZ61Ndf0J4j2H=GBTDVr5sQ@mail.gmail.com>
Subject: Re: DOCS: pg_plan_advice minor doc fixes
To: Lakshmi N <lakshmin.jhs@gmail.com>
Cc: pgsql-hackers@postgresql.org, pgsql-hackers@lists.postgresql.org
Content-Type: multipart/mixed; boundary="000000000000525d28064eee2ed4"
Archived-At: 
 <https://www.postgresql.org/message-id/CAJnZyeAdvzY01rTb_XDN3PfAPD0NZ61Ndf0J4j2H%3DGBTDVr5sQ%40mail.gmail.com>
Precedence: bulk

--000000000000525d28064eee2ed4
Content-Type: multipart/alternative; boundary="000000000000525d27064eee2ed2"

--000000000000525d27064eee2ed2
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Lakshmi N <lakshmin.jhs@gmail.com> =E4=BA=8E2026=E5=B9=B44=E6=9C=888=E6=97=
=A5=E5=91=A8=E4=B8=89 15:09=E5=86=99=E9=81=93=EF=BC=9A

> Hi hackers,
>
> I noticed the following minor discrepancy in the pg_plan_advice docs,
> attached a patch to address this.
>
> (1) FOREIGN SCAN should be FOREIGN JOIN in the scan method advice tag. I
> tested with FOREIGN SCAN and can confirm it gives a syntax error while th=
e
> other one works correctly.
>
> postgres=3D# SET pg_plan_advice.advice =3D 'FOREIGN_JOIN((t1 t2))';
> EXPLAIN (COSTS OFF, PLAN_ADVICE)
>     SELECT * FROM fdw_t1 t1 JOIN fdw_t2 t2 ON t1.id =3D t2.t1_id;
>
> SET
>                    QUERY PLAN
> -------------------------------------------------
>  Foreign Scan
>    Relations: (fdw_t1 t1) INNER JOIN (fdw_t2 t2)
>  Supplied Plan Advice:
>    FOREIGN_JOIN((t1 t2)) /* matched */
>  Generated Plan Advice:
>    FOREIGN_JOIN((t1 t2))
>    NO_GATHER(t1 t2)
> (7 rows)
>
>
> (2) NESTED_LOOP_MEMOIZE is missing from the join method list in the doc
> though it is supported.  Verified this by running below:
>
> postgres=3D# SET pg_plan_advice.advice =3D 'NESTED_LOOP_MEMOIZE(d)';
> SET
> postgres=3D# EXPLAIN (COSTS OFF, PLAN_ADVICE) select * from t1 f JOIN t2 =
ON
> f.id =3D t2.t1_id;
>                  QUERY PLAN
> --------------------------------------------
>  Hash Join
>    Hash Cond: (t2.t1_id =3D f.id)
>    ->  Seq Scan on t2
>    ->  Hash
>          ->  Seq Scan on t1 f
>  Supplied Plan Advice:
>    NESTED_LOOP_MEMOIZE(d) /* not matched */
>  Generated Plan Advice:
>    JOIN_ORDER(t2 f)
>    HASH_JOIN(f)
>    SEQ_SCAN(t2 f)
>    NO_GATHER(f t2)
> (12 rows)
>
> Regards,
> Lakshmi
>


Hi,

I found that this patch is not fully modified and is missing a part:
`Foreign Scan` --->  `Foreign Join`

Best regards,
--
wang jie

--000000000000525d27064eee2ed2
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote g=
mail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">Lakshmi N &lt;<=
a href=3D"mailto:lakshmin.jhs@gmail.com">lakshmin.jhs@gmail.com</a>&gt; =E4=
=BA=8E2026=E5=B9=B44=E6=9C=888=E6=97=A5=E5=91=A8=E4=B8=89 15:09=E5=86=99=E9=
=81=93=EF=BC=9A<br></div><blockquote class=3D"gmail_quote" style=3D"margin:=
0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">=
<div dir=3D"ltr">Hi hackers,<div><br></div><div>I noticed the following min=
or discrepancy in the pg_plan_advice docs, attached a patch to address this=
.=C2=A0</div><div><br></div><div>(1) FOREIGN SCAN should be FOREIGN JOIN in=
 the scan method advice=C2=A0tag. I tested with FOREIGN SCAN and can confir=
m it gives a syntax error while the other one works correctly.</div><div><b=
r></div><div>postgres=3D# SET pg_plan_advice.advice =3D &#39;FOREIGN_JOIN((=
t1 t2))&#39;;=C2=A0<br>EXPLAIN (COSTS OFF, PLAN_ADVICE)<br>=C2=A0 =C2=A0 SE=
LECT * FROM fdw_t1 t1 JOIN fdw_t2 t2 ON=C2=A0<a href=3D"http://t1.id/" targ=
et=3D"_blank">t1.id</a>=C2=A0=3D t2.t1_id;<br><br>SET<br>=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0QUERY PLAN =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0<br>------------=
-------------------------------------<br>=C2=A0Foreign Scan<br>=C2=A0 =C2=
=A0Relations: (fdw_t1 t1) INNER JOIN (fdw_t2 t2)<br>=C2=A0Supplied Plan Adv=
ice:<br>=C2=A0 =C2=A0FOREIGN_JOIN((t1 t2)) /* matched */<br>=C2=A0Generated=
 Plan Advice:<br>=C2=A0 =C2=A0FOREIGN_JOIN((t1 t2))<br>=C2=A0 =C2=A0NO_GATH=
ER(t1 t2)<br>(7 rows)<br></div><div><br></div><div><br></div><div>(2) NESTE=
D_LOOP_MEMOIZE is missing from the join method list in the doc though it is=
 supported.=C2=A0 Verified this by running below:</div><div><br></div><div>=
postgres=3D# SET pg_plan_advice.advice =3D &#39;NESTED_LOOP_MEMOIZE(d)&#39;=
;<br>SET<br>postgres=3D# EXPLAIN (COSTS OFF, PLAN_ADVICE) select * from t1 =
f JOIN t2 ON=C2=A0<a href=3D"http://f.id/" target=3D"_blank">f.id</a>=C2=A0=
=3D t2.t1_id;<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0QUERY PLAN =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0<br=
>--------------------------------------------<br>=C2=A0Hash Join<br>=C2=A0 =
=C2=A0Hash Cond: (t2.t1_id =3D=C2=A0<a href=3D"http://f.id/" target=3D"_bla=
nk">f.id</a>)<br>=C2=A0 =C2=A0-&gt; =C2=A0Seq Scan on t2<br>=C2=A0 =C2=A0-&=
gt; =C2=A0Hash<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-&gt; =C2=A0Seq Scan on=
 t1 f<br>=C2=A0Supplied Plan Advice:<br>=C2=A0 =C2=A0NESTED_LOOP_MEMOIZE(d)=
 /* not matched */<br>=C2=A0Generated Plan Advice:<br>=C2=A0 =C2=A0JOIN_ORD=
ER(t2 f)<br>=C2=A0 =C2=A0HASH_JOIN(f)<br>=C2=A0 =C2=A0SEQ_SCAN(t2 f)<br>=C2=
=A0 =C2=A0NO_GATHER(f t2)<br>(12 rows)</div><div><br></div><div>Regards,</d=
iv><div>Lakshmi</div></div></blockquote><div><br></div><div><br></div>Hi,<b=
r><br>I found that this patch is not fully modified and is missing a part:<=
br>`Foreign Scan` ---&gt; =C2=A0`Foreign Join`<br><br>Best regards,<br>--<b=
r><div>wang jie=C2=A0</div></div></div>

--000000000000525d27064eee2ed2--

--000000000000525d28064eee2ed4
Content-Type: application/octet-stream;
	name="v2-0001-DOCS-Update-pgplanadvice-tags-for-FOREIGN_JOIN-an.patch"
Content-Disposition: attachment;
	filename="v2-0001-DOCS-Update-pgplanadvice-tags-for-FOREIGN_JOIN-an.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_mnpqyh7d0>
X-Attachment-Id: f_mnpqyh7d0

RnJvbSA4MzcyM2JmMGI5NmIwNDNiODQ5Yzc0N2RjMmRiYjFjODVhNTZmNmY4IE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBMYWtzaG1pIE4gPGxha3NobWluLmpoc0BnbWFpbC5jb20+CkRh
dGU6IFR1ZSwgNyBBcHIgMjAyNiAyMzo1ODoxNSAtMDcwMApTdWJqZWN0OiBbUEFUQ0ggdjJdIERP
Q1M6IFVwZGF0ZSBwZ3BsYW5hZHZpY2UgdGFncyBmb3IgRk9SRUlHTl9KT0lOIGFuZAogTkVTVEVE
X0xPT1BfTUVNT0laRQoKLS0tCiBkb2Mvc3JjL3NnbWwvcGdwbGFuYWR2aWNlLnNnbWwgfCAxMiAr
KysrKystLS0tLS0KIDEgZmlsZSBjaGFuZ2VkLCA2IGluc2VydGlvbnMoKyksIDYgZGVsZXRpb25z
KC0pCgpkaWZmIC0tZ2l0IGEvZG9jL3NyYy9zZ21sL3BncGxhbmFkdmljZS5zZ21sIGIvZG9jL3Ny
Yy9zZ21sL3BncGxhbmFkdmljZS5zZ21sCmluZGV4IGMzZTFjY2I2MGEyLi5lOGY1ZWRlYzg3NCAx
MDA2NDQKLS0tIGEvZG9jL3NyYy9zZ21sL3BncGxhbmFkdmljZS5zZ21sCisrKyBiL2RvYy9zcmMv
c2dtbC9wZ3BsYW5hZHZpY2Uuc2dtbApAQCAtMjY2LDcgKzI2Niw3IEBAIFNFUV9TQ0FOKDxyZXBs
YWNlYWJsZT50YXJnZXQ8L3JlcGxhY2VhYmxlPiBbIC4uLiBdKQogVElEX1NDQU4oPHJlcGxhY2Vh
YmxlPnRhcmdldDwvcmVwbGFjZWFibGU+IFsgLi4uIF0pCiBJTkRFWF9TQ0FOKDxyZXBsYWNlYWJs
ZT50YXJnZXQ8L3JlcGxhY2VhYmxlPiA8cmVwbGFjZWFibGU+aW5kZXhfbmFtZTwvcmVwbGFjZWFi
bGU+IFsgLi4uIF0pCiBJTkRFWF9PTkxZX1NDQU4oPHJlcGxhY2VhYmxlPnRhcmdldDwvcmVwbGFj
ZWFibGU+IDxyZXBsYWNlYWJsZT5pbmRleF9uYW1lPC9yZXBsYWNlYWJsZT4gWyAuLi4gXSkKLUZP
UkVJR05fU0NBTigoPHJlcGxhY2VhYmxlPnRhcmdldDwvcmVwbGFjZWFibGU+IFsgLi4uIF0pIFsg
Li4uIF0pCitGT1JFSUdOX0pPSU4oKDxyZXBsYWNlYWJsZT50YXJnZXQ8L3JlcGxhY2VhYmxlPiBb
IC4uLiBdKSBbIC4uLiBdKQogQklUTUFQX0hFQVBfU0NBTig8cmVwbGFjZWFibGU+dGFyZ2V0PC9y
ZXBsYWNlYWJsZT4gWyAuLi4gXSkKIERPX05PVF9TQ0FOKDxyZXBsYWNlYWJsZT50YXJnZXQ8L3Jl
cGxhY2VhYmxlPiBbIC4uLiBdKTwvc3lub3BzaXM+CiAKQEAgLTI4OCwxMSArMjg4LDExIEBAIERP
X05PVF9TQ0FOKDxyZXBsYWNlYWJsZT50YXJnZXQ8L3JlcGxhY2VhYmxlPiBbIC4uLiBdKTwvc3lu
b3BzaXM+CiAgICA8L3BhcmE+CiAKICAgIDxwYXJhPgotICAgIDxsaXRlcmFsPkZPUkVJR05fU0NB
TjwvbGl0ZXJhbD4gc3BlY2lmaWVzIHRoYXQgYSBqb2luIGJldHdlZW4gdHdvIG9yCisgICAgPGxp
dGVyYWw+Rk9SRUlHTl9KT0lOPC9saXRlcmFsPiBzcGVjaWZpZXMgdGhhdCBhIGpvaW4gYmV0d2Vl
biB0d28gb3IKICAgICBtb3JlIGZvcmVpZ24gdGFibGVzIHNob3VsZCBiZSBwdXNoZWQgZG93biB0
byBhIHJlbW90ZSBzZXJ2ZXIgc28KLSAgICB0aGF0IGl0IGNhbiBiZSBpbXBsZW1lbnRlZCBhcyBh
IHNpbmdsZSA8bGl0ZXJhbD5Gb3JlaWduIFNjYW48L2xpdGVyYWw+LgotICAgIFNwZWNpZnlpbmcg
PGxpdGVyYWw+Rk9SRUlHTl9TQ0FOPC9saXRlcmFsPiBmb3IgYSBzaW5nbGUgZm9yZWlnbiB0YWJs
ZSBpcwotICAgIG5laXRoZXIgbmVjZXNzYXJ5IG5vciBwZXJtaXNzaWJsZTogYSA8bGl0ZXJhbD5G
b3JlaWduIFNjYW48L2xpdGVyYWw+IHdpbGwKKyAgICB0aGF0IGl0IGNhbiBiZSBpbXBsZW1lbnRl
ZCBhcyBhIHNpbmdsZSA8bGl0ZXJhbD5Gb3JlaWduIEpvaW48L2xpdGVyYWw+LgorICAgIFNwZWNp
ZnlpbmcgPGxpdGVyYWw+Rk9SRUlHTl9KT0lOPC9saXRlcmFsPiBmb3IgYSBzaW5nbGUgZm9yZWln
biB0YWJsZSBpcworICAgIG5laXRoZXIgbmVjZXNzYXJ5IG5vciBwZXJtaXNzaWJsZTogYSA8bGl0
ZXJhbD5Gb3JlaWduIEpvaW48L2xpdGVyYWw+IHdpbGwKICAgICBuZWVkIHRvIGJlIHVzZWQgcmVn
YXJkbGVzcy4gSWYgeW91IHdhbnQgdG8gcHJldmVudCBhIGpvaW4gZnJvbSBiZWluZwogICAgIHB1
c2hlZCBkb3duLCBjb25zaWRlciB1c2luZyB0aGUgPGxpdGVyYWw+Sk9JTl9PUkRFUjwvbGl0ZXJh
bD4gdGFnIGZvcgogICAgIHRoYXQgcHVycG9zZS4KQEAgLTM5NSw3ICszOTUsNyBAQCBqb2luX21l
dGhvZF9uYW1lKDxyZXBsYWNlYWJsZT5qb2luX21ldGhvZF9pdGVtPC9yZXBsYWNlYWJsZT4gWyAu
Li4gXSkKIAogPHBocmFzZT53aGVyZSA8cmVwbGFjZWFibGU+am9pbl9tZXRob2RfbmFtZTwvcmVw
bGFjZWFibGU+IGlzOjwvcGhyYXNlPgogCi17IE1FUkdFX0pPSU5fTUFURVJJQUxJWkUgfCBNRVJH
RV9KT0lOX1BMQUlOIHwgTkVTVEVEX0xPT1BfTUFURVJJQUxJWkUgfCBORVNURURfTE9PUF9QTEFJ
TiB8IEhBU0hfSk9JTiB9Cit7IE1FUkdFX0pPSU5fTUFURVJJQUxJWkUgfCBNRVJHRV9KT0lOX1BM
QUlOIHwgTkVTVEVEX0xPT1BfTUFURVJJQUxJWkUgfCBORVNURURfTE9PUF9NRU1PSVpFIHwgTkVT
VEVEX0xPT1BfUExBSU4gfCBIQVNIX0pPSU4gfQogCiA8cGhyYXNlPmFuZCA8cmVwbGFjZWFibGU+
am9pbl9tZXRob2RfaXRlbTwvcmVwbGFjZWFibGU+IGlzOjwvcGhyYXNlPgogCi0tIAoyLjM0LjEK
Cg==
--000000000000525d28064eee2ed4--