MIME-Version: 1.0
From: Lakshmi N <lakshmin.jhs@gmail.com>
Date: Wed, 8 Apr 2026 00:09:23 -0700
Message-ID: 
 <CA+3i_M-mo7Of=Pn8WzRfJLt=fc=gDTn1oOdj8v8BEtgXh9ZMCg@mail.gmail.com>
Subject: DOCS: pg_plan_advice minor doc fixes
To: pgsql-hackers@postgresql.org, pgsql-hackers@lists.postgresql.org
Content-Type: multipart/mixed; boundary="0000000000000b5568064eed954f"
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2B3i_M-mo7Of%3DPn8WzRfJLt%3Dfc%3DgDTn1oOdj8v8BEtgXh9ZMCg%40mail.gmail.com>
Precedence: bulk

--0000000000000b5568064eed954f
Content-Type: multipart/alternative; boundary="0000000000000b5567064eed954d"

--0000000000000b5567064eed954d
Content-Type: text/plain; charset="UTF-8"

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 the
other one works correctly.

postgres=# SET pg_plan_advice.advice = 'FOREIGN_JOIN((t1 t2))';
EXPLAIN (COSTS OFF, PLAN_ADVICE)
    SELECT * FROM fdw_t1 t1 JOIN fdw_t2 t2 ON t1.id = 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=# SET pg_plan_advice.advice = 'NESTED_LOOP_MEMOIZE(d)';
SET
postgres=# EXPLAIN (COSTS OFF, PLAN_ADVICE) select * from t1 f JOIN t2 ON
f.id = t2.t1_id;
                 QUERY PLAN
--------------------------------------------
 Hash Join
   Hash Cond: (t2.t1_id = 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

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

<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>

--0000000000000b5567064eed954d--

--0000000000000b5568064eed954f
Content-Type: application/octet-stream;
	name="0001-doc-Fix-pgplanadvice-tags-for-FOREIGN_JOIN-and-NESTE.patch"
Content-Disposition: attachment;
	filename="0001-doc-Fix-pgplanadvice-tags-for-FOREIGN_JOIN-and-NESTE.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_mnppi92q0>
X-Attachment-Id: f_mnppi92q0

RnJvbSBhNGQ2NWIxZjJlZmY1MDU1MzUwOTcwMDhmNWFlNTVkMGFlMzdkNjYxIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBMYWtzaG1pIE4gPGxha3NobWluLmpoc0BnbWFpbC5jb20+CkRh
dGU6IFR1ZSwgNyBBcHIgMjAyNiAyMzo1ODoxNSAtMDcwMApTdWJqZWN0OiBbUEFUQ0hdIERPQ1M6
IFVwZGF0ZSBwZ3BsYW5hZHZpY2UgdGFncyBmb3IgRk9SRUlHTl9KT0lOIGFuZAogTkVTVEVEX0xP
T1BfTUVNT0laRQoKLS0tCiBkb2Mvc3JjL3NnbWwvcGdwbGFuYWR2aWNlLnNnbWwgfCA4ICsrKyst
LS0tCiAxIGZpbGUgY2hhbmdlZCwgNCBpbnNlcnRpb25zKCspLCA0IGRlbGV0aW9ucygtKQoKZGlm
ZiAtLWdpdCBhL2RvYy9zcmMvc2dtbC9wZ3BsYW5hZHZpY2Uuc2dtbCBiL2RvYy9zcmMvc2dtbC9w
Z3BsYW5hZHZpY2Uuc2dtbAppbmRleCBjM2UxY2NiNjBhMi4uNzMxNTU0NjFjMGIgMTAwNjQ0Ci0t
LSBhL2RvYy9zcmMvc2dtbC9wZ3BsYW5hZHZpY2Uuc2dtbAorKysgYi9kb2Mvc3JjL3NnbWwvcGdw
bGFuYWR2aWNlLnNnbWwKQEAgLTI2Niw3ICsyNjYsNyBAQCBTRVFfU0NBTig8cmVwbGFjZWFibGU+
dGFyZ2V0PC9yZXBsYWNlYWJsZT4gWyAuLi4gXSkKIFRJRF9TQ0FOKDxyZXBsYWNlYWJsZT50YXJn
ZXQ8L3JlcGxhY2VhYmxlPiBbIC4uLiBdKQogSU5ERVhfU0NBTig8cmVwbGFjZWFibGU+dGFyZ2V0
PC9yZXBsYWNlYWJsZT4gPHJlcGxhY2VhYmxlPmluZGV4X25hbWU8L3JlcGxhY2VhYmxlPiBbIC4u
LiBdKQogSU5ERVhfT05MWV9TQ0FOKDxyZXBsYWNlYWJsZT50YXJnZXQ8L3JlcGxhY2VhYmxlPiA8
cmVwbGFjZWFibGU+aW5kZXhfbmFtZTwvcmVwbGFjZWFibGU+IFsgLi4uIF0pCi1GT1JFSUdOX1ND
QU4oKDxyZXBsYWNlYWJsZT50YXJnZXQ8L3JlcGxhY2VhYmxlPiBbIC4uLiBdKSBbIC4uLiBdKQor
Rk9SRUlHTl9KT0lOKCg8cmVwbGFjZWFibGU+dGFyZ2V0PC9yZXBsYWNlYWJsZT4gWyAuLi4gXSkg
WyAuLi4gXSkKIEJJVE1BUF9IRUFQX1NDQU4oPHJlcGxhY2VhYmxlPnRhcmdldDwvcmVwbGFjZWFi
bGU+IFsgLi4uIF0pCiBET19OT1RfU0NBTig8cmVwbGFjZWFibGU+dGFyZ2V0PC9yZXBsYWNlYWJs
ZT4gWyAuLi4gXSk8L3N5bm9wc2lzPgogCkBAIC0yODgsMTAgKzI4OCwxMCBAQCBET19OT1RfU0NB
Tig8cmVwbGFjZWFibGU+dGFyZ2V0PC9yZXBsYWNlYWJsZT4gWyAuLi4gXSk8L3N5bm9wc2lzPgog
ICAgPC9wYXJhPgogCiAgICA8cGFyYT4KLSAgICA8bGl0ZXJhbD5GT1JFSUdOX1NDQU48L2xpdGVy
YWw+IHNwZWNpZmllcyB0aGF0IGEgam9pbiBiZXR3ZWVuIHR3byBvcgorICAgIDxsaXRlcmFsPkZP
UkVJR05fSk9JTjwvbGl0ZXJhbD4gc3BlY2lmaWVzIHRoYXQgYSBqb2luIGJldHdlZW4gdHdvIG9y
CiAgICAgbW9yZSBmb3JlaWduIHRhYmxlcyBzaG91bGQgYmUgcHVzaGVkIGRvd24gdG8gYSByZW1v
dGUgc2VydmVyIHNvCiAgICAgdGhhdCBpdCBjYW4gYmUgaW1wbGVtZW50ZWQgYXMgYSBzaW5nbGUg
PGxpdGVyYWw+Rm9yZWlnbiBTY2FuPC9saXRlcmFsPi4KLSAgICBTcGVjaWZ5aW5nIDxsaXRlcmFs
PkZPUkVJR05fU0NBTjwvbGl0ZXJhbD4gZm9yIGEgc2luZ2xlIGZvcmVpZ24gdGFibGUgaXMKKyAg
ICBTcGVjaWZ5aW5nIDxsaXRlcmFsPkZPUkVJR05fSk9JTjwvbGl0ZXJhbD4gZm9yIGEgc2luZ2xl
IGZvcmVpZ24gdGFibGUgaXMKICAgICBuZWl0aGVyIG5lY2Vzc2FyeSBub3IgcGVybWlzc2libGU6
IGEgPGxpdGVyYWw+Rm9yZWlnbiBTY2FuPC9saXRlcmFsPiB3aWxsCiAgICAgbmVlZCB0byBiZSB1
c2VkIHJlZ2FyZGxlc3MuIElmIHlvdSB3YW50IHRvIHByZXZlbnQgYSBqb2luIGZyb20gYmVpbmcK
ICAgICBwdXNoZWQgZG93biwgY29uc2lkZXIgdXNpbmcgdGhlIDxsaXRlcmFsPkpPSU5fT1JERVI8
L2xpdGVyYWw+IHRhZyBmb3IKQEAgLTM5NSw3ICszOTUsNyBAQCBqb2luX21ldGhvZF9uYW1lKDxy
ZXBsYWNlYWJsZT5qb2luX21ldGhvZF9pdGVtPC9yZXBsYWNlYWJsZT4gWyAuLi4gXSkKIAogPHBo
cmFzZT53aGVyZSA8cmVwbGFjZWFibGU+am9pbl9tZXRob2RfbmFtZTwvcmVwbGFjZWFibGU+IGlz
OjwvcGhyYXNlPgogCi17IE1FUkdFX0pPSU5fTUFURVJJQUxJWkUgfCBNRVJHRV9KT0lOX1BMQUlO
IHwgTkVTVEVEX0xPT1BfTUFURVJJQUxJWkUgfCBORVNURURfTE9PUF9QTEFJTiB8IEhBU0hfSk9J
TiB9Cit7IE1FUkdFX0pPSU5fTUFURVJJQUxJWkUgfCBNRVJHRV9KT0lOX1BMQUlOIHwgTkVTVEVE
X0xPT1BfTUFURVJJQUxJWkUgfCBORVNURURfTE9PUF9NRU1PSVpFIHwgTkVTVEVEX0xPT1BfUExB
SU4gfCBIQVNIX0pPSU4gfQogCiA8cGhyYXNlPmFuZCA8cmVwbGFjZWFibGU+am9pbl9tZXRob2Rf
aXRlbTwvcmVwbGFjZWFibGU+IGlzOjwvcGhyYXNlPgogCi0tIAoyLjQzLjAKCg==
--0000000000000b5568064eed954f--