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.96) (envelope-from ) id 1wAjBO-000GaG-2p for pgsql-hackers@arkaria.postgresql.org; Thu, 09 Apr 2026 06:48:07 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wAjBM-0043sD-2r for pgsql-hackers@arkaria.postgresql.org; Thu, 09 Apr 2026 06:48:05 +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.96) (envelope-from ) id 1wAjBM-0043s5-1q for pgsql-hackers@lists.postgresql.org; Thu, 09 Apr 2026 06:48:05 +0000 Received: from mail-yx1-xb12d.google.com ([2607:f8b0:4864:20::b12d]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wAjBL-000000008Qu-0W8i for pgsql-hackers@lists.postgresql.org; Thu, 09 Apr 2026 06:48:05 +0000 Received: by mail-yx1-xb12d.google.com with SMTP id 956f58d0204a3-6501725d888so431505d50.0 for ; Wed, 08 Apr 2026 23:48:03 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1775717281; cv=none; d=google.com; s=arc-20240605; b=WfG6+Tt+Pu0jo9iXLoI5+04NbDcwNtbNQpUJvkDfGcy+Ndzq9aeN2MpN9A/kg8x/P7 X0apZpyqz1NSCgTZhLijKL6IzylOA1K4g8vSWnllR274h3LmEMxEOW4YBwTVJJPplGY+ 0b7RTlYxVPo2dQ5NyoJJooiktCUym/JyQwkb/vLdnjTCdgAfciFb9t1GBSGE0DtawqFk 6t0WpsTEIrqYXgeUExNYHQypZ512KIaquc4CkEAhNMH9q0a4aMxDdqEsFq4nx7yazuok up6qyHuCebMEbuiEcecOtUZFrdmui5QbrKTVvGDyHt7iAdo9v/hWqj53m8T4FrN0Yulc gbkQ== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=tum1PjFLRAEOuQwEk8FvOCE1Xf7TJG7FBHkI10jrNks=; fh=ohg7t9td8AM2FobmVif+haNuoxa0q5lC4N0U6SKpKO8=; b=hNjpvBhOKwkYIlUQG33vuEkL01OZ02JashvSJt5AE2COZY9F+h5l6yNkoqPOIiI6ct YJfrLg1AyskW6kW0V3Dfk+ZDxxLo554O8iF8rtJliINK3X8TnjDjbw603uaBKnnadVBs b8Znqalhdm5RN8reHF+Nw3TSUwr21+YNItvSwkjJc7fu8zl0SSnQnvsSEFd/M/pqAZFk E8K/6nDt+iGgAVmfSFMZ0iGMvNtu85YTdKx5q4UjLaaMqq+cI8RjGiSQFlE17yHUKSda 3kCCaPwwZ0RKnE673awKRnrsIU6bszA8eevY3Ak6pAF8GrPJ8yD/hYTh1jic8zpqk5hn NbQA==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20251104; t=1775717281; x=1776322081; darn=lists.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=tum1PjFLRAEOuQwEk8FvOCE1Xf7TJG7FBHkI10jrNks=; b=TIzXn2zIqXzDsWjifAncuY8IJ2fXf0vvZP4Tk4Sf2ynXg6S5RLLadbykhDSU/+OPPf j9eitgpxvm2a5wxKP6gY8kQaxKNCG/mjhtHk6nVwQukBRMUpw23rjiwYMxvL54FENAdI hOE/MWRrtEGfgBcsFAbqYnRCGdR/mpgs0s7WIywt1OR960bgykTgchmeK+8sW93oeBgJ q5zWVzhB64TsYYF8KLLFIzdtlMSNtznUq5CdEGSThKEfroLiC4oj4Q8bIMEPEjzNYvgL v0rxWeipkIxrVftPXtDnSkGgLat/xBvOX40WPYqR6wxZZepoqxAGD99d4+M1zyohoLv2 LtQw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1775717281; x=1776322081; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=tum1PjFLRAEOuQwEk8FvOCE1Xf7TJG7FBHkI10jrNks=; b=TRpCPkVXChIy0rbzqfDqHgjMnAhm/eXUxkPnyl6B6GMahSNLF+lwzAi9ROyYvChKIm cbUsMdqN7GjyBmp/+PSNkv95nJD+476+SMgvYIC6zAgi7DOF8ZoLwkSZvY1MmWepGjDW UkehXmGl2YGIqlQfn1gnxRRI+VT69SrOLhBzodYLECDaDzaoJp2Gscr8T+XS4y5ANp8t uUyOT6EJlm5UpCtob07jgtwfbUMoGfMzfEWci2BWlpNcKjWWbOm9HqzVB+31kmd22eAK ogK9lU0YkCeJJjlB9CYX15VcnjeIYIHltjr9oY6Rr/QQHek8NBGZpfr9UAUaTYedoFLP 2OxQ== X-Forwarded-Encrypted: i=1; AJvYcCXv2T2Bmk3JggL3K3H54/9iWbamgoTfRXv+RfChFJPn3L+j2bgMwql88OSmT5BHUOSB5nb+/ayX0u+oegeo@lists.postgresql.org X-Gm-Message-State: AOJu0YxvgxaSKAJ7D4Bmdprd6rKKhHxJIGD5BtGhIuREiyx2WAi7V91N iD5fWe/9JsEhXhh3H6Y/qL8v7fGJx67zRd0KMqwdKCFY49dqfRpm6F2YF2+2AhcEW5+jOVeqtOL vY97CxdX9/t9+mE1pznGdfAFZEp6qrbE= X-Gm-Gg: AeBDiev3begi73KirIEYkbTT8YMnn6alDs2uQIv4+4WjSA+8YE0IxnulwTyl7zi5PNt ouEMiqQqfA47/5uIKRAvpka32iXdnAOMqHIx2MXAQWg5gypGl/HCKZumb2/XXkMsSVq0UKT5Jvc HTNtxDDpMcjo1xAAqykDOE1hTOJec7gFsNjjhS4J5L3fLO/rfpCXmHR6ipHquLTdvFPjWlqGIWE m09FDHOD37B4xtqYQdrw0GK0U82+tMSKbT2eaN0kiG3VUs1PUtjVLihkQysOtfnraJ7nQVYOPJb E9vfjAs= X-Received: by 2002:a05:690e:a4f:b0:64d:6bee:2c5b with SMTP id 956f58d0204a3-651875cad39mr2312899d50.16.1775717281457; Wed, 08 Apr 2026 23:48:01 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Lakshmi N Date: Wed, 8 Apr 2026 23:47:48 -0700 X-Gm-Features: AQROBzAlYimr1Kjsd4VkJl-CHj7fDf1arEYxtIqBDTG1CW8ncdDQ5L5UywBZDdU Message-ID: Subject: Re: DOCS: pg_plan_advice minor doc fixes To: jie wang Cc: pgsql-hackers@postgresql.org, pgsql-hackers@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000c869fc064f016526" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000c869fc064f016526 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi Jie, On Wed, Apr 8, 2026 at 12:52=E2=80=AFAM jie wang wro= te: > > > Lakshmi N =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 t= he >> 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` > LGTM. Regards, Lakshmi > --000000000000c869fc064f016526 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Jie,

On Wed, Apr = 8, 2026 at 12:52=E2=80=AFAM jie wang <jugierwang@gmail.com> wrote:


<= div class=3D"gmail_quote">
Lakshmi N &= lt;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 n= oticed the following minor discrepancy in the pg_plan_advice docs, attached= a patch to address this.=C2=A0

(1) FOREIGN SCAN s= hould be FOREIGN JOIN in the scan method advice=C2=A0tag. I tested with FOR= EIGN SCAN and can confirm it gives a syntax error while the other one works= correctly.

postgres=3D# SET pg_plan_advice.advice= =3D 'FOREIGN_JOIN((t1 t2))';=C2=A0
EXPLAIN (COSTS OFF, PLAN_ADV= ICE)
=C2=A0 =C2=A0 SELECT * FROM fdw_t1 t1 JOIN fdw_t2 t2 ON=C2=A0t1.id=C2=A0=3D t2.t1_id;

S= ET
=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=A0Foreig= n Scan
=C2=A0 =C2=A0Relations: (fdw_t1 t1) INNER JOIN (fdw_t2 t2)
=C2= =A0Supplied Plan Advice:
=C2=A0 =C2=A0FOREIGN_JOIN((t1 t2)) /* matched *= /
=C2=A0Generated Plan Advice:
=C2=A0 =C2=A0FOREIGN_JOIN((t1 t2))
= =C2=A0 =C2=A0NO_GATHER(t1 t2)
(7 rows)


=
(2) NESTED_LOOP_MEMOIZE is missing from the join method list in = the doc though it is supported.=C2=A0 Verified this by running below:
=

postgres=3D# SET pg_plan_advice.advice =3D 'NESTED_= LOOP_MEMOIZE(d)';
SET
postgres=3D# EXPLAIN (COSTS OFF, PLAN_ADVIC= E) select * from t1 f JOIN t2 ON=C2=A0f.id=C2=A0=3D t2.t1_id;
=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= Hash Join
=C2=A0 =C2=A0Hash Cond: (t2.t1_id =3D=C2=A0f.id)
=C2=A0 =C2=A0-> =C2=A0Seq Scan on t= 2
=C2=A0 =C2=A0-> =C2=A0Hash
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-&g= t; =C2=A0Seq Scan on t1 f
=C2=A0Supplied Plan Advice:
=C2=A0 =C2=A0NE= STED_LOOP_MEMOIZE(d) /* not matched */
=C2=A0Generated Plan Advice:
= =C2=A0 =C2=A0JOIN_ORDER(t2 f)
=C2=A0 =C2=A0HASH_JOIN(f)
=C2=A0 =C2=A0= SEQ_SCAN(t2 f)
=C2=A0 =C2=A0NO_GATHER(f t2)
(12 rows)

<= /div>
Regards,
Lakshmi


Hi,

I found that this patch is not fully modified an= d is missing a part:
`Foreign Scan` ---> =C2=A0`Foreign Join`

LGTM.

Reg= ards,
Lakshmi
--000000000000c869fc064f016526--