public inbox for [email protected]
help / color / mirror / Atom feedDOCS: pg_plan_advice minor doc fixes
4+ messages / 3 participants
[nested] [flat]
* DOCS: pg_plan_advice minor doc fixes
@ 2026-04-08 07:09 Lakshmi N <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Lakshmi N @ 2026-04-08 07:09 UTC (permalink / raw)
To: pgsql-hackers; [email protected]
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
Attachments:
[application/octet-stream] 0001-doc-Fix-pgplanadvice-tags-for-FOREIGN_JOIN-and-NESTE.patch (2.3K, 3-0001-doc-Fix-pgplanadvice-tags-for-FOREIGN_JOIN-and-NESTE.patch)
download | inline diff:
From a4d65b1f2eff505535097008f5ae55d0ae37d661 Mon Sep 17 00:00:00 2001
From: Lakshmi N <[email protected]>
Date: Tue, 7 Apr 2026 23:58:15 -0700
Subject: [PATCH] DOCS: Update pgplanadvice tags for FOREIGN_JOIN and
NESTED_LOOP_MEMOIZE
---
doc/src/sgml/pgplanadvice.sgml | 8 ++++----
1 file changed, 4 insertions(+), 4 deletions(-)
diff --git a/doc/src/sgml/pgplanadvice.sgml b/doc/src/sgml/pgplanadvice.sgml
index c3e1ccb60a2..73155461c0b 100644
--- a/doc/src/sgml/pgplanadvice.sgml
+++ b/doc/src/sgml/pgplanadvice.sgml
@@ -266,7 +266,7 @@ SEQ_SCAN(<replaceable>target</replaceable> [ ... ])
TID_SCAN(<replaceable>target</replaceable> [ ... ])
INDEX_SCAN(<replaceable>target</replaceable> <replaceable>index_name</replaceable> [ ... ])
INDEX_ONLY_SCAN(<replaceable>target</replaceable> <replaceable>index_name</replaceable> [ ... ])
-FOREIGN_SCAN((<replaceable>target</replaceable> [ ... ]) [ ... ])
+FOREIGN_JOIN((<replaceable>target</replaceable> [ ... ]) [ ... ])
BITMAP_HEAP_SCAN(<replaceable>target</replaceable> [ ... ])
DO_NOT_SCAN(<replaceable>target</replaceable> [ ... ])</synopsis>
@@ -288,10 +288,10 @@ DO_NOT_SCAN(<replaceable>target</replaceable> [ ... ])</synopsis>
</para>
<para>
- <literal>FOREIGN_SCAN</literal> specifies that a join between two or
+ <literal>FOREIGN_JOIN</literal> specifies that a join between two or
more foreign tables should be pushed down to a remote server so
that it can be implemented as a single <literal>Foreign Scan</literal>.
- Specifying <literal>FOREIGN_SCAN</literal> for a single foreign table is
+ Specifying <literal>FOREIGN_JOIN</literal> for a single foreign table is
neither necessary nor permissible: a <literal>Foreign Scan</literal> will
need to be used regardless. If you want to prevent a join from being
pushed down, consider using the <literal>JOIN_ORDER</literal> tag for
@@ -395,7 +395,7 @@ join_method_name(<replaceable>join_method_item</replaceable> [ ... ])
<phrase>where <replaceable>join_method_name</replaceable> is:</phrase>
-{ MERGE_JOIN_MATERIALIZE | MERGE_JOIN_PLAIN | NESTED_LOOP_MATERIALIZE | NESTED_LOOP_PLAIN | HASH_JOIN }
+{ MERGE_JOIN_MATERIALIZE | MERGE_JOIN_PLAIN | NESTED_LOOP_MATERIALIZE | NESTED_LOOP_MEMOIZE | NESTED_LOOP_PLAIN | HASH_JOIN }
<phrase>and <replaceable>join_method_item</replaceable> is:</phrase>
--
2.43.0
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: DOCS: pg_plan_advice minor doc fixes
@ 2026-04-08 07:52 jie wang <[email protected]>
parent: Lakshmi N <[email protected]>
0 siblings, 2 replies; 4+ messages in thread
From: jie wang @ 2026-04-08 07:52 UTC (permalink / raw)
To: Lakshmi N <[email protected]>; +Cc: pgsql-hackers; [email protected]
Lakshmi N <[email protected]> 于2026年4月8日周三 15:09写道：
> 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
>
Hi,
I found that this patch is not fully modified and is missing a part:
`Foreign Scan` ---> `Foreign Join`
Best regards,
--
wang jie
Attachments:
[application/octet-stream] v2-0001-DOCS-Update-pgplanadvice-tags-for-FOREIGN_JOIN-an.patch (2.5K, 3-v2-0001-DOCS-Update-pgplanadvice-tags-for-FOREIGN_JOIN-an.patch)
download | inline diff:
From 83723bf0b96b043b849c747dc2dbb1c85a56f6f8 Mon Sep 17 00:00:00 2001
From: Lakshmi N <[email protected]>
Date: Tue, 7 Apr 2026 23:58:15 -0700
Subject: [PATCH v2] DOCS: Update pgplanadvice tags for FOREIGN_JOIN and
NESTED_LOOP_MEMOIZE
---
doc/src/sgml/pgplanadvice.sgml | 12 ++++++------
1 file changed, 6 insertions(+), 6 deletions(-)
diff --git a/doc/src/sgml/pgplanadvice.sgml b/doc/src/sgml/pgplanadvice.sgml
index c3e1ccb60a2..e8f5edec874 100644
--- a/doc/src/sgml/pgplanadvice.sgml
+++ b/doc/src/sgml/pgplanadvice.sgml
@@ -266,7 +266,7 @@ SEQ_SCAN(<replaceable>target</replaceable> [ ... ])
TID_SCAN(<replaceable>target</replaceable> [ ... ])
INDEX_SCAN(<replaceable>target</replaceable> <replaceable>index_name</replaceable> [ ... ])
INDEX_ONLY_SCAN(<replaceable>target</replaceable> <replaceable>index_name</replaceable> [ ... ])
-FOREIGN_SCAN((<replaceable>target</replaceable> [ ... ]) [ ... ])
+FOREIGN_JOIN((<replaceable>target</replaceable> [ ... ]) [ ... ])
BITMAP_HEAP_SCAN(<replaceable>target</replaceable> [ ... ])
DO_NOT_SCAN(<replaceable>target</replaceable> [ ... ])</synopsis>
@@ -288,11 +288,11 @@ DO_NOT_SCAN(<replaceable>target</replaceable> [ ... ])</synopsis>
</para>
<para>
- <literal>FOREIGN_SCAN</literal> specifies that a join between two or
+ <literal>FOREIGN_JOIN</literal> specifies that a join between two or
more foreign tables should be pushed down to a remote server so
- that it can be implemented as a single <literal>Foreign Scan</literal>.
- Specifying <literal>FOREIGN_SCAN</literal> for a single foreign table is
- neither necessary nor permissible: a <literal>Foreign Scan</literal> will
+ that it can be implemented as a single <literal>Foreign Join</literal>.
+ Specifying <literal>FOREIGN_JOIN</literal> for a single foreign table is
+ neither necessary nor permissible: a <literal>Foreign Join</literal> will
need to be used regardless. If you want to prevent a join from being
pushed down, consider using the <literal>JOIN_ORDER</literal> tag for
that purpose.
@@ -395,7 +395,7 @@ join_method_name(<replaceable>join_method_item</replaceable> [ ... ])
<phrase>where <replaceable>join_method_name</replaceable> is:</phrase>
-{ MERGE_JOIN_MATERIALIZE | MERGE_JOIN_PLAIN | NESTED_LOOP_MATERIALIZE | NESTED_LOOP_PLAIN | HASH_JOIN }
+{ MERGE_JOIN_MATERIALIZE | MERGE_JOIN_PLAIN | NESTED_LOOP_MATERIALIZE | NESTED_LOOP_MEMOIZE | NESTED_LOOP_PLAIN | HASH_JOIN }
<phrase>and <replaceable>join_method_item</replaceable> is:</phrase>
--
2.34.1
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: DOCS: pg_plan_advice minor doc fixes
@ 2026-04-09 06:47 Lakshmi N <[email protected]>
parent: jie wang <[email protected]>
1 sibling, 0 replies; 4+ messages in thread
From: Lakshmi N @ 2026-04-09 06:47 UTC (permalink / raw)
To: jie wang <[email protected]>; +Cc: pgsql-hackers; [email protected]
Hi Jie,
On Wed, Apr 8, 2026 at 12:52 AM jie wang <[email protected]> wrote:
>
>
> Lakshmi N <[email protected]> 于2026年4月8日周三 15:09写道：
>
>> 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
>>
>
>
> Hi,
>
> I found that this patch is not fully modified and is missing a part:
> `Foreign Scan` ---> `Foreign Join`
>
LGTM.
Regards,
Lakshmi
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: DOCS: pg_plan_advice minor doc fixes
@ 2026-04-13 16:49 Robert Haas <[email protected]>
parent: jie wang <[email protected]>
1 sibling, 0 replies; 4+ messages in thread
From: Robert Haas @ 2026-04-13 16:49 UTC (permalink / raw)
To: jie wang <[email protected]>; +Cc: Lakshmi N <[email protected]>; pgsql-hackers; [email protected]
On Wed, Apr 8, 2026 at 3:52 AM jie wang <[email protected]> wrote:
> I found that this patch is not fully modified and is missing a part:
> `Foreign Scan` ---> `Foreign Join`
I believe you're wrong about this. EXPLAIN shows "Foreign Scan", not
"Foreign Join".
Committed without this change.
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2026-04-13 16:49 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-04-08 07:09 DOCS: pg_plan_advice minor doc fixes Lakshmi N <[email protected]>
2026-04-08 07:52 ` jie wang <[email protected]>
2026-04-09 06:47 ` Lakshmi N <[email protected]>
2026-04-13 16:49 ` Robert Haas <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox