public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v3 1/1] Handle a NULL short_desc in ShowAllGUCConfig
11+ messages / 3 participants
[nested] [flat]
* [PATCH v3 1/1] Handle a NULL short_desc in ShowAllGUCConfig
@ 2022-05-25 04:46 Steve Chavez <[email protected]>
0 siblings, 0 replies; 11+ messages in thread
From: Steve Chavez @ 2022-05-25 04:46 UTC (permalink / raw)
Prevent a segfault when using a SHOW ALL, in case a DefineCustomXXXVariable()
was defined with a NULL short_desc.
---
src/backend/utils/misc/guc.c | 16 +++++++++++++---
src/include/c.h | 10 ++++++++++
src/include/utils/guc.h | 10 +++++-----
3 files changed, 28 insertions(+), 8 deletions(-)
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 8e9b71375c..55d41ae7d6 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -9780,7 +9780,16 @@ ShowAllGUCConfig(DestReceiver *dest)
isnull[1] = true;
}
- values[2] = PointerGetDatum(cstring_to_text(conf->short_desc));
+ if (conf->short_desc)
+ {
+ values[2] = PointerGetDatum(cstring_to_text(conf->short_desc));
+ isnull[2] = false;
+ }
+ else
+ {
+ values[2] = PointerGetDatum(NULL);
+ isnull[2] = true;
+ }
/* send it to dest */
do_tup_output(tstate, values, isnull);
@@ -9792,7 +9801,8 @@ ShowAllGUCConfig(DestReceiver *dest)
pfree(setting);
pfree(DatumGetPointer(values[1]));
}
- pfree(DatumGetPointer(values[2]));
+ if (conf->short_desc)
+ pfree(DatumGetPointer(values[2]));
}
end_tup_output(tstate);
@@ -10002,7 +10012,7 @@ GetConfigOptionByNum(int varnum, const char **values, bool *noshow)
values[3] = _(config_group_names[conf->group]);
/* short_desc */
- values[4] = _(conf->short_desc);
+ values[4] = conf->short_desc != NULL ? _(conf->short_desc) : NULL;
/* extra_desc */
values[5] = conf->long_desc != NULL ? _(conf->long_desc) : NULL;
diff --git a/src/include/c.h b/src/include/c.h
index 4f16e589b3..f253aa930b 100644
--- a/src/include/c.h
+++ b/src/include/c.h
@@ -144,6 +144,16 @@
#define pg_attribute_no_sanitize_alignment()
#endif
+/*
+ * pg_attribute_nonnull means the compiler should warn if the function is called
+ * with the listed arguments set to NULL.
+ */
+#if defined(__clang_major__) || defined(__GNUC__)
+#define pg_attribute_nonnull(...) __attribute__((nonnull(__VA_ARGS__)))
+#else
+#define pg_attribute_nonnull(...)
+#endif
+
/*
* Append PG_USED_FOR_ASSERTS_ONLY to definitions of variables that are only
* used in assert-enabled builds, to avoid compiler warnings about unused
diff --git a/src/include/utils/guc.h b/src/include/utils/guc.h
index efcbad7842..be691c5e9c 100644
--- a/src/include/utils/guc.h
+++ b/src/include/utils/guc.h
@@ -303,7 +303,7 @@ extern void DefineCustomBoolVariable(const char *name,
int flags,
GucBoolCheckHook check_hook,
GucBoolAssignHook assign_hook,
- GucShowHook show_hook);
+ GucShowHook show_hook) pg_attribute_nonnull(1, 4);
extern void DefineCustomIntVariable(const char *name,
const char *short_desc,
@@ -316,7 +316,7 @@ extern void DefineCustomIntVariable(const char *name,
int flags,
GucIntCheckHook check_hook,
GucIntAssignHook assign_hook,
- GucShowHook show_hook);
+ GucShowHook show_hook) pg_attribute_nonnull(1, 4);
extern void DefineCustomRealVariable(const char *name,
const char *short_desc,
@@ -329,7 +329,7 @@ extern void DefineCustomRealVariable(const char *name,
int flags,
GucRealCheckHook check_hook,
GucRealAssignHook assign_hook,
- GucShowHook show_hook);
+ GucShowHook show_hook) pg_attribute_nonnull(1, 4);
extern void DefineCustomStringVariable(const char *name,
const char *short_desc,
@@ -340,7 +340,7 @@ extern void DefineCustomStringVariable(const char *name,
int flags,
GucStringCheckHook check_hook,
GucStringAssignHook assign_hook,
- GucShowHook show_hook);
+ GucShowHook show_hook) pg_attribute_nonnull(1, 4);
extern void DefineCustomEnumVariable(const char *name,
const char *short_desc,
@@ -352,7 +352,7 @@ extern void DefineCustomEnumVariable(const char *name,
int flags,
GucEnumCheckHook check_hook,
GucEnumAssignHook assign_hook,
- GucShowHook show_hook);
+ GucShowHook show_hook) pg_attribute_nonnull(1, 4);
extern void MarkGUCPrefixReserved(const char *className);
--
2.25.1
--Dxnq1zWXvFF0Q93v--
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: explain plans for foreign servers
@ 2025-11-18 03:25 dinesh salve <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: dinesh salve @ 2025-11-18 03:25 UTC (permalink / raw)
To: Sami Imseih <[email protected]>; +Cc: Tom Lane <[email protected]>; pgsql-hackers; Jeff Davis <[email protected]>
Hi Sami,
Thanks for the feedback. I have refactored the commit on the latest version
of PG and added a few more tests. To simplify the roll out of this feature,
I decided to work on analyze=false use case first. Please find the attached
patch for the same.
Regards,
Dinesh
On Thu, Mar 6, 2025 at 4:54 AM Jeff Davis <[email protected]> wrote:
> On Wed, 2025-03-05 at 14:12 -0500, Tom Lane wrote:
> > I'm afraid not. That pretty fundamentally breaks the wire protocol,
> > I think.
>
> The extended protocol docs say: "The possible responses to Execute are
> the same as those described above for queries issued via simple query
> protocol, except that Execute doesn't cause ReadyForQuery or
> RowDescription to be issued."
>
> Each result set needs a RowDescription, so I think you're right that it
> breaks the extended protocol. I missed that the first time.
>
> Regards,
> Jeff Davis
>
>
--
Regards,
Dinesh Salve
Cell:- 91 8125898845
Attachments:
[application/octet-stream] 0001-This-change-adds-capability-to-fetch-explain-plans-f.patch (59.1K, ../../CAP+B4TDxE3gbrixL9qR9F+CK_N_N0-HmXSL55QXVZNAorRHSHQ@mail.gmail.com/3-0001-This-change-adds-capability-to-fetch-explain-plans-f.patch)
download | inline diff:
From b5908a7ac4ea6f40493cb0d5638d6a254a4fb768 Mon Sep 17 00:00:00 2001
From: Dinesh Salve <[email protected]>
Date: Wed, 5 Nov 2025 05:49:11 +0000
Subject: [PATCH 1/1] This change adds capability to fetch explain plans for
foreign tables. We have introduced new option "remote_plans" to achieve the
same. This option does not work with ANALYZE option yet.
---
.../postgres_fdw/expected/postgres_fdw.out | 581 ++++++++++++++++++
contrib/postgres_fdw/option.c | 71 +++
contrib/postgres_fdw/postgres_fdw.c | 256 +++++++-
contrib/postgres_fdw/postgres_fdw.h | 26 +
contrib/postgres_fdw/sql/postgres_fdw.sql | 39 ++
5 files changed, 964 insertions(+), 9 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index cd28126049d..5496a6ddea5 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -441,6 +441,173 @@ SELECT 'fixed', NULL FROM ft1 t1 WHERE c1 = 1;
fixed |
(1 row)
+-- with WHERE clause and remote_plans with different formats
+EXPLAIN (REMOTE_PLANS, FORMAT YAML, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------
+ - Plan: +
+ Node Type: "Foreign Scan" +
+ Operation: "Select" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Relation Name: "ft1" +
+ Schema: "public" +
+ Alias: "t1" +
+ Disabled: false +
+ Output: +
+ - "c1" +
+ - "c2" +
+ - "c3" +
+ - "c4" +
+ - "c5" +
+ - "c6" +
+ - "c7" +
+ - "c8" +
+ Remote SQL: "SELECT \"C 1\", c2, c3, c4, c5, c6, c7, c8 FROM \"S 1\".\"T 1\" WHERE ((\"C 1\" = 101))"+
+ Plan Node ID: 0 +
+ Remote Plans: +
+ Plan Node ID 0: +
+ - Plan: +
+ Node Type: "Index Scan" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Scan Direction: "Forward" +
+ Index Name: "t1_pkey" +
+ Relation Name: "T 1" +
+ Schema: "S 1" +
+ Alias: "T 1" +
+ Disabled: false +
+ Output: +
+ - "\"C 1\"" +
+ - "c2" +
+ - "c3" +
+ - "c4" +
+ - "c5" +
+ - "c6" +
+ - "c7" +
+ - "c8" +
+ Index Cond: "(\"T 1\".\"C 1\" = 101)"
+(1 row)
+
+EXPLAIN (REMOTE_PLANS TRUE, FORMAT XML, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------
+ <explain xmlns="http://www.postgresql.org/2009/explain"> +
+ <Query> +
+ <Plan> +
+ <Node-Type>Foreign Scan</Node-Type> +
+ <Operation>Select</Operation> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Relation-Name>ft1</Relation-Name> +
+ <Schema>public</Schema> +
+ <Alias>t1</Alias> +
+ <Disabled>false</Disabled> +
+ <Output> +
+ <Item>c1</Item> +
+ <Item>c2</Item> +
+ <Item>c3</Item> +
+ <Item>c4</Item> +
+ <Item>c5</Item> +
+ <Item>c6</Item> +
+ <Item>c7</Item> +
+ <Item>c8</Item> +
+ </Output> +
+ <Remote-SQL>SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 101))</Remote-SQL>+
+ <Plan-Node-ID>0</Plan-Node-ID> +
+ </Plan> +
+ <Remote-Plans> +
+ <Plan-Node-ID-0> +
+ <explain xmlns="http://www.postgresql.org/2009/explain"> +
+ <Query> +
+ <Plan> +
+ <Node-Type>Index Scan</Node-Type> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Scan-Direction>Forward</Scan-Direction> +
+ <Index-Name>t1_pkey</Index-Name> +
+ <Relation-Name>T 1</Relation-Name> +
+ <Schema>S 1</Schema> +
+ <Alias>T 1</Alias> +
+ <Disabled>false</Disabled> +
+ <Output> +
+ <Item>"C 1"</Item> +
+ <Item>c2</Item> +
+ <Item>c3</Item> +
+ <Item>c4</Item> +
+ <Item>c5</Item> +
+ <Item>c6</Item> +
+ <Item>c7</Item> +
+ <Item>c8</Item> +
+ </Output> +
+ <Index-Cond>("T 1"."C 1" = 101)</Index-Cond> +
+ </Plan> +
+ </Query> +
+ </explain> +
+ </Plan-Node-ID-0> +
+ </Remote-Plans> +
+ </Query> +
+ </explain>
+(1 row)
+
+EXPLAIN (REMOTE_PLANS, FORMAT JSON, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Foreign Scan", +
+ "Operation": "Select", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Relation Name": "ft1", +
+ "Schema": "public", +
+ "Alias": "t1", +
+ "Disabled": false, +
+ "Output": ["c1", "c2", "c3", "c4", "c5", "c6", "c7", "c8"], +
+ "Remote SQL": "SELECT \"C 1\", c2, c3, c4, c5, c6, c7, c8 FROM \"S 1\".\"T 1\" WHERE ((\"C 1\" = 101))",+
+ "Plan Node ID": 0 +
+ }, +
+ "Remote Plans": { +
+ "Plan Node ID 0": [ +
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Index Scan", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Scan Direction": "Forward", +
+ "Index Name": "t1_pkey", +
+ "Relation Name": "T 1", +
+ "Schema": "S 1", +
+ "Alias": "T 1", +
+ "Disabled": false, +
+ "Output": ["\"C 1\"", "c2", "c3", "c4", "c5", "c6", "c7", "c8"], +
+ "Index Cond": "(\"T 1\".\"C 1\" = 101)" +
+ } +
+ } +
+ ] +
+ ] +
+ } +
+ } +
+ ]
+(1 row)
+
+EXPLAIN (REMOTE_PLANS TRUE, FORMAT TEXT, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------
+ Foreign Scan on public.ft1 t1
+ Output: c1, c2, c3, c4, c5, c6, c7, c8
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 101))
+ Plan Node ID: 0
+ Remote Plans:
+ -------------
+ Plan Node ID 0:
+ Index Scan using t1_pkey on "S 1"."T 1"
+ Output: "C 1", c2, c3, c4, c5, c6, c7, c8
+ Index Cond: ("T 1"."C 1" = 101)
+(10 rows)
+
-- Test forcing the remote server to produce sorted data for a merge join.
SET enable_hashjoin TO false;
SET enable_nestloop TO false;
@@ -5086,6 +5253,373 @@ SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
Remote SQL: SELECT r5."C 1", r6.c1 FROM ("S 1"."T 1" r5 INNER JOIN "S 1"."T 3" r6 ON (((r5."C 1" = r6.c1)))) ORDER BY r5."C 1" ASC NULLS LAST
(13 rows)
+-- EXPLAIN remote_plans
+EXPLAIN (remote_plans, format text, costs off, analyze)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+ERROR: EXPLAIN options REMOTE_PLANS and ANALYZE cannot be used together
+EXPLAIN (remote_plans, format text, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+ QUERY PLAN
+-------------------------------------------------------
+ Limit
+ Plan Node ID: 0
+ -> Merge Semi Join
+ Merge Cond: (ft1.c1 = ft2_1.c1)
+ Plan Node ID: 1
+ -> Foreign Scan
+ Relations: (ft1) INNER JOIN (ft2)
+ Plan Node ID: 2
+ -> Foreign Scan
+ Relations: (ft2 ft2_1) INNER JOIN (ft4)
+ Plan Node ID: 3
+ Remote Plans:
+ -------------
+ Plan Node ID 2:
+ Index Only Scan using t1_pkey on "T 1" r2
+ Plan Node ID 3:
+ Merge Join
+ Merge Cond: (r5."C 1" = r6.c1)
+ -> Index Only Scan using t1_pkey on "T 1" r5
+ -> Sort
+ Sort Key: r6.c1
+ -> Seq Scan on "T 3" r6
+(22 rows)
+
+EXPLAIN (remote_plans, format xml, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+ QUERY PLAN
+------------------------------------------------------------------------
+ <explain xmlns="http://www.postgresql.org/2009/explain"> +
+ <Query> +
+ <Plan> +
+ <Node-Type>Limit</Node-Type> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Disabled>false</Disabled> +
+ <Plan-Node-ID>0</Plan-Node-ID> +
+ <Plans> +
+ <Plan> +
+ <Node-Type>Merge Join</Node-Type> +
+ <Parent-Relationship>Outer</Parent-Relationship> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Join-Type>Semi</Join-Type> +
+ <Disabled>false</Disabled> +
+ <Inner-Unique>false</Inner-Unique> +
+ <Merge-Cond>(ft1.c1 = ft2_1.c1)</Merge-Cond> +
+ <Plan-Node-ID>1</Plan-Node-ID> +
+ <Plans> +
+ <Plan> +
+ <Node-Type>Foreign Scan</Node-Type> +
+ <Operation>Select</Operation> +
+ <Parent-Relationship>Outer</Parent-Relationship> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Disabled>false</Disabled> +
+ <Relations>(ft1) INNER JOIN (ft2)</Relations> +
+ <Plan-Node-ID>2</Plan-Node-ID> +
+ </Plan> +
+ <Plan> +
+ <Node-Type>Foreign Scan</Node-Type> +
+ <Operation>Select</Operation> +
+ <Parent-Relationship>Inner</Parent-Relationship> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Disabled>false</Disabled> +
+ <Relations>(ft2 ft2_1) INNER JOIN (ft4)</Relations> +
+ <Plan-Node-ID>3</Plan-Node-ID> +
+ </Plan> +
+ </Plans> +
+ </Plan> +
+ </Plans> +
+ </Plan> +
+ <Remote-Plans> +
+ <Plan-Node-ID-2> +
+ <explain xmlns="http://www.postgresql.org/2009/explain"> +
+ <Query> +
+ <Plan> +
+ <Node-Type>Index Only Scan</Node-Type> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Scan-Direction>Forward</Scan-Direction> +
+ <Index-Name>t1_pkey</Index-Name> +
+ <Relation-Name>T 1</Relation-Name> +
+ <Alias>r2</Alias> +
+ <Disabled>false</Disabled> +
+ </Plan> +
+ </Query> +
+ </explain> +
+ </Plan-Node-ID-2> +
+ <Plan-Node-ID-3> +
+ <explain xmlns="http://www.postgresql.org/2009/explain"> +
+ <Query> +
+ <Plan> +
+ <Node-Type>Merge Join</Node-Type> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Join-Type>Inner</Join-Type> +
+ <Disabled>false</Disabled> +
+ <Inner-Unique>true</Inner-Unique> +
+ <Merge-Cond>(r5."C 1" = r6.c1)</Merge-Cond> +
+ <Plans> +
+ <Plan> +
+ <Node-Type>Index Only Scan</Node-Type> +
+ <Parent-Relationship>Outer</Parent-Relationship> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Scan-Direction>Forward</Scan-Direction> +
+ <Index-Name>t1_pkey</Index-Name> +
+ <Relation-Name>T 1</Relation-Name> +
+ <Alias>r5</Alias> +
+ <Disabled>false</Disabled> +
+ </Plan> +
+ <Plan> +
+ <Node-Type>Sort</Node-Type> +
+ <Parent-Relationship>Inner</Parent-Relationship> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Disabled>false</Disabled> +
+ <Sort-Key> +
+ <Item>r6.c1</Item> +
+ </Sort-Key> +
+ <Plans> +
+ <Plan> +
+ <Node-Type>Seq Scan</Node-Type> +
+ <Parent-Relationship>Outer</Parent-Relationship>+
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Relation-Name>T 3</Relation-Name> +
+ <Alias>r6</Alias> +
+ <Disabled>false</Disabled> +
+ </Plan> +
+ </Plans> +
+ </Plan> +
+ </Plans> +
+ </Plan> +
+ </Query> +
+ </explain> +
+ </Plan-Node-ID-3> +
+ </Remote-Plans> +
+ </Query> +
+ </explain>
+(1 row)
+
+EXPLAIN (remote_plans, format json, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+ QUERY PLAN
+------------------------------------------------------------
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Limit", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Disabled": false, +
+ "Plan Node ID": 0, +
+ "Plans": [ +
+ { +
+ "Node Type": "Merge Join", +
+ "Parent Relationship": "Outer", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Join Type": "Semi", +
+ "Disabled": false, +
+ "Inner Unique": false, +
+ "Merge Cond": "(ft1.c1 = ft2_1.c1)", +
+ "Plan Node ID": 1, +
+ "Plans": [ +
+ { +
+ "Node Type": "Foreign Scan", +
+ "Operation": "Select", +
+ "Parent Relationship": "Outer", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Disabled": false, +
+ "Relations": "(ft1) INNER JOIN (ft2)", +
+ "Plan Node ID": 2 +
+ }, +
+ { +
+ "Node Type": "Foreign Scan", +
+ "Operation": "Select", +
+ "Parent Relationship": "Inner", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Disabled": false, +
+ "Relations": "(ft2 ft2_1) INNER JOIN (ft4)",+
+ "Plan Node ID": 3 +
+ } +
+ ] +
+ } +
+ ] +
+ }, +
+ "Remote Plans": { +
+ "Plan Node ID 2": [ +
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Index Only Scan", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Scan Direction": "Forward", +
+ "Index Name": "t1_pkey", +
+ "Relation Name": "T 1", +
+ "Alias": "r2", +
+ "Disabled": false +
+ } +
+ } +
+ ] +
+ ], +
+ "Plan Node ID 3": [ +
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Merge Join", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Join Type": "Inner", +
+ "Disabled": false, +
+ "Inner Unique": true, +
+ "Merge Cond": "(r5.\"C 1\" = r6.c1)", +
+ "Plans": [ +
+ { +
+ "Node Type": "Index Only Scan", +
+ "Parent Relationship": "Outer", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Scan Direction": "Forward", +
+ "Index Name": "t1_pkey", +
+ "Relation Name": "T 1", +
+ "Alias": "r5", +
+ "Disabled": false +
+ }, +
+ { +
+ "Node Type": "Sort", +
+ "Parent Relationship": "Inner", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Disabled": false, +
+ "Sort Key": ["r6.c1"], +
+ "Plans": [ +
+ { +
+ "Node Type": "Seq Scan", +
+ "Parent Relationship": "Outer", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Relation Name": "T 3", +
+ "Alias": "r6", +
+ "Disabled": false +
+ } +
+ ] +
+ } +
+ ] +
+ } +
+ } +
+ ] +
+ ] +
+ } +
+ } +
+ ]
+(1 row)
+
+EXPLAIN (remote_plans, format yaml, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+ QUERY PLAN
+-------------------------------------------------------
+ - Plan: +
+ Node Type: "Limit" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Disabled: false +
+ Plan Node ID: 0 +
+ Plans: +
+ - Node Type: "Merge Join" +
+ Parent Relationship: "Outer" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Join Type: "Semi" +
+ Disabled: false +
+ Inner Unique: false +
+ Merge Cond: "(ft1.c1 = ft2_1.c1)" +
+ Plan Node ID: 1 +
+ Plans: +
+ - Node Type: "Foreign Scan" +
+ Operation: "Select" +
+ Parent Relationship: "Outer" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Disabled: false +
+ Relations: "(ft1) INNER JOIN (ft2)" +
+ Plan Node ID: 2 +
+ - Node Type: "Foreign Scan" +
+ Operation: "Select" +
+ Parent Relationship: "Inner" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Disabled: false +
+ Relations: "(ft2 ft2_1) INNER JOIN (ft4)"+
+ Plan Node ID: 3 +
+ Remote Plans: +
+ Plan Node ID 2: +
+ - Plan: +
+ Node Type: "Index Only Scan" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Scan Direction: "Forward" +
+ Index Name: "t1_pkey" +
+ Relation Name: "T 1" +
+ Alias: "r2" +
+ Disabled: false +
+ Plan Node ID 3: +
+ - Plan: +
+ Node Type: "Merge Join" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Join Type: "Inner" +
+ Disabled: false +
+ Inner Unique: true +
+ Merge Cond: "(r5.\"C 1\" = r6.c1)" +
+ Plans: +
+ - Node Type: "Index Only Scan" +
+ Parent Relationship: "Outer" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Scan Direction: "Forward" +
+ Index Name: "t1_pkey" +
+ Relation Name: "T 1" +
+ Alias: "r5" +
+ Disabled: false +
+ - Node Type: "Sort" +
+ Parent Relationship: "Inner" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Disabled: false +
+ Sort Key: +
+ - "r6.c1" +
+ Plans: +
+ - Node Type: "Seq Scan" +
+ Parent Relationship: "Outer" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Relation Name: "T 3" +
+ Alias: "r6" +
+ Disabled: false
+(1 row)
+
-- ===================================================================
-- test writable foreign table stuff
-- ===================================================================
@@ -6303,6 +6837,25 @@ DELETE FROM ft2 WHERE c1 = 1200 RETURNING tableoid::regclass;
ft2
(1 row)
+-- test write on foreign tables with remote_plans
+EXPLAIN (remote_plans, verbose, costs off)
+UPDATE ft2 SET c2 = c2 + 300 WHERE c1 % 10 = 3;
+ QUERY PLAN
+---------------------------------------------------------------------------------------
+ Update on public.ft2
+ Plan Node ID: 0
+ -> Foreign Update on public.ft2
+ Remote SQL: UPDATE "S 1"."T 1" SET c2 = (c2 + 300) WHERE ((("C 1" % 10) = 3))
+ Plan Node ID: 1
+ Remote Plans:
+ -------------
+ Plan Node ID 1:
+ Update on "S 1"."T 1"
+ -> Seq Scan on "S 1"."T 1"
+ Output: (c2 + 300), ctid
+ Filter: (("T 1"."C 1" % 10) = 3)
+(12 rows)
+
-- Test UPDATE/DELETE with RETURNING on a three-table join
INSERT INTO ft2 (c1,c2,c3)
SELECT id, id - 1200, to_char(id, 'FM00000') FROM generate_series(1201, 1300) id;
@@ -12260,6 +12813,34 @@ SELECT * FROM insert_tbl ORDER BY a;
2505 | 505 | bar
(2 rows)
+EXPLAIN (REMOTE_PLANS, VERBOSE, COSTS OFF)
+INSERT INTO insert_tbl (SELECT * FROM local_tbl UNION ALL SELECT * FROM remote_tbl);
+ QUERY PLAN
+-------------------------------------------------------------------------
+ Insert on public.insert_tbl
+ Remote SQL: INSERT INTO public.base_tbl4(a, b, c) VALUES ($1, $2, $3)
+ Batch Size: 1
+ Plan Node ID: 0
+ -> Append
+ Plan Node ID: 1
+ -> Seq Scan on public.local_tbl
+ Output: local_tbl.a, local_tbl.b, local_tbl.c
+ Plan Node ID: 2
+ -> Async Foreign Scan on public.remote_tbl
+ Output: remote_tbl.a, remote_tbl.b, remote_tbl.c
+ Remote SQL: SELECT a, b, c FROM public.base_tbl3
+ Plan Node ID: 3
+ Remote Plans:
+ -------------
+ Plan Node ID 0:
+ Insert on public.base_tbl4
+ -> Result
+ Output: $1, $2, $3
+ Plan Node ID 3:
+ Seq Scan on public.base_tbl3
+ Output: a, b, c
+(22 rows)
+
-- Check with direct modify
EXPLAIN (VERBOSE, COSTS OFF)
WITH t AS (UPDATE remote_tbl SET c = c || c RETURNING *)
diff --git a/contrib/postgres_fdw/option.c b/contrib/postgres_fdw/option.c
index 04788b7e8b3..a3b87d2bbaf 100644
--- a/contrib/postgres_fdw/option.c
+++ b/contrib/postgres_fdw/option.c
@@ -17,6 +17,8 @@
#include "catalog/pg_foreign_table.h"
#include "catalog/pg_user_mapping.h"
#include "commands/defrem.h"
+#include "commands/explain.h"
+#include "commands/explain_state.h"
#include "commands/extension.h"
#include "libpq/libpq-be.h"
#include "postgres_fdw.h"
@@ -40,6 +42,13 @@ typedef struct PgFdwOption
*/
static PgFdwOption *postgres_fdw_options;
+/*
+ * EXPLAIN hooks
+ */
+static explain_per_node_hook_type prev_explain_per_node_hook;
+static explain_per_plan_hook_type prev_explain_per_plan_hook;
+static explain_validate_options_hook_type prev_explain_validate_options_hook;
+
/*
* GUC parameters
*/
@@ -561,6 +570,57 @@ process_pgfdw_appname(const char *appname)
return buf.data;
}
+/*
+ * Get the PgFdwExplainState structure from an ExplainState; if there is
+ * none, create one, attach it to the ExplainState, and return it.
+ */
+static PgFdwExplainState *
+pgfdw_ensure_options(ExplainState *es)
+{
+ PgFdwExplainState *pgfdw_explain_state;
+
+ pgfdw_explain_state = GetExplainExtensionState(es, GetExplainExtensionId("postgres_fdw"));
+
+ if (pgfdw_explain_state == NULL)
+ {
+ pgfdw_explain_state = palloc0(sizeof(PgFdwExplainState));
+ SetExplainExtensionState(es, GetExplainExtensionId("postgres_fdw"), pgfdw_explain_state);
+ pgfdw_explain_state->all_remote_plans = NIL;
+ }
+
+ return pgfdw_explain_state;
+}
+
+/*
+ * Parse handler for EXPLAIN (REMOTE_PLANS).
+ */
+static void
+pgfdw_remote_plans_apply(ExplainState *es, DefElem *opt, ParseState *pstate)
+{
+ PgFdwExplainState *options = pgfdw_ensure_options(es);
+
+ options->remote_plans = defGetBoolean(opt);
+}
+
+static void
+postgresExplainValidateOptions(ExplainState *es, List *options, ParseState *pstate)
+{
+ ListCell *lc;
+
+ foreach(lc, options)
+ {
+ DefElem *opt = (DefElem *) lfirst(lc);
+
+ if (strcmp(opt->defname, "remote_plans") == 0)
+ {
+ if (defGetBoolean(opt) && es->analyze)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("EXPLAIN options REMOTE_PLANS and ANALYZE cannot be used together"));
+ }
+ }
+}
+
/*
* Module load callback
*/
@@ -587,4 +647,15 @@ _PG_init(void)
NULL);
MarkGUCPrefixReserved("postgres_fdw");
+
+ RegisterExtensionExplainOption("remote_plans", pgfdw_remote_plans_apply);
+
+ /* per node EXPLAIN hook */
+ prev_explain_per_node_hook = explain_per_node_hook;
+ explain_per_node_hook = postgresExplainPerNode;
+ prev_explain_per_plan_hook = explain_per_plan_hook;
+ explain_per_plan_hook = postgresExplainPerPlan;
+ prev_explain_validate_options_hook = explain_validate_options_hook;
+ explain_validate_options_hook = postgresExplainValidateOptions;
+
}
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 06b52c65300..9b99a2386b3 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -43,6 +43,7 @@
#include "utils/builtins.h"
#include "utils/float.h"
#include "utils/guc.h"
+#include "utils/json.h"
#include "utils/lsyscache.h"
#include "utils/memutils.h"
#include "utils/rel.h"
@@ -133,6 +134,20 @@ enum FdwDirectModifyPrivateIndex
FdwDirectModifyPrivateSetProcessed,
};
+static const char *const explain_formats[] = {
+ [EXPLAIN_FORMAT_TEXT] = "TEXT",
+ [EXPLAIN_FORMAT_JSON] = "JSON",
+ [EXPLAIN_FORMAT_XML] = "XML",
+ [EXPLAIN_FORMAT_YAML] = "YAML",
+};
+
+/*
+ * Track the extension id in the backend.
+ */
+static int extension_id = -1;
+#define GET_EXTENSION_ID() ((extension_id == -1) ? \
+ GetExplainExtensionId("postgres_fdw"): extension_id)
+
/*
* Execution state of a foreign scan using postgres_fdw.
*/
@@ -2822,6 +2837,65 @@ postgresEndDirectModify(ForeignScanState *node)
/* MemoryContext will be deleted automatically. */
}
+static void
+postgresExplainStatement(int plan_node_id,
+ ExplainState *es,
+ PgFdwExplainState * pgfdw_explain_state,
+ PGconn *conn,
+ char *sql)
+{
+ PGresult *volatile res = NULL;
+ StringInfoData explain_sql;
+
+ PG_TRY();
+ {
+ int numrows,
+ i;
+ PgFdwExplainRemotePlans *explain = (PgFdwExplainRemotePlans *) palloc(sizeof(PgFdwExplainRemotePlans));
+
+ initStringInfo(&explain_sql);
+ initStringInfo(&explain->explain_plan);
+
+ appendStringInfo(&explain_sql, "EXPLAIN (\
+ GENERIC_PLAN 1, \
+ FORMAT %s, \
+ VERBOSE %d, \
+ COSTS %d, \
+ SETTINGS %d) \
+ %s",
+ explain_formats[es->format],
+ (es->verbose) ? 1 : 0,
+ (es->costs) ? 1 : 0,
+ (es->settings) ? 1 : 0,
+ sql);
+
+ /* Run the query and collect the remote plan */
+ res = pgfdw_exec_query(conn, explain_sql.data, NULL);
+ if (PQresultStatus(res) != PGRES_TUPLES_OK)
+ pgfdw_report_error(res, conn, explain_sql.data);
+
+ numrows = PQntuples(res);
+
+ for (i = 0; i < numrows; i++)
+ appendStringInfo(&explain->explain_plan, "%s\n", pstrdup(PQgetvalue(res, i, 0)));
+
+ if (explain->explain_plan.len > 0 && explain->explain_plan.data[explain->explain_plan.len - 1] == '\n')
+ explain->explain_plan.data[--explain->explain_plan.len] = '\0';
+
+ explain->plan_node_id = plan_node_id;
+ pgfdw_explain_state->all_remote_plans = lappend(pgfdw_explain_state->all_remote_plans, explain);
+ }
+ PG_FINALLY();
+ {
+ if (res)
+ PQclear(res);
+
+ if (explain_sql.data)
+ pfree(explain_sql.data);
+ }
+ PG_END_TRY();
+}
+
/*
* postgresExplainForeignScan
* Produce extra output for EXPLAIN of a ForeignScan on a foreign table
@@ -2831,6 +2905,9 @@ postgresExplainForeignScan(ForeignScanState *node, ExplainState *es)
{
ForeignScan *plan = castNode(ForeignScan, node->ss.ps.plan);
List *fdw_private = plan->fdw_private;
+ PgFdwExplainState *pgfdw_explain_state;
+ char *sql;
+ List *foreign_scan_table = NIL;
/*
* Identify foreign scans that are really joins or upper relations. The
@@ -2892,6 +2969,14 @@ postgresExplainForeignScan(ForeignScanState *node, ExplainState *es)
Assert(rte->rtekind == RTE_RELATION);
/* This logic should agree with explain.c's ExplainTargetRel */
relname = get_rel_name(rte->relid);
+
+ /*
+ * add one of the tables to foreign_scan_table to get the
+ * serverId for remote plans
+ */
+ if (list_length(foreign_scan_table) == 0)
+ foreign_scan_table = lappend_oid(foreign_scan_table, rte->relid);
+
if (es->verbose)
{
char *namespace;
@@ -2917,15 +3002,38 @@ postgresExplainForeignScan(ForeignScanState *node, ExplainState *es)
ExplainPropertyText("Relations", relations.data, es);
}
+ sql = strVal(list_nth(fdw_private, FdwScanPrivateSelectSql));
+
/*
* Add remote query, when VERBOSE option is specified.
*/
if (es->verbose)
+ ExplainPropertyText("Remote SQL", sql, es);
+
+ pgfdw_explain_state = GetExplainExtensionState(es, GET_EXTENSION_ID());
+
+ if (pgfdw_explain_state && pgfdw_explain_state->remote_plans)
{
- char *sql;
+ UserMapping *user = NULL;
+ PGconn *conn = NULL;
+ ForeignTable *table;
- sql = strVal(list_nth(fdw_private, FdwScanPrivateSelectSql));
- ExplainPropertyText("Remote SQL", sql, es);
+ if (node && !node->ss.ss_currentRelation &&
+ foreign_scan_table == NIL)
+ return;
+
+ if (node && node->ss.ss_currentRelation)
+ table = GetForeignTable(RelationGetRelid(node->ss.ss_currentRelation));
+ else
+ table = GetForeignTable(list_nth_oid(foreign_scan_table, 0));
+
+ Assert(table);
+
+ user = GetUserMapping(GetUserId(), table->serverid);
+ conn = GetConnection(user, false, NULL);
+
+ postgresExplainStatement(node->ss.ps.plan->plan_node_id, es, pgfdw_explain_state, conn, sql);
+ ReleaseConnection(conn);
}
}
@@ -2940,11 +3048,12 @@ postgresExplainForeignModify(ModifyTableState *mtstate,
int subplan_index,
ExplainState *es)
{
+ char *sql = strVal(list_nth(fdw_private,
+ FdwModifyPrivateUpdateSql));
+ PgFdwExplainState *pgfdw_explain_state;
+
if (es->verbose)
{
- char *sql = strVal(list_nth(fdw_private,
- FdwModifyPrivateUpdateSql));
-
ExplainPropertyText("Remote SQL", sql, es);
/*
@@ -2954,6 +3063,24 @@ postgresExplainForeignModify(ModifyTableState *mtstate,
if (rinfo->ri_BatchSize > 0)
ExplainPropertyInteger("Batch Size", NULL, rinfo->ri_BatchSize, es);
}
+
+ pgfdw_explain_state = GetExplainExtensionState(es, GET_EXTENSION_ID());
+ if (pgfdw_explain_state && pgfdw_explain_state->remote_plans)
+ {
+ UserMapping *user = NULL;
+ PGconn *conn = NULL;
+ ForeignTable *table;
+
+ table = GetForeignTable(rinfo->ri_RelationDesc->rd_rel->oid);
+
+ Assert(table);
+
+ user = GetUserMapping(GetUserId(), table->serverid);
+ conn = GetConnection(user, false, NULL);
+
+ postgresExplainStatement(mtstate->ps.plan->plan_node_id, es, pgfdw_explain_state, conn, sql);
+ ReleaseConnection(conn);
+ }
}
/*
@@ -2966,12 +3093,31 @@ postgresExplainDirectModify(ForeignScanState *node, ExplainState *es)
{
List *fdw_private;
char *sql;
+ PgFdwExplainState *pgfdw_explain_state;
+
+ fdw_private = ((ForeignScan *) node->ss.ps.plan)->fdw_private;
+ sql = strVal(list_nth(fdw_private, FdwDirectModifyPrivateUpdateSql));
if (es->verbose)
- {
- fdw_private = ((ForeignScan *) node->ss.ps.plan)->fdw_private;
- sql = strVal(list_nth(fdw_private, FdwDirectModifyPrivateUpdateSql));
ExplainPropertyText("Remote SQL", sql, es);
+
+ pgfdw_explain_state = GetExplainExtensionState(es, GET_EXTENSION_ID());
+
+ if (pgfdw_explain_state && pgfdw_explain_state->remote_plans)
+ {
+ UserMapping *user = NULL;
+ PGconn *conn = NULL;
+ ForeignTable *table;
+
+ table = GetForeignTable(RelationGetRelid(node->ss.ss_currentRelation));
+
+ Assert(table);
+
+ user = GetUserMapping(GetUserId(), table->serverid);
+ conn = GetConnection(user, false, NULL);
+
+ postgresExplainStatement(node->ss.ps.plan->plan_node_id, es, pgfdw_explain_state, conn, sql);
+ ReleaseConnection(conn);
}
}
@@ -7886,3 +8032,95 @@ get_batch_size_option(Relation rel)
return batch_size;
}
+
+void
+postgresExplainPerNode(PlanState *planstate, List *ancestors,
+ const char *relationship, const char *plan_name,
+ ExplainState *es)
+{
+ PgFdwExplainState *pgfdw_explain_state;
+
+ pgfdw_explain_state = GetExplainExtensionState(es, GET_EXTENSION_ID());
+
+ if (pgfdw_explain_state == NULL ||
+ !pgfdw_explain_state->remote_plans)
+ return;
+
+ if (pgfdw_explain_state && pgfdw_explain_state->remote_plans)
+ ExplainPropertyInteger("Plan Node ID", NULL, planstate->plan->plan_node_id, es);
+}
+
+static void
+pgfdwFormatRemotePlan(PgFdwExplainRemotePlans * explain,
+ ExplainState *es,
+ int plan_node_id)
+{
+ char *token;
+ StringInfoData remote_plan_name;
+
+ initStringInfo(&remote_plan_name);
+ appendStringInfo(&remote_plan_name, "Plan Node ID %d", plan_node_id);
+
+ ExplainOpenGroup(remote_plan_name.data, remote_plan_name.data, false, es);
+
+ if (es->format == EXPLAIN_FORMAT_TEXT)
+ {
+ appendStringInfo(es->str, "Plan Node ID %d:", plan_node_id);
+ appendStringInfoString(es->str, "\n");
+ }
+
+ while ((token = strsep(&explain->explain_plan.data, "\n")) != NULL)
+ {
+ if (es->format == EXPLAIN_FORMAT_JSON ||
+ es->format == EXPLAIN_FORMAT_YAML)
+ appendStringInfoString(es->str, "\n");
+
+ appendStringInfoSpaces(es->str, (es->indent == 0) ? 2 : es->indent * 2);
+ appendStringInfoString(es->str, token);
+
+ if (es->format == EXPLAIN_FORMAT_XML ||
+ es->format == EXPLAIN_FORMAT_TEXT)
+ appendStringInfoString(es->str, "\n");
+ }
+
+ ExplainCloseGroup(remote_plan_name.data, remote_plan_name.data, false, es);
+ pfree(remote_plan_name.data);
+}
+
+void
+postgresExplainPerPlan(PlannedStmt *plannedstmt,
+ IntoClause *into,
+ ExplainState *es,
+ const char *queryString,
+ ParamListInfo params,
+ QueryEnvironment *queryEnv)
+{
+ ListCell *lc;
+ PgFdwExplainState *pgfdw_explain_state;
+
+ pgfdw_explain_state = GetExplainExtensionState(es, GET_EXTENSION_ID());
+
+ if (pgfdw_explain_state == NULL ||
+ pgfdw_explain_state->all_remote_plans == NIL ||
+ !pgfdw_explain_state->remote_plans)
+ return;
+
+ ExplainOpenGroup("Remote Plans", "Remote Plans", true, es);
+ if (es->format == EXPLAIN_FORMAT_TEXT)
+ {
+ appendStringInfo(es->str, "Remote Plans:\n");
+ appendStringInfo(es->str, "-------------\n");
+ }
+
+ /* Process every remote plan captured */
+ foreach(lc, pgfdw_explain_state->all_remote_plans)
+ {
+ PgFdwExplainRemotePlans *explain = (PgFdwExplainRemotePlans *) lfirst(lc);
+
+ pgfdwFormatRemotePlan(explain,
+ es,
+ explain->plan_node_id);
+ }
+
+ ExplainCloseGroup("Remote Plans", "Remote Plans", true, es);
+}
diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h
index e69735298d7..b135664b933 100644
--- a/contrib/postgres_fdw/postgres_fdw.h
+++ b/contrib/postgres_fdw/postgres_fdw.h
@@ -13,6 +13,7 @@
#ifndef POSTGRES_FDW_H
#define POSTGRES_FDW_H
+#include "commands/explain_state.h"
#include "foreign/foreign.h"
#include "lib/stringinfo.h"
#include "libpq/libpq-be-fe.h"
@@ -151,6 +152,21 @@ typedef enum PgFdwSamplingMethod
ANALYZE_SAMPLE_BERNOULLI, /* TABLESAMPLE bernoulli */
} PgFdwSamplingMethod;
+typedef struct PgFdwExplainRemotePlans
+{
+ int plan_node_id;
+ StringInfoData explain_plan;
+
+} PgFdwExplainRemotePlans;
+
+typedef struct PgFdwExplainState
+{
+ List *all_remote_plans;
+
+ /* EXPLAIN options */
+ bool remote_plans;
+} PgFdwExplainState;
+
/* in postgres_fdw.c */
extern int set_transmission_modes(void);
extern void reset_transmission_modes(int nestlevel);
@@ -178,6 +194,16 @@ extern int ExtractConnectionOptions(List *defelems,
extern List *ExtractExtensionList(const char *extensionsString,
bool warnOnMissing);
extern char *process_pgfdw_appname(const char *appname);
+extern void postgresExplainPerNode(PlanState *planstate, List *ancestors,
+ const char *relationship,
+ const char *plan_name,
+ ExplainState *es);
+extern void postgresExplainPerPlan(PlannedStmt *plannedstmt,
+ IntoClause *into,
+ ExplainState *es,
+ const char *queryString,
+ ParamListInfo params,
+ QueryEnvironment *queryEnv);
extern char *pgfdw_application_name;
/* in deparse.c */
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 9a8f9e28135..25823a7ebe7 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -281,6 +281,11 @@ SELECT * FROM ft1 t1 WHERE t1.c3 = (SELECT MAX(c3) FROM ft2 t2) ORDER BY c1;
WITH t1 AS (SELECT * FROM ft1 WHERE c1 <= 10) SELECT t2.c1, t2.c2, t2.c3, t2.c4 FROM t1, ft2 t2 WHERE t1.c1 = t2.c1 ORDER BY t1.c1;
-- fixed values
SELECT 'fixed', NULL FROM ft1 t1 WHERE c1 = 1;
+-- with WHERE clause and remote_plans with different formats
+EXPLAIN (REMOTE_PLANS, FORMAT YAML, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+EXPLAIN (REMOTE_PLANS TRUE, FORMAT XML, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+EXPLAIN (REMOTE_PLANS, FORMAT JSON, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+EXPLAIN (REMOTE_PLANS TRUE, FORMAT TEXT, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
-- Test forcing the remote server to produce sorted data for a merge join.
SET enable_hashjoin TO false;
SET enable_nestloop TO false;
@@ -1489,6 +1494,33 @@ SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
ORDER BY ft1.c1 LIMIT 5;
+-- EXPLAIN remote_plans
+EXPLAIN (remote_plans, format text, costs off, analyze)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+EXPLAIN (remote_plans, format text, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+EXPLAIN (remote_plans, format xml, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+EXPLAIN (remote_plans, format json, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+EXPLAIN (remote_plans, format yaml, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+
-- ===================================================================
-- test writable foreign table stuff
-- ===================================================================
@@ -1538,6 +1570,10 @@ EXPLAIN (verbose, costs off)
DELETE FROM ft2 WHERE c1 = 1200 RETURNING tableoid::regclass; -- can be pushed down
DELETE FROM ft2 WHERE c1 = 1200 RETURNING tableoid::regclass;
+-- test write on foreign tables with remote_plans
+EXPLAIN (remote_plans, verbose, costs off)
+UPDATE ft2 SET c2 = c2 + 300 WHERE c1 % 10 = 3;
+
-- Test UPDATE/DELETE with RETURNING on a three-table join
INSERT INTO ft2 (c1,c2,c3)
SELECT id, id - 1200, to_char(id, 'FM00000') FROM generate_series(1201, 1300) id;
@@ -4138,6 +4174,9 @@ INSERT INTO insert_tbl (SELECT * FROM local_tbl UNION ALL SELECT * FROM remote_t
SELECT * FROM insert_tbl ORDER BY a;
+EXPLAIN (REMOTE_PLANS, VERBOSE, COSTS OFF)
+INSERT INTO insert_tbl (SELECT * FROM local_tbl UNION ALL SELECT * FROM remote_tbl);
+
-- Check with direct modify
EXPLAIN (VERBOSE, COSTS OFF)
WITH t AS (UPDATE remote_tbl SET c = c || c RETURNING *)
--
2.43.0
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: explain plans for foreign servers
@ 2025-12-03 22:40 Sami Imseih <[email protected]>
parent: dinesh salve <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Sami Imseih @ 2025-12-03 22:40 UTC (permalink / raw)
To: dinesh salve <[email protected]>; +Cc: Tom Lane <[email protected]>; pgsql-hackers; Jeff Davis <[email protected]>
> I have refactored the commit on the latest version of PG and added a few more tests.
Thanks for the update!
> To simplify the roll out of this feature, I decided to work on analyze=false use case first.
I did not go through the entire patch yet, but a few things stood out
from my first pass.
1/
RegisterExtensionExplainOption is called during _PG_init, which is fine, but I
also wonder if we can call this during postgresExplainForeignScan as well?
The reason being is for _PG_init to be invoked, the user must load postgres_fdw
(LOAD, session_preload_libraries, shared_preload_libraries), which from my
experience is not very common in postgres_fdw. Users ordinarily just
"CREATE EXTENSION..."
So this needs to be documented [0]
2/
Does this behave sanely with multiple fdw connections? Can we add
tests for this?
+
+ /*
+ * add one of the tables to
foreign_scan_table to get the
+ * serverId for remote plans
+ */
+ if (list_length(foreign_scan_table) == 0)
+ foreign_scan_table =
lappend_oid(foreign_scan_table, rte->relid);
+
[0] https://www.postgresql.org/docs/current/postgres-fdw.html
--
Sami Imseih
Amazon Web Services (AWS)
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: explain plans for foreign servers
@ 2025-12-04 00:34 Sami Imseih <[email protected]>
parent: Sami Imseih <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Sami Imseih @ 2025-12-04 00:34 UTC (permalink / raw)
To: dinesh salve <[email protected]>; +Cc: Tom Lane <[email protected]>; pgsql-hackers; Jeff Davis <[email protected]>
I forgot to mention this point earlier.
I noticed GENERIC_PLAN is hard-coded to 1 (true).
Is that an oversight, or is it required?
```
+ GENERIC_PLAN 1, \
```
--
Sami Imseih
Amazon Web Services (AWS)
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: explain plans for foreign servers
@ 2025-12-09 21:08 Sami Imseih <[email protected]>
parent: Sami Imseih <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Sami Imseih @ 2025-12-09 21:08 UTC (permalink / raw)
To: dinesh salve <[email protected]>; +Cc: Tom Lane <[email protected]>; pgsql-hackers; Jeff Davis <[email protected]>
Hi,
I spent more time reviewing this patch. Here are additional comments.
1/ Remove unnecessary includes
#include "commands/explain_state.h" from option.c.
#include "utils/json.h" from postgres_fdw.c.
2/ Add new EXPLAIN options
MEMORY and SUMMARY flags added to the remote EXPLAIN query formatting.
These do not require ANALYZE to be used.
A larger question is how would we want to ensure that new core EXPLAIN
options can be automatically set?
This is not quite common, but perhaps it is a good thing to add a comment
near ExplainState explaining that if a new option is added, make sure
that postgre_fdw remote_plans are updated.
```
typedef struct ExplainState
{
StringInfo str; /* output buffer */
/* options */
bool verbose; /* be verbose */
bool analyze; /* print actual times */
bool costs; /* print estimated co
```
3/ Removed unnecessary pstrdup() when appending remote plan rows to
explain_plan.
Removed unnecessary pstrdup() when appending remote plan rows to explain_plan.
```
+ appendStringInfo(&explain->explain_plan,
"%s\n", pstrdup(PQgetvalue(res, i, 0)));
```
4/ Simplify foreign table OID handling in postgresExplainForeignScan
I am not sure why we need a list that can only hold a single value.
Can we just use an Oid variable to store this?
5/ Encapsulates getting the connection, executing the remote EXPLAIN,
and releasing the connection.
Replaces repeated code in postgresExplainForeignScan,
postgresExplainForeignModify, and postgresExplainDirectModify.
For #4 and #5, attached is my attempt to simplify these routines. What
do you think?
6/ Updated typedefs.list
... to include PgFdwExplainRemotePlans and PgFdwExplainState.
7/ Tests
I quickly skimmed the tests, but I did not see a join push-down test.
We should add
that.
--
Sami Imseih
Amazon Web Services (AWS)
/*
* explain_remote_query
* Helper function to get connection and execute remote EXPLAIN
*/
static void
explain_remote_query(int plan_node_id, ExplainState *es,
PgFdwExplainState *pgfdw_explain_state,
Oid foreign_table_oid, char *sql)
{
UserMapping *user;
PGconn *conn;
ForeignTable *table;
table = GetForeignTable(foreign_table_oid);
user = GetUserMapping(GetUserId(), table->serverid);
conn = GetConnection(user, false, NULL);
postgresExplainStatement(plan_node_id, es, pgfdw_explain_state, conn, sql);
ReleaseConnection(conn);
}
/*
* postgresExplainForeignScan
* Produce extra output for EXPLAIN of a ForeignScan on a foreign table
*/
static void
postgresExplainForeignScan(ForeignScanState *node, ExplainState *es)
{
ForeignScan *plan = castNode(ForeignScan, node->ss.ps.plan);
List *fdw_private = plan->fdw_private;
PgFdwExplainState *pgfdw_explain_state;
char *sql;
Oid foreign_table_oid = InvalidOid;
if (node->ss.ss_currentRelation)
foreign_table_oid = RelationGetRelid(node->ss.ss_currentRelation);
/*
* Identify foreign scans that are really joins or upper relations. The
* input looks something like "(1) LEFT JOIN (2)", and we must replace the
* digit string(s), which are RT indexes, with the correct relation names.
* We do that here, not when the plan is created, because we can't know
* what aliases ruleutils.c will assign at plan creation time.
*/
if (list_length(fdw_private) > FdwScanPrivateRelations)
{
StringInfoData relations;
char *rawrelations;
char *ptr;
int minrti,
rtoffset;
rawrelations = strVal(list_nth(fdw_private, FdwScanPrivateRelations));
/*
* A difficulty with using a string representation of RT indexes is
* that setrefs.c won't update the string when flattening the
* rangetable. To find out what rtoffset was applied, identify the
* minimum RT index appearing in the string and compare it to the
* minimum member of plan->fs_base_relids. (We expect all the relids
* in the join will have been offset by the same amount; the Asserts
* below should catch it if that ever changes.)
*/
minrti = INT_MAX;
ptr = rawrelations;
while (*ptr)
{
if (isdigit((unsigned char) *ptr))
{
int rti = strtol(ptr, &ptr, 10);
if (rti < minrti)
minrti = rti;
}
else
ptr++;
}
rtoffset = bms_next_member(plan->fs_base_relids, -1) - minrti;
/* Now we can translate the string */
initStringInfo(&relations);
ptr = rawrelations;
while (*ptr)
{
if (isdigit((unsigned char) *ptr))
{
int rti = strtol(ptr, &ptr, 10);
RangeTblEntry *rte;
char *relname;
char *refname;
rti += rtoffset;
Assert(bms_is_member(rti, plan->fs_base_relids));
rte = rt_fetch(rti, es->rtable);
Assert(rte->rtekind == RTE_RELATION);
/* This logic should agree with explain.c's ExplainTargetRel */
relname = get_rel_name(rte->relid);
/*
* Save first table OID for getting server connection
*/
if (!OidIsValid(foreign_table_oid))
foreign_table_oid = rte->relid;
if (es->verbose)
{
char *namespace;
namespace = get_namespace_name_or_temp(get_rel_namespace(rte->relid));
appendStringInfo(&relations, "%s.%s",
quote_identifier(namespace),
quote_identifier(relname));
}
else
appendStringInfoString(&relations,
quote_identifier(relname));
refname = (char *) list_nth(es->rtable_names, rti - 1);
if (refname == NULL)
refname = rte->eref->aliasname;
if (strcmp(refname, relname) != 0)
appendStringInfo(&relations, " %s",
quote_identifier(refname));
}
else
appendStringInfoChar(&relations, *ptr++);
}
ExplainPropertyText("Relations", relations.data, es);
}
sql = strVal(list_nth(fdw_private, FdwScanPrivateSelectSql));
/*
* Add remote query, when VERBOSE option is specified.
*/
if (es->verbose)
ExplainPropertyText("Remote SQL", sql, es);
pgfdw_explain_state = GetExplainExtensionState(es, GET_EXTENSION_ID());
/* If we don't have a foreign table oid by now, something went wrong */
Assert(foreign_table_oid);
if (pgfdw_explain_state && pgfdw_explain_state->remote_plans)
explain_remote_query(node->ss.ps.plan->plan_node_id,
es,
pgfdw_explain_state,
foreign_table_oid,
sql);
}
/*
* postgresExplainForeignModify
* Produce extra output for EXPLAIN of a ModifyTable on a foreign table
*/
static void
postgresExplainForeignModify(ModifyTableState *mtstate,
ResultRelInfo *rinfo,
List *fdw_private,
int subplan_index,
ExplainState *es)
{
char *sql = strVal(list_nth(fdw_private,
FdwModifyPrivateUpdateSql));
PgFdwExplainState *pgfdw_explain_state;
if (es->verbose)
{
ExplainPropertyText("Remote SQL", sql, es);
/*
* For INSERT we should always have batch size >= 1, but UPDATE and
* DELETE don't support batching so don't show the property.
*/
if (rinfo->ri_BatchSize > 0)
ExplainPropertyInteger("Batch Size", NULL, rinfo->ri_BatchSize, es);
}
pgfdw_explain_state = GetExplainExtensionState(es, GET_EXTENSION_ID());
if (pgfdw_explain_state && pgfdw_explain_state->remote_plans)
explain_remote_query(mtstate->ps.plan->plan_node_id,
es,
pgfdw_explain_state,
rinfo->ri_RelationDesc->rd_rel->oid,
sql);
}
/*
* postgresExplainDirectModify
* Produce extra output for EXPLAIN of a ForeignScan that modifies a
* foreign table directly
*/
static void
postgresExplainDirectModify(ForeignScanState *node, ExplainState *es)
{
List *fdw_private;
char *sql;
PgFdwExplainState *pgfdw_explain_state;
fdw_private = ((ForeignScan *) node->ss.ps.plan)->fdw_private;
sql = strVal(list_nth(fdw_private, FdwDirectModifyPrivateUpdateSql));
if (es->verbose)
ExplainPropertyText("Remote SQL", sql, es);
pgfdw_explain_state = GetExplainExtensionState(es, GET_EXTENSION_ID());
if (pgfdw_explain_state && pgfdw_explain_state->remote_plans)
explain_remote_query(node->ss.ps.plan->plan_node_id,
es,
pgfdw_explain_state,
RelationGetRelid(node->ss.ss_currentRelation),
sql);
}
Attachments:
[text/plain] simplify_explain_routines.txt (6.1K, ../../CAA5RZ0spw7Fmd5pGvFdTaG2GqkSm6HeXJABbBeqzUDkuN4uevQ@mail.gmail.com/2-simplify_explain_routines.txt)
download | inline:
/*
* explain_remote_query
* Helper function to get connection and execute remote EXPLAIN
*/
static void
explain_remote_query(int plan_node_id, ExplainState *es,
PgFdwExplainState *pgfdw_explain_state,
Oid foreign_table_oid, char *sql)
{
UserMapping *user;
PGconn *conn;
ForeignTable *table;
table = GetForeignTable(foreign_table_oid);
user = GetUserMapping(GetUserId(), table->serverid);
conn = GetConnection(user, false, NULL);
postgresExplainStatement(plan_node_id, es, pgfdw_explain_state, conn, sql);
ReleaseConnection(conn);
}
/*
* postgresExplainForeignScan
* Produce extra output for EXPLAIN of a ForeignScan on a foreign table
*/
static void
postgresExplainForeignScan(ForeignScanState *node, ExplainState *es)
{
ForeignScan *plan = castNode(ForeignScan, node->ss.ps.plan);
List *fdw_private = plan->fdw_private;
PgFdwExplainState *pgfdw_explain_state;
char *sql;
Oid foreign_table_oid = InvalidOid;
if (node->ss.ss_currentRelation)
foreign_table_oid = RelationGetRelid(node->ss.ss_currentRelation);
/*
* Identify foreign scans that are really joins or upper relations. The
* input looks something like "(1) LEFT JOIN (2)", and we must replace the
* digit string(s), which are RT indexes, with the correct relation names.
* We do that here, not when the plan is created, because we can't know
* what aliases ruleutils.c will assign at plan creation time.
*/
if (list_length(fdw_private) > FdwScanPrivateRelations)
{
StringInfoData relations;
char *rawrelations;
char *ptr;
int minrti,
rtoffset;
rawrelations = strVal(list_nth(fdw_private, FdwScanPrivateRelations));
/*
* A difficulty with using a string representation of RT indexes is
* that setrefs.c won't update the string when flattening the
* rangetable. To find out what rtoffset was applied, identify the
* minimum RT index appearing in the string and compare it to the
* minimum member of plan->fs_base_relids. (We expect all the relids
* in the join will have been offset by the same amount; the Asserts
* below should catch it if that ever changes.)
*/
minrti = INT_MAX;
ptr = rawrelations;
while (*ptr)
{
if (isdigit((unsigned char) *ptr))
{
int rti = strtol(ptr, &ptr, 10);
if (rti < minrti)
minrti = rti;
}
else
ptr++;
}
rtoffset = bms_next_member(plan->fs_base_relids, -1) - minrti;
/* Now we can translate the string */
initStringInfo(&relations);
ptr = rawrelations;
while (*ptr)
{
if (isdigit((unsigned char) *ptr))
{
int rti = strtol(ptr, &ptr, 10);
RangeTblEntry *rte;
char *relname;
char *refname;
rti += rtoffset;
Assert(bms_is_member(rti, plan->fs_base_relids));
rte = rt_fetch(rti, es->rtable);
Assert(rte->rtekind == RTE_RELATION);
/* This logic should agree with explain.c's ExplainTargetRel */
relname = get_rel_name(rte->relid);
/*
* Save first table OID for getting server connection
*/
if (!OidIsValid(foreign_table_oid))
foreign_table_oid = rte->relid;
if (es->verbose)
{
char *namespace;
namespace = get_namespace_name_or_temp(get_rel_namespace(rte->relid));
appendStringInfo(&relations, "%s.%s",
quote_identifier(namespace),
quote_identifier(relname));
}
else
appendStringInfoString(&relations,
quote_identifier(relname));
refname = (char *) list_nth(es->rtable_names, rti - 1);
if (refname == NULL)
refname = rte->eref->aliasname;
if (strcmp(refname, relname) != 0)
appendStringInfo(&relations, " %s",
quote_identifier(refname));
}
else
appendStringInfoChar(&relations, *ptr++);
}
ExplainPropertyText("Relations", relations.data, es);
}
sql = strVal(list_nth(fdw_private, FdwScanPrivateSelectSql));
/*
* Add remote query, when VERBOSE option is specified.
*/
if (es->verbose)
ExplainPropertyText("Remote SQL", sql, es);
pgfdw_explain_state = GetExplainExtensionState(es, GET_EXTENSION_ID());
/* If we don't have a foreign table oid by now, something went wrong */
Assert(foreign_table_oid);
if (pgfdw_explain_state && pgfdw_explain_state->remote_plans)
explain_remote_query(node->ss.ps.plan->plan_node_id,
es,
pgfdw_explain_state,
foreign_table_oid,
sql);
}
/*
* postgresExplainForeignModify
* Produce extra output for EXPLAIN of a ModifyTable on a foreign table
*/
static void
postgresExplainForeignModify(ModifyTableState *mtstate,
ResultRelInfo *rinfo,
List *fdw_private,
int subplan_index,
ExplainState *es)
{
char *sql = strVal(list_nth(fdw_private,
FdwModifyPrivateUpdateSql));
PgFdwExplainState *pgfdw_explain_state;
if (es->verbose)
{
ExplainPropertyText("Remote SQL", sql, es);
/*
* For INSERT we should always have batch size >= 1, but UPDATE and
* DELETE don't support batching so don't show the property.
*/
if (rinfo->ri_BatchSize > 0)
ExplainPropertyInteger("Batch Size", NULL, rinfo->ri_BatchSize, es);
}
pgfdw_explain_state = GetExplainExtensionState(es, GET_EXTENSION_ID());
if (pgfdw_explain_state && pgfdw_explain_state->remote_plans)
explain_remote_query(mtstate->ps.plan->plan_node_id,
es,
pgfdw_explain_state,
rinfo->ri_RelationDesc->rd_rel->oid,
sql);
}
/*
* postgresExplainDirectModify
* Produce extra output for EXPLAIN of a ForeignScan that modifies a
* foreign table directly
*/
static void
postgresExplainDirectModify(ForeignScanState *node, ExplainState *es)
{
List *fdw_private;
char *sql;
PgFdwExplainState *pgfdw_explain_state;
fdw_private = ((ForeignScan *) node->ss.ps.plan)->fdw_private;
sql = strVal(list_nth(fdw_private, FdwDirectModifyPrivateUpdateSql));
if (es->verbose)
ExplainPropertyText("Remote SQL", sql, es);
pgfdw_explain_state = GetExplainExtensionState(es, GET_EXTENSION_ID());
if (pgfdw_explain_state && pgfdw_explain_state->remote_plans)
explain_remote_query(node->ss.ps.plan->plan_node_id,
es,
pgfdw_explain_state,
RelationGetRelid(node->ss.ss_currentRelation),
sql);
}
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: explain plans for foreign servers
@ 2026-01-07 10:00 dinesh salve <[email protected]>
parent: Sami Imseih <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: dinesh salve @ 2026-01-07 10:00 UTC (permalink / raw)
To: Sami Imseih <[email protected]>; +Cc: Tom Lane <[email protected]>; pgsql-hackers; Jeff Davis <[email protected]>
Thanks Sami for feedback. Few points I wanted to call out and need your
inputs on -
Supporting remote_plans options for inserts:
Only "explain insert" are executed with bind variables (verified by logging
all sqls while running make check) and while executing that on remote is
erroring out with error "there is no parameter $1". We can either NOT
support remote plans for insert statements or always use generic_plan
option on remote sql. Using "generic_plan" on remote comes with an
additional check if remote supports this option or not in case remote shard
is older postgres.
I prefer not supporting remote_plans for inserts as there is nothing much
that goes in insert statement plans unless its "insert into..select". User
can always run explain on that select separately. Appreciate your inputs on
this.
About decision which explain options we should forward to remote shard:
This is because local and remote postgres could be different and we still
need to address what all options we send in remote sql as remote shard
might not even support them. We can forward only limited options to remote
which are widely supported (pg >= 9) i.e. verbose, costs, buffers, format
only.
If we need to support all possible options, we need to query the version of
remote postgres and then prepare remote sql. Thoughts?
Regard,
Dinesh (AWS)
On Wed, Dec 10, 2025 at 2:38 AM Sami Imseih <[email protected]> wrote:
> Hi,
>
> I spent more time reviewing this patch. Here are additional comments.
>
> 1/ Remove unnecessary includes
>
> #include "commands/explain_state.h" from option.c.
> #include "utils/json.h" from postgres_fdw.c.
>
> 2/ Add new EXPLAIN options
>
> MEMORY and SUMMARY flags added to the remote EXPLAIN query formatting.
> These do not require ANALYZE to be used.
>
> A larger question is how would we want to ensure that new core EXPLAIN
> options can be automatically set?
>
> This is not quite common, but perhaps it is a good thing to add a comment
> near ExplainState explaining that if a new option is added, make sure
> that postgre_fdw remote_plans are updated.
>
> ```
> typedef struct ExplainState
> {
> StringInfo str; /* output buffer */
> /* options */
> bool verbose; /* be verbose */
> bool analyze; /* print actual times */
> bool costs; /* print estimated co
>
> ```
>
> 3/ Removed unnecessary pstrdup() when appending remote plan rows to
> explain_plan.
>
> Removed unnecessary pstrdup() when appending remote plan rows to
> explain_plan.
>
> ```
> + appendStringInfo(&explain->explain_plan,
> "%s\n", pstrdup(PQgetvalue(res, i, 0)));
> ```
>
>
> 4/ Simplify foreign table OID handling in postgresExplainForeignScan
>
> I am not sure why we need a list that can only hold a single value.
> Can we just use an Oid variable to store this?
>
>
> 5/ Encapsulates getting the connection, executing the remote EXPLAIN,
> and releasing the connection.
>
> Replaces repeated code in postgresExplainForeignScan,
> postgresExplainForeignModify, and postgresExplainDirectModify.
>
> For #4 and #5, attached is my attempt to simplify these routines. What
> do you think?
>
> 6/ Updated typedefs.list
>
> ... to include PgFdwExplainRemotePlans and PgFdwExplainState.
>
>
> 7/ Tests
>
> I quickly skimmed the tests, but I did not see a join push-down test.
> We should add
> that.
>
>
> --
> Sami Imseih
> Amazon Web Services (AWS)
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: explain plans for foreign servers
@ 2026-01-10 00:16 Sami Imseih <[email protected]>
parent: dinesh salve <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Sami Imseih @ 2026-01-10 00:16 UTC (permalink / raw)
To: dinesh salve <[email protected]>; +Cc: Tom Lane <[email protected]>; pgsql-hackers; Jeff Davis <[email protected]>
> Supporting remote_plans options for inserts:
> Only "explain insert" are executed with bind variables
> (verified by logging all sqls while running make check) and while executing
> that on remote is erroring out with error "there is no parameter $1". We can
> either NOT support remote plans for insert statements
> or always use generic_plan option on remote sql. Using "generic_plan" on
> remote comes with an additional check if remote supports
> this option or not in case remote shard is older postgres.
> I prefer not supporting remote_plans for inserts as there is nothing much that
> goes in insert statement plans unless its "insert into..select".
> User can always run explain on that select separately. Appreciate your
> inputs on this.
After looking at this a bit more, I don't think the INSERT case is the only one.
Here is an example:
```
-- Setup foreign server and table
CREATE EXTENSION postgres_fdw;
CREATE SERVER remote_server FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'localhost', port '5432', dbname 'postgres');
CREATE USER MAPPING FOR CURRENT_USER SERVER remote_server
OPTIONS (user 'postgres', password 'password');
CREATE TABLE local_table (id int, name text);
CREATE FOREIGN TABLE remote_table (
id int,
name text
) SERVER remote_server OPTIONS (table_name 'local_table');
postgres=# load 'postgres_fdw';
LOAD
postgres=# explain (remote_plans, verbose) select * from remote_table
where id = (select 1);
ERROR: there is no parameter $1
CONTEXT: remote SQL command: EXPLAIN (
FORMAT TEXT, VERBOSE
1, COSTS 1, SETTINGS
0) SELECT id, name FROM public.local_table WHERE ((id =
$1::integer))
postgres=#
postgres=# explain (verbose) select * from remote_table where id = (select 1);
QUERY PLAN
----------------------------------------------------------------------------------
Foreign Scan on public.remote_table (cost=100.01..128.54 rows=7 width=36)
Output: remote_table.id, remote_table.name
Remote SQL: SELECT id, name FROM public.local_table WHERE ((id =
$1::integer))
InitPlan expr_1
-> Result (cost=0.00..0.01 rows=1 width=4)
Output: 1
(6 rows)
````
The above is due to the value of the subquery is sent as a parameter; see
`printRemoteParam`in deparse.c.
Also see this comment in deparse.c:
```
* This is used when we're just trying to EXPLAIN the remote query.
* We don't have the actual value of the runtime parameter yet, and we don't
* want the remote planner to generate a plan that depends on such a value
* anyway. Thus, we can't do something simple like "$1::paramtype".
* Instead, we emit "((SELECT null::paramtype)::paramtype)".
```
The above comment is related to the EXPLAIN being sent remotely when
use_remote_estimate is enabled. But the point is, it will not be possible to
send the runtime parameters to the remote EXPLAIN.
So "generic_plan" as a mandatory option may be the best way to proceed,
and only make the remote_plans option available to remote versions that
support this option.
Maybe others have a better way?
> About decision which explain options we should forward to remote shard:
> This is because local and remote postgres could be different and we still
> need to address what all options we send in remote sql as remote shard
> might not even support them. We can forward only limited options to
> remote which are widely supported (pg >= 9) i.e. verbose, costs, buffers,
> format only. If we need to support all possible options, we need to query
> the version of remote postgres and then prepare remote sql. Thoughts?
I think if we try to forward an option that is on the source side but not on
the remote side, it's fair to just error out with "ERROR:
unrecognized EXPLAIN option..."
That should be acceptable, because the user will know better not to use that
option. right?
--
Sami Imseih
Amazon Web Services (AWS)
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: explain plans for foreign servers
@ 2026-06-19 19:04 dinesh salve <[email protected]>
parent: Sami Imseih <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: dinesh salve @ 2026-06-19 19:04 UTC (permalink / raw)
To: Sami Imseih <[email protected]>; +Cc: Tom Lane <[email protected]>; pgsql-hackers; Jeff Davis <[email protected]>
> So "generic_plan" as a mandatory option may be the best way to proceed,
> and only make the remote_plans option available to remote versions that
> support this option.
> Maybe others have a better way?
I agree, I found use of generic_plan convenient here. Attached patch 2
considers this. I have added an error message if the remote postgres server
is not compatible with options being sent. Also updated documentation with
limitations.
Thanks a lot for feedback, Sami. Please review the attached patch and share
feedback.
On Sat, Jan 10, 2026 at 5:46 AM Sami Imseih <[email protected]> wrote:
> > Supporting remote_plans options for inserts:
> > Only "explain insert" are executed with bind variables
> > (verified by logging all sqls while running make check) and while
> executing
> > that on remote is erroring out with error "there is no parameter $1". We
> can
> > either NOT support remote plans for insert statements
> > or always use generic_plan option on remote sql. Using "generic_plan" on
> > remote comes with an additional check if remote supports
> > this option or not in case remote shard is older postgres.
> > I prefer not supporting remote_plans for inserts as there is nothing
> much that
> > goes in insert statement plans unless its "insert into..select".
> > User can always run explain on that select separately. Appreciate your
> > inputs on this.
>
> After looking at this a bit more, I don't think the INSERT case is the
> only one.
>
> Here is an example:
> ```
> -- Setup foreign server and table
> CREATE EXTENSION postgres_fdw;
> CREATE SERVER remote_server FOREIGN DATA WRAPPER postgres_fdw
> OPTIONS (host 'localhost', port '5432', dbname 'postgres');
> CREATE USER MAPPING FOR CURRENT_USER SERVER remote_server
> OPTIONS (user 'postgres', password 'password');
>
> CREATE TABLE local_table (id int, name text);
> CREATE FOREIGN TABLE remote_table (
> id int,
> name text
> ) SERVER remote_server OPTIONS (table_name 'local_table');
>
> postgres=# load 'postgres_fdw';
> LOAD
> postgres=# explain (remote_plans, verbose) select * from remote_table
> where id = (select 1);
> ERROR: there is no parameter $1
> CONTEXT: remote SQL command: EXPLAIN (
> FORMAT TEXT, VERBOSE
> 1, COSTS 1, SETTINGS
> 0) SELECT id, name FROM public.local_table WHERE ((id =
> $1::integer))
> postgres=#
>
> postgres=# explain (verbose) select * from remote_table where id = (select
> 1);
> QUERY PLAN
>
> ----------------------------------------------------------------------------------
> Foreign Scan on public.remote_table (cost=100.01..128.54 rows=7 width=36)
> Output: remote_table.id, remote_table.name
> Remote SQL: SELECT id, name FROM public.local_table WHERE ((id =
> $1::integer))
> InitPlan expr_1
> -> Result (cost=0.00..0.01 rows=1 width=4)
> Output: 1
> (6 rows)
>
> ````
>
> The above is due to the value of the subquery is sent as a parameter; see
> `printRemoteParam`in deparse.c.
>
> Also see this comment in deparse.c:
>
> ```
> * This is used when we're just trying to EXPLAIN the remote query.
> * We don't have the actual value of the runtime parameter yet, and we don't
> * want the remote planner to generate a plan that depends on such a value
> * anyway. Thus, we can't do something simple like "$1::paramtype".
> * Instead, we emit "((SELECT null::paramtype)::paramtype)".
> ```
>
> The above comment is related to the EXPLAIN being sent remotely when
> use_remote_estimate is enabled. But the point is, it will not be possible
> to
> send the runtime parameters to the remote EXPLAIN.
>
> So "generic_plan" as a mandatory option may be the best way to proceed,
> and only make the remote_plans option available to remote versions that
> support this option.
>
> Maybe others have a better way?
>
>
> > About decision which explain options we should forward to remote shard:
> > This is because local and remote postgres could be different and we still
> > need to address what all options we send in remote sql as remote shard
> > might not even support them. We can forward only limited options to
> > remote which are widely supported (pg >= 9) i.e. verbose, costs, buffers,
> > format only. If we need to support all possible options, we need to query
> > the version of remote postgres and then prepare remote sql. Thoughts?
>
> I think if we try to forward an option that is on the source side but not
> on
> the remote side, it's fair to just error out with "ERROR:
> unrecognized EXPLAIN option..."
>
> That should be acceptable, because the user will know better not to use
> that
> option. right?
>
> --
> Sami Imseih
> Amazon Web Services (AWS)
>
Attachments:
[application/octet-stream] 0002-postgres_fdw-show-remote-EXPLAIN-plans-via-REMOTE_PL.patch (68.8K, ../../CAP+B4TDngSD2SftX3-O_scuPPBcYQOUaRhraH-XhX4WQ1KeDsA@mail.gmail.com/3-0002-postgres_fdw-show-remote-EXPLAIN-plans-via-REMOTE_PL.patch)
download | inline diff:
From f46403f33189862933b545d4c4b1f9b0c68f2f78 Mon Sep 17 00:00:00 2001
From: Dinesh Salve <[email protected]>
Date: Sat, 20 Jun 2026 00:26:00 +0530
Subject: [PATCH] postgres_fdw: show remote EXPLAIN plans via REMOTE_PLANS
option
Add a REMOTE_PLANS option to EXPLAIN that, for every foreign scan or
foreign modification, runs EXPLAIN on the remote server for the
corresponding remote query and prints the result in a "Remote Plans"
section keyed by the local plan node id.
The remote query that postgres_fdw sends often contains $n parameter
placeholders (parameterized foreign scans and joins, subquery outputs,
and INSERT/UPDATE/DELETE), which a plain remote EXPLAIN cannot plan and
fails with "there is no parameter $1". REMOTE_PLANS therefore forces
GENERIC_PLAN on the remote EXPLAIN, which plans such statements without
bound values. GENERIC_PLAN is only available from PostgreSQL 16, so
REMOTE_PLANS errors out on older remote servers; the plans shown are
consequently generic.
REMOTE_PLANS cannot be combined with ANALYZE, so the ANALYZE-only EXPLAIN
options are not forwarded to the remote. The remaining plan-shaping
options the user requested are forwarded as-is; if the remote server does
not recognize one of them it errors out with "unrecognized EXPLAIN
option".
The option is registered when postgres_fdw is loaded.
---
.../postgres_fdw/expected/postgres_fdw.out | 626 ++++++++++++++++++
contrib/postgres_fdw/option.c | 71 ++
contrib/postgres_fdw/postgres_fdw.c | 279 +++++++-
contrib/postgres_fdw/postgres_fdw.h | 26 +
contrib/postgres_fdw/sql/postgres_fdw.sql | 50 ++
doc/src/sgml/postgres-fdw.sgml | 41 ++
src/include/commands/explain_state.h | 2 +-
src/tools/pgindent/typedefs.list | 2 +
8 files changed, 1084 insertions(+), 13 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index e90289e..803ac6c 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -471,6 +471,173 @@ SELECT 'fixed', NULL FROM ft1 t1 WHERE c1 = 1;
fixed |
(1 row)
+-- with WHERE clause and remote_plans with different formats
+EXPLAIN (REMOTE_PLANS, FORMAT YAML, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------
+ - Plan: +
+ Node Type: "Foreign Scan" +
+ Operation: "Select" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Relation Name: "ft1" +
+ Schema: "public" +
+ Alias: "t1" +
+ Disabled: false +
+ Output: +
+ - "c1" +
+ - "c2" +
+ - "c3" +
+ - "c4" +
+ - "c5" +
+ - "c6" +
+ - "c7" +
+ - "c8" +
+ Remote SQL: "SELECT \"C 1\", c2, c3, c4, c5, c6, c7, c8 FROM \"S 1\".\"T 1\" WHERE ((\"C 1\" = 101))"+
+ Plan Node ID: 0 +
+ Remote Plans: +
+ Plan Node ID 0: +
+ - Plan: +
+ Node Type: "Index Scan" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Scan Direction: "Forward" +
+ Index Name: "t1_pkey" +
+ Relation Name: "T 1" +
+ Schema: "S 1" +
+ Alias: "T 1" +
+ Disabled: false +
+ Output: +
+ - "\"C 1\"" +
+ - "c2" +
+ - "c3" +
+ - "c4" +
+ - "c5" +
+ - "c6" +
+ - "c7" +
+ - "c8" +
+ Index Cond: "(\"T 1\".\"C 1\" = 101)"
+(1 row)
+
+EXPLAIN (REMOTE_PLANS TRUE, FORMAT XML, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------
+ <explain xmlns="http://www.postgresql.org/2009/explain"> +
+ <Query> +
+ <Plan> +
+ <Node-Type>Foreign Scan</Node-Type> +
+ <Operation>Select</Operation> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Relation-Name>ft1</Relation-Name> +
+ <Schema>public</Schema> +
+ <Alias>t1</Alias> +
+ <Disabled>false</Disabled> +
+ <Output> +
+ <Item>c1</Item> +
+ <Item>c2</Item> +
+ <Item>c3</Item> +
+ <Item>c4</Item> +
+ <Item>c5</Item> +
+ <Item>c6</Item> +
+ <Item>c7</Item> +
+ <Item>c8</Item> +
+ </Output> +
+ <Remote-SQL>SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 101))</Remote-SQL>+
+ <Plan-Node-ID>0</Plan-Node-ID> +
+ </Plan> +
+ <Remote-Plans> +
+ <Plan-Node-ID-0> +
+ <explain xmlns="http://www.postgresql.org/2009/explain"> +
+ <Query> +
+ <Plan> +
+ <Node-Type>Index Scan</Node-Type> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Scan-Direction>Forward</Scan-Direction> +
+ <Index-Name>t1_pkey</Index-Name> +
+ <Relation-Name>T 1</Relation-Name> +
+ <Schema>S 1</Schema> +
+ <Alias>T 1</Alias> +
+ <Disabled>false</Disabled> +
+ <Output> +
+ <Item>"C 1"</Item> +
+ <Item>c2</Item> +
+ <Item>c3</Item> +
+ <Item>c4</Item> +
+ <Item>c5</Item> +
+ <Item>c6</Item> +
+ <Item>c7</Item> +
+ <Item>c8</Item> +
+ </Output> +
+ <Index-Cond>("T 1"."C 1" = 101)</Index-Cond> +
+ </Plan> +
+ </Query> +
+ </explain> +
+ </Plan-Node-ID-0> +
+ </Remote-Plans> +
+ </Query> +
+ </explain>
+(1 row)
+
+EXPLAIN (REMOTE_PLANS, FORMAT JSON, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Foreign Scan", +
+ "Operation": "Select", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Relation Name": "ft1", +
+ "Schema": "public", +
+ "Alias": "t1", +
+ "Disabled": false, +
+ "Output": ["c1", "c2", "c3", "c4", "c5", "c6", "c7", "c8"], +
+ "Remote SQL": "SELECT \"C 1\", c2, c3, c4, c5, c6, c7, c8 FROM \"S 1\".\"T 1\" WHERE ((\"C 1\" = 101))",+
+ "Plan Node ID": 0 +
+ }, +
+ "Remote Plans": { +
+ "Plan Node ID 0": [ +
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Index Scan", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Scan Direction": "Forward", +
+ "Index Name": "t1_pkey", +
+ "Relation Name": "T 1", +
+ "Schema": "S 1", +
+ "Alias": "T 1", +
+ "Disabled": false, +
+ "Output": ["\"C 1\"", "c2", "c3", "c4", "c5", "c6", "c7", "c8"], +
+ "Index Cond": "(\"T 1\".\"C 1\" = 101)" +
+ } +
+ } +
+ ] +
+ ] +
+ } +
+ } +
+ ]
+(1 row)
+
+EXPLAIN (REMOTE_PLANS TRUE, FORMAT TEXT, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------
+ Foreign Scan on public.ft1 t1
+ Output: c1, c2, c3, c4, c5, c6, c7, c8
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 101))
+ Plan Node ID: 0
+ Remote Plans:
+ -------------
+ Plan Node ID 0:
+ Index Scan using t1_pkey on "S 1"."T 1"
+ Output: "C 1", c2, c3, c4, c5, c6, c7, c8
+ Index Cond: ("T 1"."C 1" = 101)
+(10 rows)
+
-- Test forcing the remote server to produce sorted data for a merge join.
SET enable_hashjoin TO false;
SET enable_nestloop TO false;
@@ -646,6 +813,25 @@ SELECT t1."C 1", t2.c1, t3.c1 FROM "S 1"."T 1" t1 full join ft1 t2 full join ft2
110 | 110 | 110
(10 rows)
+-- Join push-down test
+-- Ensure join conditions are pushed down to the foreign server
+EXPLAIN (REMOTE_PLANS, VERBOSE, COSTS OFF)
+ SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) WHERE t1.c2 = 10;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+ Output: t1.c1, t2.c1
+ Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
+ Remote SQL: SELECT r1."C 1", r2."C 1" FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r2."C 1" = r1."C 1")) AND ((r1.c2 = 10))))
+ Plan Node ID: 0
+ Remote Plans:
+ -------------
+ Plan Node ID 0:
+ Seq Scan on "S 1"."T 1" r2
+ Output: r2."C 1", r2."C 1"
+ Filter: (r2.c2 = 10)
+(11 rows)
+
RESET enable_hashjoin;
RESET enable_nestloop;
-- Test executing assertion in estimate_path_cost_size() that makes sure that
@@ -790,6 +976,32 @@ SELECT * FROM "S 1"."T 1" a, ft2 b WHERE a."C 1" = 47 AND b.c1 = a.c2;
47 | 7 | 00047 | Tue Feb 17 00:00:00 1970 PST | Tue Feb 17 00:00:00 1970 | 7 | 7 | foo | 7 | 7 | 00007 | Thu Jan 08 00:00:00 1970 PST | Thu Jan 08 00:00:00 1970 | 7 | 7 | foo
(1 row)
+-- REMOTE_PLANS over a parameterized foreign scan: the deparsed remote SQL
+-- carries a $1 placeholder, which only plans on the remote side because
+-- REMOTE_PLANS forces GENERIC_PLAN.
+EXPLAIN (REMOTE_PLANS, VERBOSE, COSTS OFF)
+ SELECT * FROM "S 1"."T 1" a, ft2 b WHERE a."C 1" = 47 AND b.c1 = a.c2;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------
+ Nested Loop
+ Output: a."C 1", a.c2, a.c3, a.c4, a.c5, a.c6, a.c7, a.c8, b.c1, b.c2, b.c3, b.c4, b.c5, b.c6, b.c7, b.c8
+ Plan Node ID: 0
+ -> Index Scan using t1_pkey on "S 1"."T 1" a
+ Output: a."C 1", a.c2, a.c3, a.c4, a.c5, a.c6, a.c7, a.c8
+ Index Cond: (a."C 1" = 47)
+ Plan Node ID: 1
+ -> Foreign Scan on public.ft2 b
+ Output: b.c1, b.c2, b.c3, b.c4, b.c5, b.c6, b.c7, b.c8
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = $1::integer))
+ Plan Node ID: 2
+ Remote Plans:
+ -------------
+ Plan Node ID 2:
+ Index Scan using t1_pkey on "S 1"."T 1"
+ Output: "C 1", c2, c3, c4, c5, c6, c7, c8
+ Index Cond: ("T 1"."C 1" = $1)
+(17 rows)
+
-- check both safe and unsafe join conditions
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM ft2 a, ft2 b
@@ -5116,6 +5328,373 @@ SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
Remote SQL: SELECT r5."C 1", r6.c1 FROM ("S 1"."T 1" r5 INNER JOIN "S 1"."T 3" r6 ON (((r5."C 1" = r6.c1)))) ORDER BY r5."C 1" ASC NULLS LAST
(13 rows)
+-- EXPLAIN remote_plans
+EXPLAIN (remote_plans, format text, costs off, analyze)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+ERROR: EXPLAIN options REMOTE_PLANS and ANALYZE cannot be used together
+EXPLAIN (remote_plans, format text, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+ QUERY PLAN
+-------------------------------------------------------
+ Limit
+ Plan Node ID: 0
+ -> Merge Semi Join
+ Merge Cond: (ft1.c1 = ft2_1.c1)
+ Plan Node ID: 1
+ -> Foreign Scan
+ Relations: (ft1) INNER JOIN (ft2)
+ Plan Node ID: 2
+ -> Foreign Scan
+ Relations: (ft2 ft2_1) INNER JOIN (ft4)
+ Plan Node ID: 3
+ Remote Plans:
+ -------------
+ Plan Node ID 2:
+ Index Only Scan using t1_pkey on "T 1" r2
+ Plan Node ID 3:
+ Merge Join
+ Merge Cond: (r5."C 1" = r6.c1)
+ -> Index Only Scan using t1_pkey on "T 1" r5
+ -> Sort
+ Sort Key: r6.c1
+ -> Seq Scan on "T 3" r6
+(22 rows)
+
+EXPLAIN (remote_plans, format xml, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+ QUERY PLAN
+------------------------------------------------------------------------
+ <explain xmlns="http://www.postgresql.org/2009/explain"> +
+ <Query> +
+ <Plan> +
+ <Node-Type>Limit</Node-Type> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Disabled>false</Disabled> +
+ <Plan-Node-ID>0</Plan-Node-ID> +
+ <Plans> +
+ <Plan> +
+ <Node-Type>Merge Join</Node-Type> +
+ <Parent-Relationship>Outer</Parent-Relationship> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Join-Type>Semi</Join-Type> +
+ <Disabled>false</Disabled> +
+ <Inner-Unique>false</Inner-Unique> +
+ <Merge-Cond>(ft1.c1 = ft2_1.c1)</Merge-Cond> +
+ <Plan-Node-ID>1</Plan-Node-ID> +
+ <Plans> +
+ <Plan> +
+ <Node-Type>Foreign Scan</Node-Type> +
+ <Operation>Select</Operation> +
+ <Parent-Relationship>Outer</Parent-Relationship> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Disabled>false</Disabled> +
+ <Relations>(ft1) INNER JOIN (ft2)</Relations> +
+ <Plan-Node-ID>2</Plan-Node-ID> +
+ </Plan> +
+ <Plan> +
+ <Node-Type>Foreign Scan</Node-Type> +
+ <Operation>Select</Operation> +
+ <Parent-Relationship>Inner</Parent-Relationship> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Disabled>false</Disabled> +
+ <Relations>(ft2 ft2_1) INNER JOIN (ft4)</Relations> +
+ <Plan-Node-ID>3</Plan-Node-ID> +
+ </Plan> +
+ </Plans> +
+ </Plan> +
+ </Plans> +
+ </Plan> +
+ <Remote-Plans> +
+ <Plan-Node-ID-2> +
+ <explain xmlns="http://www.postgresql.org/2009/explain"> +
+ <Query> +
+ <Plan> +
+ <Node-Type>Index Only Scan</Node-Type> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Scan-Direction>Forward</Scan-Direction> +
+ <Index-Name>t1_pkey</Index-Name> +
+ <Relation-Name>T 1</Relation-Name> +
+ <Alias>r2</Alias> +
+ <Disabled>false</Disabled> +
+ </Plan> +
+ </Query> +
+ </explain> +
+ </Plan-Node-ID-2> +
+ <Plan-Node-ID-3> +
+ <explain xmlns="http://www.postgresql.org/2009/explain"> +
+ <Query> +
+ <Plan> +
+ <Node-Type>Merge Join</Node-Type> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Join-Type>Inner</Join-Type> +
+ <Disabled>false</Disabled> +
+ <Inner-Unique>true</Inner-Unique> +
+ <Merge-Cond>(r5."C 1" = r6.c1)</Merge-Cond> +
+ <Plans> +
+ <Plan> +
+ <Node-Type>Index Only Scan</Node-Type> +
+ <Parent-Relationship>Outer</Parent-Relationship> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Scan-Direction>Forward</Scan-Direction> +
+ <Index-Name>t1_pkey</Index-Name> +
+ <Relation-Name>T 1</Relation-Name> +
+ <Alias>r5</Alias> +
+ <Disabled>false</Disabled> +
+ </Plan> +
+ <Plan> +
+ <Node-Type>Sort</Node-Type> +
+ <Parent-Relationship>Inner</Parent-Relationship> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Disabled>false</Disabled> +
+ <Sort-Key> +
+ <Item>r6.c1</Item> +
+ </Sort-Key> +
+ <Plans> +
+ <Plan> +
+ <Node-Type>Seq Scan</Node-Type> +
+ <Parent-Relationship>Outer</Parent-Relationship>+
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Relation-Name>T 3</Relation-Name> +
+ <Alias>r6</Alias> +
+ <Disabled>false</Disabled> +
+ </Plan> +
+ </Plans> +
+ </Plan> +
+ </Plans> +
+ </Plan> +
+ </Query> +
+ </explain> +
+ </Plan-Node-ID-3> +
+ </Remote-Plans> +
+ </Query> +
+ </explain>
+(1 row)
+
+EXPLAIN (remote_plans, format json, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+ QUERY PLAN
+------------------------------------------------------------
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Limit", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Disabled": false, +
+ "Plan Node ID": 0, +
+ "Plans": [ +
+ { +
+ "Node Type": "Merge Join", +
+ "Parent Relationship": "Outer", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Join Type": "Semi", +
+ "Disabled": false, +
+ "Inner Unique": false, +
+ "Merge Cond": "(ft1.c1 = ft2_1.c1)", +
+ "Plan Node ID": 1, +
+ "Plans": [ +
+ { +
+ "Node Type": "Foreign Scan", +
+ "Operation": "Select", +
+ "Parent Relationship": "Outer", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Disabled": false, +
+ "Relations": "(ft1) INNER JOIN (ft2)", +
+ "Plan Node ID": 2 +
+ }, +
+ { +
+ "Node Type": "Foreign Scan", +
+ "Operation": "Select", +
+ "Parent Relationship": "Inner", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Disabled": false, +
+ "Relations": "(ft2 ft2_1) INNER JOIN (ft4)",+
+ "Plan Node ID": 3 +
+ } +
+ ] +
+ } +
+ ] +
+ }, +
+ "Remote Plans": { +
+ "Plan Node ID 2": [ +
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Index Only Scan", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Scan Direction": "Forward", +
+ "Index Name": "t1_pkey", +
+ "Relation Name": "T 1", +
+ "Alias": "r2", +
+ "Disabled": false +
+ } +
+ } +
+ ] +
+ ], +
+ "Plan Node ID 3": [ +
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Merge Join", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Join Type": "Inner", +
+ "Disabled": false, +
+ "Inner Unique": true, +
+ "Merge Cond": "(r5.\"C 1\" = r6.c1)", +
+ "Plans": [ +
+ { +
+ "Node Type": "Index Only Scan", +
+ "Parent Relationship": "Outer", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Scan Direction": "Forward", +
+ "Index Name": "t1_pkey", +
+ "Relation Name": "T 1", +
+ "Alias": "r5", +
+ "Disabled": false +
+ }, +
+ { +
+ "Node Type": "Sort", +
+ "Parent Relationship": "Inner", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Disabled": false, +
+ "Sort Key": ["r6.c1"], +
+ "Plans": [ +
+ { +
+ "Node Type": "Seq Scan", +
+ "Parent Relationship": "Outer", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Relation Name": "T 3", +
+ "Alias": "r6", +
+ "Disabled": false +
+ } +
+ ] +
+ } +
+ ] +
+ } +
+ } +
+ ] +
+ ] +
+ } +
+ } +
+ ]
+(1 row)
+
+EXPLAIN (remote_plans, format yaml, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+ QUERY PLAN
+-------------------------------------------------------
+ - Plan: +
+ Node Type: "Limit" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Disabled: false +
+ Plan Node ID: 0 +
+ Plans: +
+ - Node Type: "Merge Join" +
+ Parent Relationship: "Outer" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Join Type: "Semi" +
+ Disabled: false +
+ Inner Unique: false +
+ Merge Cond: "(ft1.c1 = ft2_1.c1)" +
+ Plan Node ID: 1 +
+ Plans: +
+ - Node Type: "Foreign Scan" +
+ Operation: "Select" +
+ Parent Relationship: "Outer" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Disabled: false +
+ Relations: "(ft1) INNER JOIN (ft2)" +
+ Plan Node ID: 2 +
+ - Node Type: "Foreign Scan" +
+ Operation: "Select" +
+ Parent Relationship: "Inner" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Disabled: false +
+ Relations: "(ft2 ft2_1) INNER JOIN (ft4)"+
+ Plan Node ID: 3 +
+ Remote Plans: +
+ Plan Node ID 2: +
+ - Plan: +
+ Node Type: "Index Only Scan" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Scan Direction: "Forward" +
+ Index Name: "t1_pkey" +
+ Relation Name: "T 1" +
+ Alias: "r2" +
+ Disabled: false +
+ Plan Node ID 3: +
+ - Plan: +
+ Node Type: "Merge Join" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Join Type: "Inner" +
+ Disabled: false +
+ Inner Unique: true +
+ Merge Cond: "(r5.\"C 1\" = r6.c1)" +
+ Plans: +
+ - Node Type: "Index Only Scan" +
+ Parent Relationship: "Outer" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Scan Direction: "Forward" +
+ Index Name: "t1_pkey" +
+ Relation Name: "T 1" +
+ Alias: "r5" +
+ Disabled: false +
+ - Node Type: "Sort" +
+ Parent Relationship: "Inner" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Disabled: false +
+ Sort Key: +
+ - "r6.c1" +
+ Plans: +
+ - Node Type: "Seq Scan" +
+ Parent Relationship: "Outer" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Relation Name: "T 3" +
+ Alias: "r6" +
+ Disabled: false
+(1 row)
+
-- ===================================================================
-- test writable foreign table stuff
-- ===================================================================
@@ -6354,6 +6933,25 @@ SELECT * FROM ft8 WHERE c1 = '[2,3)' ORDER BY c1, c4;
[2,3) | 3 | AAA002 | [01-01-2000,01-01-2020)
(1 row)
+-- test write on foreign tables with remote_plans
+EXPLAIN (remote_plans, verbose, costs off)
+UPDATE ft2 SET c2 = c2 + 300 WHERE c1 % 10 = 3;
+ QUERY PLAN
+---------------------------------------------------------------------------------------
+ Update on public.ft2
+ Plan Node ID: 0
+ -> Foreign Update on public.ft2
+ Remote SQL: UPDATE "S 1"."T 1" SET c2 = (c2 + 300) WHERE ((("C 1" % 10) = 3))
+ Plan Node ID: 1
+ Remote Plans:
+ -------------
+ Plan Node ID 1:
+ Update on "S 1"."T 1"
+ -> Seq Scan on "S 1"."T 1"
+ Output: (c2 + 300), ctid
+ Filter: (("T 1"."C 1" % 10) = 3)
+(12 rows)
+
-- Test UPDATE/DELETE with RETURNING on a three-table join
INSERT INTO ft2 (c1,c2,c3)
SELECT id, id - 1200, to_char(id, 'FM00000') FROM generate_series(1201, 1300) id;
@@ -12322,6 +12920,34 @@ SELECT * FROM insert_tbl ORDER BY a;
2505 | 505 | bar
(2 rows)
+EXPLAIN (REMOTE_PLANS, VERBOSE, COSTS OFF)
+INSERT INTO insert_tbl (SELECT * FROM local_tbl UNION ALL SELECT * FROM remote_tbl);
+ QUERY PLAN
+-------------------------------------------------------------------------
+ Insert on public.insert_tbl
+ Remote SQL: INSERT INTO public.base_tbl4(a, b, c) VALUES ($1, $2, $3)
+ Batch Size: 1
+ Plan Node ID: 0
+ -> Append
+ Plan Node ID: 1
+ -> Seq Scan on public.local_tbl
+ Output: local_tbl.a, local_tbl.b, local_tbl.c
+ Plan Node ID: 2
+ -> Async Foreign Scan on public.remote_tbl
+ Output: remote_tbl.a, remote_tbl.b, remote_tbl.c
+ Remote SQL: SELECT a, b, c FROM public.base_tbl3
+ Plan Node ID: 3
+ Remote Plans:
+ -------------
+ Plan Node ID 0:
+ Insert on public.base_tbl4
+ -> Result
+ Output: $1, $2, $3
+ Plan Node ID 3:
+ Seq Scan on public.base_tbl3
+ Output: a, b, c
+(22 rows)
+
-- Check with direct modify
EXPLAIN (VERBOSE, COSTS OFF)
WITH t AS (UPDATE remote_tbl SET c = c || c RETURNING *)
diff --git a/contrib/postgres_fdw/option.c b/contrib/postgres_fdw/option.c
index 79b16c3..c39a387 100644
--- a/contrib/postgres_fdw/option.c
+++ b/contrib/postgres_fdw/option.c
@@ -17,6 +17,7 @@
#include "catalog/pg_foreign_table.h"
#include "catalog/pg_user_mapping.h"
#include "commands/defrem.h"
+#include "commands/explain.h"
#include "commands/extension.h"
#include "libpq/libpq-be.h"
#include "postgres_fdw.h"
@@ -40,6 +41,13 @@ typedef struct PgFdwOption
*/
static PgFdwOption *postgres_fdw_options;
+/*
+ * EXPLAIN hooks
+ */
+static explain_per_node_hook_type prev_explain_per_node_hook;
+static explain_per_plan_hook_type prev_explain_per_plan_hook;
+static explain_validate_options_hook_type prev_explain_validate_options_hook;
+
/*
* GUC parameters
*/
@@ -566,6 +574,57 @@ process_pgfdw_appname(const char *appname)
return buf.data;
}
+/*
+ * Get the PgFdwExplainState structure from an ExplainState; if there is
+ * none, create one, attach it to the ExplainState, and return it.
+ */
+static PgFdwExplainState *
+pgfdw_ensure_options(ExplainState *es)
+{
+ PgFdwExplainState *pgfdw_explain_state;
+
+ pgfdw_explain_state = GetExplainExtensionState(es, GetExplainExtensionId("postgres_fdw"));
+
+ if (pgfdw_explain_state == NULL)
+ {
+ pgfdw_explain_state = palloc0(sizeof(PgFdwExplainState));
+ SetExplainExtensionState(es, GetExplainExtensionId("postgres_fdw"), pgfdw_explain_state);
+ pgfdw_explain_state->all_remote_plans = NIL;
+ }
+
+ return pgfdw_explain_state;
+}
+
+/*
+ * Parse handler for EXPLAIN (REMOTE_PLANS).
+ */
+static void
+pgfdw_remote_plans_apply(ExplainState *es, DefElem *opt, ParseState *pstate)
+{
+ PgFdwExplainState *options = pgfdw_ensure_options(es);
+
+ options->remote_plans = defGetBoolean(opt);
+}
+
+static void
+postgresExplainValidateOptions(ExplainState *es, List *options, ParseState *pstate)
+{
+ ListCell *lc;
+
+ foreach(lc, options)
+ {
+ DefElem *opt = (DefElem *) lfirst(lc);
+
+ if (strcmp(opt->defname, "remote_plans") == 0)
+ {
+ if (defGetBoolean(opt) && es->analyze)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("EXPLAIN options REMOTE_PLANS and ANALYZE cannot be used together"));
+ }
+ }
+}
+
/*
* Module load callback
*/
@@ -592,4 +651,16 @@ _PG_init(void)
NULL);
MarkGUCPrefixReserved("postgres_fdw");
+
+ RegisterExtensionExplainOption("remote_plans", pgfdw_remote_plans_apply,
+ GUCCheckBooleanExplainOption);
+
+ /* per node EXPLAIN hook */
+ prev_explain_per_node_hook = explain_per_node_hook;
+ explain_per_node_hook = postgresExplainPerNode;
+ prev_explain_per_plan_hook = explain_per_plan_hook;
+ explain_per_plan_hook = postgresExplainPerPlan;
+ prev_explain_validate_options_hook = explain_validate_options_hook;
+ explain_validate_options_hook = postgresExplainValidateOptions;
+
}
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 6dbae58..8eeb1e6 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -137,6 +137,26 @@ enum FdwDirectModifyPrivateIndex
FdwDirectModifyPrivateSetProcessed,
};
+static const char *const explain_formats[] = {
+ [EXPLAIN_FORMAT_TEXT] = "TEXT",
+ [EXPLAIN_FORMAT_JSON] = "JSON",
+ [EXPLAIN_FORMAT_XML] = "XML",
+ [EXPLAIN_FORMAT_YAML] = "YAML",
+};
+
+/*
+ * Track the extension id in the backend.
+ */
+static int extension_id = -1;
+
+static int
+get_extension_id(void)
+{
+ if (extension_id == -1)
+ extension_id = GetExplainExtensionId("postgres_fdw");
+ return extension_id;
+}
+
/*
* Execution state of a foreign scan using postgres_fdw.
*/
@@ -3038,6 +3058,109 @@ postgresEndDirectModify(ForeignScanState *node)
/* MemoryContext will be deleted automatically. */
}
+static void
+postgresExplainStatement(int plan_node_id,
+ ExplainState *es,
+ PgFdwExplainState * pgfdw_explain_state,
+ PGconn *conn,
+ char *sql)
+{
+ PGresult *volatile res = NULL;
+ StringInfoData explain_sql;
+ int remote_version = PQserverVersion(conn);
+
+ /*
+ * REMOTE_PLANS relies on GENERIC_PLAN to explain the statements that
+ * postgres_fdw sends to the remote server: the deparsed SQL embeds $n
+ * parameter placeholders (for parameterized scans, and for INSERT/UPDATE/
+ * DELETE), and a plain remote EXPLAIN would fail with "there is no
+ * parameter $1". GENERIC_PLAN plans such statements without bound values.
+ * It is only available on remote servers running v16 or later, so refuse
+ * to proceed against anything older rather than emit a confusing error.
+ */
+ if (remote_version < 160000)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("EXPLAIN option REMOTE_PLANS requires a remote server version of 16 or later"),
+ errdetail("The remote server version is %d.", remote_version));
+
+ PG_TRY();
+ {
+ int numrows,
+ i;
+ PgFdwExplainRemotePlans *explain = (PgFdwExplainRemotePlans *) palloc(sizeof(PgFdwExplainRemotePlans));
+
+ initStringInfo(&explain_sql);
+ initStringInfo(&explain->explain_plan);
+
+ /*
+ * Build the remote EXPLAIN. GENERIC_PLAN is always forced on (see
+ * above). Of the remaining options we forward only the ones that
+ * describe the plan and that the user actually requested; the
+ * ANALYZE-only options (WAL, TIMING, SERIALIZE, IO, SUMMARY) are
+ * intentionally omitted because REMOTE_PLANS cannot be combined with
+ * ANALYZE. If the user requests an option that the remote server does
+ * not recognize, the remote EXPLAIN simply errors out with
+ * "unrecognized EXPLAIN option", which is a clear enough signal.
+ */
+ appendStringInfoString(&explain_sql, "EXPLAIN (GENERIC_PLAN TRUE");
+ appendStringInfo(&explain_sql, ", VERBOSE %s", es->verbose ? "TRUE" : "FALSE");
+ appendStringInfo(&explain_sql, ", COSTS %s", es->costs ? "TRUE" : "FALSE");
+ appendStringInfo(&explain_sql, ", SETTINGS %s", es->settings ? "TRUE" : "FALSE");
+ appendStringInfo(&explain_sql, ", BUFFERS %s", es->buffers ? "TRUE" : "FALSE");
+ appendStringInfo(&explain_sql, ", MEMORY %s", es->memory ? "TRUE" : "FALSE");
+
+ appendStringInfo(&explain_sql, ", FORMAT %s) %s",
+ explain_formats[es->format], sql);
+
+ /* Run the query and collect the remote plan */
+ res = pgfdw_exec_query(conn, explain_sql.data, NULL);
+ if (PQresultStatus(res) != PGRES_TUPLES_OK)
+ pgfdw_report_error(res, conn, explain_sql.data);
+
+ numrows = PQntuples(res);
+
+ for (i = 0; i < numrows; i++)
+ appendStringInfo(&explain->explain_plan, "%s\n", PQgetvalue(res, i, 0));
+
+ if (explain->explain_plan.len > 0 && explain->explain_plan.data[explain->explain_plan.len - 1] == '\n')
+ explain->explain_plan.data[--explain->explain_plan.len] = '\0';
+
+ explain->plan_node_id = plan_node_id;
+ pgfdw_explain_state->all_remote_plans = lappend(pgfdw_explain_state->all_remote_plans, explain);
+ }
+ PG_FINALLY();
+ {
+ if (res)
+ PQclear(res);
+
+ if (explain_sql.data)
+ pfree(explain_sql.data);
+ }
+ PG_END_TRY();
+}
+
+/*
+ * explain_remote_query
+ * Helper function to get connection and execute remote EXPLAIN
+ */
+static void
+explain_remote_query(int plan_node_id, ExplainState *es,
+ PgFdwExplainState *pgfdw_explain_state,
+ Oid foreign_table_oid, char *sql)
+{
+ UserMapping *user;
+ PGconn *conn;
+ ForeignTable *table;
+
+ table = GetForeignTable(foreign_table_oid);
+ user = GetUserMapping(GetUserId(), table->serverid);
+ conn = GetConnection(user, false, NULL);
+
+ postgresExplainStatement(plan_node_id, es, pgfdw_explain_state, conn, sql);
+ ReleaseConnection(conn);
+}
+
/*
* postgresExplainForeignScan
* Produce extra output for EXPLAIN of a ForeignScan on a foreign table
@@ -3047,6 +3170,12 @@ postgresExplainForeignScan(ForeignScanState *node, ExplainState *es)
{
ForeignScan *plan = castNode(ForeignScan, node->ss.ps.plan);
List *fdw_private = plan->fdw_private;
+ PgFdwExplainState *pgfdw_explain_state;
+ char *sql;
+ Oid foreign_table_oid = InvalidOid;
+
+ if (node->ss.ss_currentRelation)
+ foreign_table_oid = RelationGetRelid(node->ss.ss_currentRelation);
/*
* Identify foreign scans that are really joins or upper relations. The
@@ -3108,6 +3237,13 @@ postgresExplainForeignScan(ForeignScanState *node, ExplainState *es)
Assert(rte->rtekind == RTE_RELATION);
/* This logic should agree with explain.c's ExplainTargetRel */
relname = get_rel_name(rte->relid);
+
+ /*
+ * Save first table OID for getting server connection
+ */
+ if (!OidIsValid(foreign_table_oid))
+ foreign_table_oid = rte->relid;
+
if (es->verbose)
{
char *namespace;
@@ -3133,16 +3269,25 @@ postgresExplainForeignScan(ForeignScanState *node, ExplainState *es)
ExplainPropertyText("Relations", relations.data, es);
}
+ sql = strVal(list_nth(fdw_private, FdwScanPrivateSelectSql));
+
/*
* Add remote query, when VERBOSE option is specified.
*/
if (es->verbose)
- {
- char *sql;
-
- sql = strVal(list_nth(fdw_private, FdwScanPrivateSelectSql));
ExplainPropertyText("Remote SQL", sql, es);
- }
+
+ pgfdw_explain_state = GetExplainExtensionState(es, get_extension_id());
+
+ /* If we don't have a foreign table oid by now, something went wrong */
+ Assert(foreign_table_oid);
+
+ if (pgfdw_explain_state && pgfdw_explain_state->remote_plans)
+ explain_remote_query(node->ss.ps.plan->plan_node_id,
+ es,
+ pgfdw_explain_state,
+ foreign_table_oid,
+ sql);
}
/*
@@ -3156,11 +3301,12 @@ postgresExplainForeignModify(ModifyTableState *mtstate,
int subplan_index,
ExplainState *es)
{
+ char *sql = strVal(list_nth(fdw_private,
+ FdwModifyPrivateUpdateSql));
+ PgFdwExplainState *pgfdw_explain_state;
+
if (es->verbose)
{
- char *sql = strVal(list_nth(fdw_private,
- FdwModifyPrivateUpdateSql));
-
ExplainPropertyText("Remote SQL", sql, es);
/*
@@ -3170,6 +3316,14 @@ postgresExplainForeignModify(ModifyTableState *mtstate,
if (rinfo->ri_BatchSize > 0)
ExplainPropertyInteger("Batch Size", NULL, rinfo->ri_BatchSize, es);
}
+
+ pgfdw_explain_state = GetExplainExtensionState(es, get_extension_id());
+ if (pgfdw_explain_state && pgfdw_explain_state->remote_plans)
+ explain_remote_query(mtstate->ps.plan->plan_node_id,
+ es,
+ pgfdw_explain_state,
+ rinfo->ri_RelationDesc->rd_rel->oid,
+ sql);
}
/*
@@ -3182,13 +3336,22 @@ postgresExplainDirectModify(ForeignScanState *node, ExplainState *es)
{
List *fdw_private;
char *sql;
+ PgFdwExplainState *pgfdw_explain_state;
+
+ fdw_private = ((ForeignScan *) node->ss.ps.plan)->fdw_private;
+ sql = strVal(list_nth(fdw_private, FdwDirectModifyPrivateUpdateSql));
if (es->verbose)
- {
- fdw_private = ((ForeignScan *) node->ss.ps.plan)->fdw_private;
- sql = strVal(list_nth(fdw_private, FdwDirectModifyPrivateUpdateSql));
ExplainPropertyText("Remote SQL", sql, es);
- }
+
+ pgfdw_explain_state = GetExplainExtensionState(es, get_extension_id());
+
+ if (pgfdw_explain_state && pgfdw_explain_state->remote_plans)
+ explain_remote_query(node->ss.ps.plan->plan_node_id,
+ es,
+ pgfdw_explain_state,
+ RelationGetRelid(node->ss.ss_currentRelation),
+ sql);
}
/*
@@ -8835,3 +8998,95 @@ get_batch_size_option(Relation rel)
return batch_size;
}
+
+void
+postgresExplainPerNode(PlanState *planstate, List *ancestors,
+ const char *relationship, const char *plan_name,
+ ExplainState *es)
+{
+ PgFdwExplainState *pgfdw_explain_state;
+
+ pgfdw_explain_state = GetExplainExtensionState(es, get_extension_id());
+
+ if (pgfdw_explain_state == NULL ||
+ !pgfdw_explain_state->remote_plans)
+ return;
+
+ if (pgfdw_explain_state && pgfdw_explain_state->remote_plans)
+ ExplainPropertyInteger("Plan Node ID", NULL, planstate->plan->plan_node_id, es);
+}
+
+static void
+pgfdwFormatRemotePlan(PgFdwExplainRemotePlans * explain,
+ ExplainState *es,
+ int plan_node_id)
+{
+ char *token;
+ StringInfoData remote_plan_name;
+
+ initStringInfo(&remote_plan_name);
+ appendStringInfo(&remote_plan_name, "Plan Node ID %d", plan_node_id);
+
+ ExplainOpenGroup(remote_plan_name.data, remote_plan_name.data, false, es);
+
+ if (es->format == EXPLAIN_FORMAT_TEXT)
+ {
+ appendStringInfo(es->str, "Plan Node ID %d:", plan_node_id);
+ appendStringInfoString(es->str, "\n");
+ }
+
+ while ((token = strsep(&explain->explain_plan.data, "\n")) != NULL)
+ {
+ if (es->format == EXPLAIN_FORMAT_JSON ||
+ es->format == EXPLAIN_FORMAT_YAML)
+ appendStringInfoString(es->str, "\n");
+
+ appendStringInfoSpaces(es->str, (es->indent == 0) ? 2 : es->indent * 2);
+ appendStringInfoString(es->str, token);
+
+ if (es->format == EXPLAIN_FORMAT_XML ||
+ es->format == EXPLAIN_FORMAT_TEXT)
+ appendStringInfoString(es->str, "\n");
+ }
+
+ ExplainCloseGroup(remote_plan_name.data, remote_plan_name.data, false, es);
+ pfree(remote_plan_name.data);
+}
+
+void
+postgresExplainPerPlan(PlannedStmt *plannedstmt,
+ IntoClause *into,
+ ExplainState *es,
+ const char *queryString,
+ ParamListInfo params,
+ QueryEnvironment *queryEnv)
+{
+ ListCell *lc;
+ PgFdwExplainState *pgfdw_explain_state;
+
+ pgfdw_explain_state = GetExplainExtensionState(es, get_extension_id());
+
+ if (pgfdw_explain_state == NULL ||
+ pgfdw_explain_state->all_remote_plans == NIL ||
+ !pgfdw_explain_state->remote_plans)
+ return;
+
+ ExplainOpenGroup("Remote Plans", "Remote Plans", true, es);
+ if (es->format == EXPLAIN_FORMAT_TEXT)
+ {
+ appendStringInfo(es->str, "Remote Plans:\n");
+ appendStringInfo(es->str, "-------------\n");
+ }
+
+ /* Process every remote plan captured */
+ foreach(lc, pgfdw_explain_state->all_remote_plans)
+ {
+ PgFdwExplainRemotePlans *explain = (PgFdwExplainRemotePlans *) lfirst(lc);
+
+ pgfdwFormatRemotePlan(explain,
+ es,
+ explain->plan_node_id);
+ }
+
+ ExplainCloseGroup("Remote Plans", "Remote Plans", true, es);
+}
diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h
index a2bb1ff..cf74dba 100644
--- a/contrib/postgres_fdw/postgres_fdw.h
+++ b/contrib/postgres_fdw/postgres_fdw.h
@@ -13,6 +13,7 @@
#ifndef POSTGRES_FDW_H
#define POSTGRES_FDW_H
+#include "commands/explain_state.h"
#include "foreign/foreign.h"
#include "lib/stringinfo.h"
#include "libpq/libpq-be-fe.h"
@@ -151,6 +152,21 @@ typedef enum PgFdwSamplingMethod
ANALYZE_SAMPLE_BERNOULLI, /* TABLESAMPLE bernoulli */
} PgFdwSamplingMethod;
+typedef struct PgFdwExplainRemotePlans
+{
+ int plan_node_id;
+ StringInfoData explain_plan;
+
+} PgFdwExplainRemotePlans;
+
+typedef struct PgFdwExplainState
+{
+ List *all_remote_plans;
+
+ /* EXPLAIN options */
+ bool remote_plans;
+} PgFdwExplainState;
+
/* in postgres_fdw.c */
extern int set_transmission_modes(void);
extern void reset_transmission_modes(int nestlevel);
@@ -178,6 +194,16 @@ extern int ExtractConnectionOptions(List *defelems,
extern List *ExtractExtensionList(const char *extensionsString,
bool warnOnMissing);
extern char *process_pgfdw_appname(const char *appname);
+extern void postgresExplainPerNode(PlanState *planstate, List *ancestors,
+ const char *relationship,
+ const char *plan_name,
+ ExplainState *es);
+extern void postgresExplainPerPlan(PlannedStmt *plannedstmt,
+ IntoClause *into,
+ ExplainState *es,
+ const char *queryString,
+ ParamListInfo params,
+ QueryEnvironment *queryEnv);
extern char *pgfdw_application_name;
/* in deparse.c */
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index dfc58be..26a3f4f 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -311,6 +311,11 @@ SELECT * FROM ft1 t1 WHERE t1.c3 = (SELECT MAX(c3) FROM ft2 t2) ORDER BY c1;
WITH t1 AS (SELECT * FROM ft1 WHERE c1 <= 10) SELECT t2.c1, t2.c2, t2.c3, t2.c4 FROM t1, ft2 t2 WHERE t1.c1 = t2.c1 ORDER BY t1.c1;
-- fixed values
SELECT 'fixed', NULL FROM ft1 t1 WHERE c1 = 1;
+-- with WHERE clause and remote_plans with different formats
+EXPLAIN (REMOTE_PLANS, FORMAT YAML, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+EXPLAIN (REMOTE_PLANS TRUE, FORMAT XML, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+EXPLAIN (REMOTE_PLANS, FORMAT JSON, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+EXPLAIN (REMOTE_PLANS TRUE, FORMAT TEXT, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
-- Test forcing the remote server to produce sorted data for a merge join.
SET enable_hashjoin TO false;
SET enable_nestloop TO false;
@@ -338,6 +343,12 @@ SELECT t1."C 1", t2.c1, t3.c1 FROM "S 1"."T 1" t1 left join ft1 t2 full join ft2
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1."C 1", t2.c1, t3.c1 FROM "S 1"."T 1" t1 full join ft1 t2 full join ft2 t3 on (t2.c1 = t3.c1) on (t3.c1 = t1."C 1") OFFSET 100 LIMIT 10;
SELECT t1."C 1", t2.c1, t3.c1 FROM "S 1"."T 1" t1 full join ft1 t2 full join ft2 t3 on (t2.c1 = t3.c1) on (t3.c1 = t1."C 1") OFFSET 100 LIMIT 10;
+
+-- Join push-down test
+-- Ensure join conditions are pushed down to the foreign server
+EXPLAIN (REMOTE_PLANS, VERBOSE, COSTS OFF)
+ SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) WHERE t1.c2 = 10;
+
RESET enable_hashjoin;
RESET enable_nestloop;
@@ -379,6 +390,11 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c8 = 'foo'; -- can't be
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM "S 1"."T 1" a, ft2 b WHERE a."C 1" = 47 AND b.c1 = a.c2;
SELECT * FROM "S 1"."T 1" a, ft2 b WHERE a."C 1" = 47 AND b.c1 = a.c2;
+-- REMOTE_PLANS over a parameterized foreign scan: the deparsed remote SQL
+-- carries a $1 placeholder, which only plans on the remote side because
+-- REMOTE_PLANS forces GENERIC_PLAN.
+EXPLAIN (REMOTE_PLANS, VERBOSE, COSTS OFF)
+ SELECT * FROM "S 1"."T 1" a, ft2 b WHERE a."C 1" = 47 AND b.c1 = a.c2;
-- check both safe and unsafe join conditions
EXPLAIN (VERBOSE, COSTS OFF)
@@ -1527,6 +1543,33 @@ SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
ORDER BY ft1.c1 LIMIT 5;
+-- EXPLAIN remote_plans
+EXPLAIN (remote_plans, format text, costs off, analyze)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+EXPLAIN (remote_plans, format text, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+EXPLAIN (remote_plans, format xml, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+EXPLAIN (remote_plans, format json, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+EXPLAIN (remote_plans, format yaml, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+
-- ===================================================================
-- test writable foreign table stuff
-- ===================================================================
@@ -1587,6 +1630,10 @@ DELETE FROM ft8 FOR PORTION OF c4 FROM '2005-01-01' TO '2006-01-01'
WHERE c1 = '[2,3)';
SELECT * FROM ft8 WHERE c1 = '[2,3)' ORDER BY c1, c4;
+-- test write on foreign tables with remote_plans
+EXPLAIN (remote_plans, verbose, costs off)
+UPDATE ft2 SET c2 = c2 + 300 WHERE c1 % 10 = 3;
+
-- Test UPDATE/DELETE with RETURNING on a three-table join
INSERT INTO ft2 (c1,c2,c3)
SELECT id, id - 1200, to_char(id, 'FM00000') FROM generate_series(1201, 1300) id;
@@ -4194,6 +4241,9 @@ INSERT INTO insert_tbl (SELECT * FROM local_tbl UNION ALL SELECT * FROM remote_t
SELECT * FROM insert_tbl ORDER BY a;
+EXPLAIN (REMOTE_PLANS, VERBOSE, COSTS OFF)
+INSERT INTO insert_tbl (SELECT * FROM local_tbl UNION ALL SELECT * FROM remote_tbl);
+
-- Check with direct modify
EXPLAIN (VERBOSE, COSTS OFF)
WITH t AS (UPDATE remote_tbl SET c = c || c RETURNING *)
diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index b9e1b04..d36a9f9 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -1197,6 +1197,47 @@ CREATE SUBSCRIPTION my_subscription SERVER subscription_server PUBLICATION testp
The query that is actually sent to the remote server for execution can
be examined using <command>EXPLAIN VERBOSE</command>.
</para>
+
+ <para>
+ In addition to the remote query itself, the plan that the remote server
+ chooses for that query can be examined with the
+ <literal>REMOTE_PLANS</literal> option of <command>EXPLAIN</command>, for
+ example <literal>EXPLAIN (VERBOSE, REMOTE_PLANS) SELECT ...</literal>. For
+ every foreign scan or foreign modification in the local plan,
+ <filename>postgres_fdw</filename> runs <command>EXPLAIN</command> on the
+ remote server for the corresponding remote query and prints the result in a
+ <literal>Remote Plans</literal> section, keyed by the plan node id of the
+ local node.
+ </para>
+
+ <para>
+ The <literal>REMOTE_PLANS</literal> option is registered when
+ <filename>postgres_fdw</filename> is loaded, so the module must be loaded
+ (for example with <command>CREATE EXTENSION postgres_fdw</command>) before
+ the option can be used. The following restrictions apply:
+ <itemizedlist>
+ <listitem>
+ <para>
+ It cannot be combined with <literal>ANALYZE</literal>; doing so raises an
+ error. The remote query is only planned, never executed, so the foreign
+ server is not affected and no remote rows are fetched.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ The remote server must be running <productname>PostgreSQL</productname>
+ 16 or later. The remote query that <filename>postgres_fdw</filename>
+ sends may contain parameter placeholders (for parameterized foreign
+ scans, and for <command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command>),
+ so the option relies on the <literal>GENERIC_PLAN</literal> option of
+ <command>EXPLAIN</command>, which was introduced in that release. As a
+ consequence, the remote plans shown are generic plans that treat those
+ placeholders as unknown values rather than plans for specific parameter
+ values.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
</sect2>
<sect2 id="postgres-fdw-remote-query-execution-environment">
diff --git a/src/include/commands/explain_state.h b/src/include/commands/explain_state.h
index 97bc7ed..ba09c1d 100644
--- a/src/include/commands/explain_state.h
+++ b/src/include/commands/explain_state.h
@@ -45,7 +45,7 @@ typedef struct ExplainWorkersState
typedef struct ExplainState
{
StringInfo str; /* output buffer */
- /* options */
+ /* options. considering updating logic to create explain_sql in postgres_fdw extension if adding new option here.*/
bool verbose; /* be verbose */
bool analyze; /* print actual times */
bool costs; /* print estimated costs */
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index f9eb23e..c7b1140 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2304,6 +2304,8 @@ PgChecksumMode
PgFdwAnalyzeState
PgFdwConnState
PgFdwDirectModifyState
+PgFdwExplainRemotePlans
+PgFdwExplainState
PgFdwModifyState
PgFdwOption
PgFdwPathExtraData
base-commit: 2963ddeef2be6d6e064cb9d382f67dcbf2c049a8
--
2.50.1 (Apple Git-155)
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: explain plans for foreign servers
@ 2026-07-03 18:35 Sami Imseih <[email protected]>
parent: dinesh salve <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Sami Imseih @ 2026-07-03 18:35 UTC (permalink / raw)
To: dinesh salve <[email protected]>; +Cc: Tom Lane <[email protected]>; pgsql-hackers; Jeff Davis <[email protected]>
Hi Dinesh,
Thanks for the patch. v2 needed rebasing, but I also went ahead and applied
my comments to v3.
1/ Forward all user options instead of hardcoding ExplainState booleans
The current approach reads individual ExplainState fields to construct the
remote EXPLAIN:
```
appendStringInfo(&explain_sql, ", VERBOSE %s", es->verbose ? "TRUE" : "FALSE");
...
.....
```
After thinking about this a bit, this creates a maintenance problem.
Any new non-ANALYZE EXPLAIN option added to core requires someone to
remember to update postgres_fdw. I previously suggested a comment
in explain_state.h to remind developers, but I think we should do
something better.
The explain_validate_options_hook already receives the user's
original option list. We save it in PgFdwExplainState and use
to construct the remote EXPLAIN SQL, skipping only remote_plans
and generic_plan.
New core EXPLAIN options will automatically be forwarded to the
remote when the user specifies them, with no postgres_fdw code
change needed. If the remote doesn't recognize an option, it errors
clearly rather than silently ignoring it.
I tested this against a PG 16 remote. EXPLAIN (REMOTE_PLANS, MEMORY)
correctly errors with "unrecognized EXPLAIN option 'memory'" since
PG 16 does not have MEMORY. This seems better than silent omission.
2/ I also made some comment improvements. Some were just too verbose.
3/ Documentation improvements
The generic plan limitation only matters when the remote SQL contains
parameter placeholders. For queries with only literals (e.g.,
WHERE id = 42), the generic plan is identical to a custom plan. The docs
should clarify this rather than making it seem like a general limitation.
I also added some concrete examples showing both cases where a literal
is used vs a parameter.
Attached is my attempt at the above. What do you think?
--
Sami Imseih
Amazon Web Services (AWS)
Attachments:
[application/octet-stream] v3-0001-postgres_fdw-show-remote-EXPLAIN-plans-via-REMOTE.patch (69.3K, ../../CAA5RZ0t_5rMsjFficztwJD7nuupbS4GDj0suKxfShdRXjm7c0A@mail.gmail.com/2-v3-0001-postgres_fdw-show-remote-EXPLAIN-plans-via-REMOTE.patch)
download | inline diff:
From 487844c90bed811dcc93b44dcf9c9e399ff1b246 Mon Sep 17 00:00:00 2001
From: Dinesh Salve <[email protected]>
Date: Sat, 20 Jun 2026 00:26:00 +0530
Subject: [PATCH v3] postgres_fdw: show remote EXPLAIN plans via REMOTE_PLANS
option
Add a REMOTE_PLANS option to EXPLAIN that, for every foreign scan or
foreign modification, runs EXPLAIN on the remote server for the
corresponding remote query and prints the result in a "Remote Plans"
section keyed by the local plan node id.
The remote query that postgres_fdw sends often contains $n parameter
placeholders (parameterized foreign scans and joins, subquery outputs,
and INSERT/UPDATE/DELETE), which a plain remote EXPLAIN cannot plan and
fails with "there is no parameter $1". REMOTE_PLANS therefore forces
GENERIC_PLAN on the remote EXPLAIN, which plans such statements without
bound values. GENERIC_PLAN is only available from PostgreSQL 16, so
REMOTE_PLANS errors out on older remote servers.
REMOTE_PLANS cannot be combined with ANALYZE. All user-specified EXPLAIN
options (other than REMOTE_PLANS and GENERIC_PLAN) are forwarded to the
remote server as-is. If the remote server does not recognize a forwarded
option, it reports an error.
The option is registered when postgres_fdw is loaded.
---
.../postgres_fdw/expected/postgres_fdw.out | 626 ++++++++++++++++++
contrib/postgres_fdw/option.c | 75 +++
contrib/postgres_fdw/postgres_fdw.c | 268 +++++++-
contrib/postgres_fdw/postgres_fdw.h | 27 +
contrib/postgres_fdw/sql/postgres_fdw.sql | 49 ++
doc/src/sgml/postgres-fdw.sgml | 87 +++
src/include/commands/explain_state.h | 1 +
src/tools/pgindent/typedefs.list | 2 +
8 files changed, 1123 insertions(+), 12 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 0805c56cb1b..f4326714381 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -471,6 +471,173 @@ SELECT 'fixed', NULL FROM ft1 t1 WHERE c1 = 1;
fixed |
(1 row)
+-- with WHERE clause and remote_plans with different formats
+EXPLAIN (REMOTE_PLANS, FORMAT YAML, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------
+ - Plan: +
+ Node Type: "Foreign Scan" +
+ Operation: "Select" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Relation Name: "ft1" +
+ Schema: "public" +
+ Alias: "t1" +
+ Disabled: false +
+ Output: +
+ - "c1" +
+ - "c2" +
+ - "c3" +
+ - "c4" +
+ - "c5" +
+ - "c6" +
+ - "c7" +
+ - "c8" +
+ Remote SQL: "SELECT \"C 1\", c2, c3, c4, c5, c6, c7, c8 FROM \"S 1\".\"T 1\" WHERE ((\"C 1\" = 101))"+
+ Plan Node ID: 0 +
+ Remote Plans: +
+ Plan Node ID 0: +
+ - Plan: +
+ Node Type: "Index Scan" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Scan Direction: "Forward" +
+ Index Name: "t1_pkey" +
+ Relation Name: "T 1" +
+ Schema: "S 1" +
+ Alias: "T 1" +
+ Disabled: false +
+ Output: +
+ - "\"C 1\"" +
+ - "c2" +
+ - "c3" +
+ - "c4" +
+ - "c5" +
+ - "c6" +
+ - "c7" +
+ - "c8" +
+ Index Cond: "(\"T 1\".\"C 1\" = 101)"
+(1 row)
+
+EXPLAIN (REMOTE_PLANS TRUE, FORMAT XML, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------
+ <explain xmlns="http://www.postgresql.org/2009/explain"> +
+ <Query> +
+ <Plan> +
+ <Node-Type>Foreign Scan</Node-Type> +
+ <Operation>Select</Operation> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Relation-Name>ft1</Relation-Name> +
+ <Schema>public</Schema> +
+ <Alias>t1</Alias> +
+ <Disabled>false</Disabled> +
+ <Output> +
+ <Item>c1</Item> +
+ <Item>c2</Item> +
+ <Item>c3</Item> +
+ <Item>c4</Item> +
+ <Item>c5</Item> +
+ <Item>c6</Item> +
+ <Item>c7</Item> +
+ <Item>c8</Item> +
+ </Output> +
+ <Remote-SQL>SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 101))</Remote-SQL>+
+ <Plan-Node-ID>0</Plan-Node-ID> +
+ </Plan> +
+ <Remote-Plans> +
+ <Plan-Node-ID-0> +
+ <explain xmlns="http://www.postgresql.org/2009/explain"> +
+ <Query> +
+ <Plan> +
+ <Node-Type>Index Scan</Node-Type> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Scan-Direction>Forward</Scan-Direction> +
+ <Index-Name>t1_pkey</Index-Name> +
+ <Relation-Name>T 1</Relation-Name> +
+ <Schema>S 1</Schema> +
+ <Alias>T 1</Alias> +
+ <Disabled>false</Disabled> +
+ <Output> +
+ <Item>"C 1"</Item> +
+ <Item>c2</Item> +
+ <Item>c3</Item> +
+ <Item>c4</Item> +
+ <Item>c5</Item> +
+ <Item>c6</Item> +
+ <Item>c7</Item> +
+ <Item>c8</Item> +
+ </Output> +
+ <Index-Cond>("T 1"."C 1" = 101)</Index-Cond> +
+ </Plan> +
+ </Query> +
+ </explain> +
+ </Plan-Node-ID-0> +
+ </Remote-Plans> +
+ </Query> +
+ </explain>
+(1 row)
+
+EXPLAIN (REMOTE_PLANS, FORMAT JSON, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Foreign Scan", +
+ "Operation": "Select", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Relation Name": "ft1", +
+ "Schema": "public", +
+ "Alias": "t1", +
+ "Disabled": false, +
+ "Output": ["c1", "c2", "c3", "c4", "c5", "c6", "c7", "c8"], +
+ "Remote SQL": "SELECT \"C 1\", c2, c3, c4, c5, c6, c7, c8 FROM \"S 1\".\"T 1\" WHERE ((\"C 1\" = 101))",+
+ "Plan Node ID": 0 +
+ }, +
+ "Remote Plans": { +
+ "Plan Node ID 0": [ +
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Index Scan", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Scan Direction": "Forward", +
+ "Index Name": "t1_pkey", +
+ "Relation Name": "T 1", +
+ "Schema": "S 1", +
+ "Alias": "T 1", +
+ "Disabled": false, +
+ "Output": ["\"C 1\"", "c2", "c3", "c4", "c5", "c6", "c7", "c8"], +
+ "Index Cond": "(\"T 1\".\"C 1\" = 101)" +
+ } +
+ } +
+ ] +
+ ] +
+ } +
+ } +
+ ]
+(1 row)
+
+EXPLAIN (REMOTE_PLANS TRUE, FORMAT TEXT, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------
+ Foreign Scan on public.ft1 t1
+ Output: c1, c2, c3, c4, c5, c6, c7, c8
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 101))
+ Plan Node ID: 0
+ Remote Plans:
+ -------------
+ Plan Node ID 0:
+ Index Scan using t1_pkey on "S 1"."T 1"
+ Output: "C 1", c2, c3, c4, c5, c6, c7, c8
+ Index Cond: ("T 1"."C 1" = 101)
+(10 rows)
+
-- Test forcing the remote server to produce sorted data for a merge join.
SET enable_hashjoin TO false;
SET enable_nestloop TO false;
@@ -646,6 +813,25 @@ SELECT t1."C 1", t2.c1, t3.c1 FROM "S 1"."T 1" t1 full join ft1 t2 full join ft2
110 | 110 | 110
(10 rows)
+-- Join push-down test
+-- Ensure join conditions are pushed down to the foreign server
+EXPLAIN (REMOTE_PLANS, VERBOSE, COSTS OFF)
+ SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) WHERE t1.c2 = 10;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+ Output: t1.c1, t2.c1
+ Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
+ Remote SQL: SELECT r1."C 1", r2."C 1" FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r2."C 1" = r1."C 1")) AND ((r1.c2 = 10))))
+ Plan Node ID: 0
+ Remote Plans:
+ -------------
+ Plan Node ID 0:
+ Seq Scan on "S 1"."T 1" r2
+ Output: r2."C 1", r2."C 1"
+ Filter: (r2.c2 = 10)
+(11 rows)
+
RESET enable_hashjoin;
RESET enable_nestloop;
-- Test executing assertion in estimate_path_cost_size() that makes sure that
@@ -790,6 +976,32 @@ SELECT * FROM "S 1"."T 1" a, ft2 b WHERE a."C 1" = 47 AND b.c1 = a.c2;
47 | 7 | 00047 | Tue Feb 17 00:00:00 1970 PST | Tue Feb 17 00:00:00 1970 | 7 | 7 | foo | 7 | 7 | 00007 | Thu Jan 08 00:00:00 1970 PST | Thu Jan 08 00:00:00 1970 | 7 | 7 | foo
(1 row)
+-- REMOTE_PLANS over a parameterized foreign scan: the deparsed remote SQL
+-- carries a $1 placeholder, which only plans on the remote side because
+-- REMOTE_PLANS forces GENERIC_PLAN.
+EXPLAIN (REMOTE_PLANS, VERBOSE, COSTS OFF)
+ SELECT * FROM "S 1"."T 1" a, ft2 b WHERE a."C 1" = 47 AND b.c1 = a.c2;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------
+ Nested Loop
+ Output: a."C 1", a.c2, a.c3, a.c4, a.c5, a.c6, a.c7, a.c8, b.c1, b.c2, b.c3, b.c4, b.c5, b.c6, b.c7, b.c8
+ Plan Node ID: 0
+ -> Index Scan using t1_pkey on "S 1"."T 1" a
+ Output: a."C 1", a.c2, a.c3, a.c4, a.c5, a.c6, a.c7, a.c8
+ Index Cond: (a."C 1" = 47)
+ Plan Node ID: 1
+ -> Foreign Scan on public.ft2 b
+ Output: b.c1, b.c2, b.c3, b.c4, b.c5, b.c6, b.c7, b.c8
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = $1::integer))
+ Plan Node ID: 2
+ Remote Plans:
+ -------------
+ Plan Node ID 2:
+ Index Scan using t1_pkey on "S 1"."T 1"
+ Output: "C 1", c2, c3, c4, c5, c6, c7, c8
+ Index Cond: ("T 1"."C 1" = $1)
+(17 rows)
+
-- check both safe and unsafe join conditions
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM ft2 a, ft2 b
@@ -5116,6 +5328,373 @@ SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
Remote SQL: SELECT r5."C 1", r6.c1 FROM ("S 1"."T 1" r5 INNER JOIN "S 1"."T 3" r6 ON (((r5."C 1" = r6.c1)))) ORDER BY r5."C 1" ASC NULLS LAST
(13 rows)
+-- EXPLAIN remote_plans
+EXPLAIN (remote_plans, format text, costs off, analyze)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+ERROR: EXPLAIN options REMOTE_PLANS and ANALYZE cannot be used together
+EXPLAIN (remote_plans, format text, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+ QUERY PLAN
+-------------------------------------------------------
+ Limit
+ Plan Node ID: 0
+ -> Merge Semi Join
+ Merge Cond: (ft1.c1 = ft2_1.c1)
+ Plan Node ID: 1
+ -> Foreign Scan
+ Relations: (ft1) INNER JOIN (ft2)
+ Plan Node ID: 2
+ -> Foreign Scan
+ Relations: (ft2 ft2_1) INNER JOIN (ft4)
+ Plan Node ID: 3
+ Remote Plans:
+ -------------
+ Plan Node ID 2:
+ Index Only Scan using t1_pkey on "T 1" r2
+ Plan Node ID 3:
+ Merge Join
+ Merge Cond: (r5."C 1" = r6.c1)
+ -> Index Only Scan using t1_pkey on "T 1" r5
+ -> Sort
+ Sort Key: r6.c1
+ -> Seq Scan on "T 3" r6
+(22 rows)
+
+EXPLAIN (remote_plans, format xml, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+ QUERY PLAN
+------------------------------------------------------------------------
+ <explain xmlns="http://www.postgresql.org/2009/explain"> +
+ <Query> +
+ <Plan> +
+ <Node-Type>Limit</Node-Type> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Disabled>false</Disabled> +
+ <Plan-Node-ID>0</Plan-Node-ID> +
+ <Plans> +
+ <Plan> +
+ <Node-Type>Merge Join</Node-Type> +
+ <Parent-Relationship>Outer</Parent-Relationship> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Join-Type>Semi</Join-Type> +
+ <Disabled>false</Disabled> +
+ <Inner-Unique>false</Inner-Unique> +
+ <Merge-Cond>(ft1.c1 = ft2_1.c1)</Merge-Cond> +
+ <Plan-Node-ID>1</Plan-Node-ID> +
+ <Plans> +
+ <Plan> +
+ <Node-Type>Foreign Scan</Node-Type> +
+ <Operation>Select</Operation> +
+ <Parent-Relationship>Outer</Parent-Relationship> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Disabled>false</Disabled> +
+ <Relations>(ft1) INNER JOIN (ft2)</Relations> +
+ <Plan-Node-ID>2</Plan-Node-ID> +
+ </Plan> +
+ <Plan> +
+ <Node-Type>Foreign Scan</Node-Type> +
+ <Operation>Select</Operation> +
+ <Parent-Relationship>Inner</Parent-Relationship> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Disabled>false</Disabled> +
+ <Relations>(ft2 ft2_1) INNER JOIN (ft4)</Relations> +
+ <Plan-Node-ID>3</Plan-Node-ID> +
+ </Plan> +
+ </Plans> +
+ </Plan> +
+ </Plans> +
+ </Plan> +
+ <Remote-Plans> +
+ <Plan-Node-ID-2> +
+ <explain xmlns="http://www.postgresql.org/2009/explain"> +
+ <Query> +
+ <Plan> +
+ <Node-Type>Index Only Scan</Node-Type> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Scan-Direction>Forward</Scan-Direction> +
+ <Index-Name>t1_pkey</Index-Name> +
+ <Relation-Name>T 1</Relation-Name> +
+ <Alias>r2</Alias> +
+ <Disabled>false</Disabled> +
+ </Plan> +
+ </Query> +
+ </explain> +
+ </Plan-Node-ID-2> +
+ <Plan-Node-ID-3> +
+ <explain xmlns="http://www.postgresql.org/2009/explain"> +
+ <Query> +
+ <Plan> +
+ <Node-Type>Merge Join</Node-Type> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Join-Type>Inner</Join-Type> +
+ <Disabled>false</Disabled> +
+ <Inner-Unique>true</Inner-Unique> +
+ <Merge-Cond>(r5."C 1" = r6.c1)</Merge-Cond> +
+ <Plans> +
+ <Plan> +
+ <Node-Type>Index Only Scan</Node-Type> +
+ <Parent-Relationship>Outer</Parent-Relationship> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Scan-Direction>Forward</Scan-Direction> +
+ <Index-Name>t1_pkey</Index-Name> +
+ <Relation-Name>T 1</Relation-Name> +
+ <Alias>r5</Alias> +
+ <Disabled>false</Disabled> +
+ </Plan> +
+ <Plan> +
+ <Node-Type>Sort</Node-Type> +
+ <Parent-Relationship>Inner</Parent-Relationship> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Disabled>false</Disabled> +
+ <Sort-Key> +
+ <Item>r6.c1</Item> +
+ </Sort-Key> +
+ <Plans> +
+ <Plan> +
+ <Node-Type>Seq Scan</Node-Type> +
+ <Parent-Relationship>Outer</Parent-Relationship>+
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Relation-Name>T 3</Relation-Name> +
+ <Alias>r6</Alias> +
+ <Disabled>false</Disabled> +
+ </Plan> +
+ </Plans> +
+ </Plan> +
+ </Plans> +
+ </Plan> +
+ </Query> +
+ </explain> +
+ </Plan-Node-ID-3> +
+ </Remote-Plans> +
+ </Query> +
+ </explain>
+(1 row)
+
+EXPLAIN (remote_plans, format json, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+ QUERY PLAN
+------------------------------------------------------------
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Limit", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Disabled": false, +
+ "Plan Node ID": 0, +
+ "Plans": [ +
+ { +
+ "Node Type": "Merge Join", +
+ "Parent Relationship": "Outer", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Join Type": "Semi", +
+ "Disabled": false, +
+ "Inner Unique": false, +
+ "Merge Cond": "(ft1.c1 = ft2_1.c1)", +
+ "Plan Node ID": 1, +
+ "Plans": [ +
+ { +
+ "Node Type": "Foreign Scan", +
+ "Operation": "Select", +
+ "Parent Relationship": "Outer", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Disabled": false, +
+ "Relations": "(ft1) INNER JOIN (ft2)", +
+ "Plan Node ID": 2 +
+ }, +
+ { +
+ "Node Type": "Foreign Scan", +
+ "Operation": "Select", +
+ "Parent Relationship": "Inner", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Disabled": false, +
+ "Relations": "(ft2 ft2_1) INNER JOIN (ft4)",+
+ "Plan Node ID": 3 +
+ } +
+ ] +
+ } +
+ ] +
+ }, +
+ "Remote Plans": { +
+ "Plan Node ID 2": [ +
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Index Only Scan", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Scan Direction": "Forward", +
+ "Index Name": "t1_pkey", +
+ "Relation Name": "T 1", +
+ "Alias": "r2", +
+ "Disabled": false +
+ } +
+ } +
+ ] +
+ ], +
+ "Plan Node ID 3": [ +
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Merge Join", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Join Type": "Inner", +
+ "Disabled": false, +
+ "Inner Unique": true, +
+ "Merge Cond": "(r5.\"C 1\" = r6.c1)", +
+ "Plans": [ +
+ { +
+ "Node Type": "Index Only Scan", +
+ "Parent Relationship": "Outer", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Scan Direction": "Forward", +
+ "Index Name": "t1_pkey", +
+ "Relation Name": "T 1", +
+ "Alias": "r5", +
+ "Disabled": false +
+ }, +
+ { +
+ "Node Type": "Sort", +
+ "Parent Relationship": "Inner", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Disabled": false, +
+ "Sort Key": ["r6.c1"], +
+ "Plans": [ +
+ { +
+ "Node Type": "Seq Scan", +
+ "Parent Relationship": "Outer", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Relation Name": "T 3", +
+ "Alias": "r6", +
+ "Disabled": false +
+ } +
+ ] +
+ } +
+ ] +
+ } +
+ } +
+ ] +
+ ] +
+ } +
+ } +
+ ]
+(1 row)
+
+EXPLAIN (remote_plans, format yaml, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+ QUERY PLAN
+-------------------------------------------------------
+ - Plan: +
+ Node Type: "Limit" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Disabled: false +
+ Plan Node ID: 0 +
+ Plans: +
+ - Node Type: "Merge Join" +
+ Parent Relationship: "Outer" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Join Type: "Semi" +
+ Disabled: false +
+ Inner Unique: false +
+ Merge Cond: "(ft1.c1 = ft2_1.c1)" +
+ Plan Node ID: 1 +
+ Plans: +
+ - Node Type: "Foreign Scan" +
+ Operation: "Select" +
+ Parent Relationship: "Outer" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Disabled: false +
+ Relations: "(ft1) INNER JOIN (ft2)" +
+ Plan Node ID: 2 +
+ - Node Type: "Foreign Scan" +
+ Operation: "Select" +
+ Parent Relationship: "Inner" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Disabled: false +
+ Relations: "(ft2 ft2_1) INNER JOIN (ft4)"+
+ Plan Node ID: 3 +
+ Remote Plans: +
+ Plan Node ID 2: +
+ - Plan: +
+ Node Type: "Index Only Scan" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Scan Direction: "Forward" +
+ Index Name: "t1_pkey" +
+ Relation Name: "T 1" +
+ Alias: "r2" +
+ Disabled: false +
+ Plan Node ID 3: +
+ - Plan: +
+ Node Type: "Merge Join" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Join Type: "Inner" +
+ Disabled: false +
+ Inner Unique: true +
+ Merge Cond: "(r5.\"C 1\" = r6.c1)" +
+ Plans: +
+ - Node Type: "Index Only Scan" +
+ Parent Relationship: "Outer" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Scan Direction: "Forward" +
+ Index Name: "t1_pkey" +
+ Relation Name: "T 1" +
+ Alias: "r5" +
+ Disabled: false +
+ - Node Type: "Sort" +
+ Parent Relationship: "Inner" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Disabled: false +
+ Sort Key: +
+ - "r6.c1" +
+ Plans: +
+ - Node Type: "Seq Scan" +
+ Parent Relationship: "Outer" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Relation Name: "T 3" +
+ Alias: "r6" +
+ Disabled: false
+(1 row)
+
-- ===================================================================
-- test writable foreign table stuff
-- ===================================================================
@@ -6390,6 +6969,25 @@ SELECT c1, c2, c3, c4 FROM fpo_part_local ORDER BY c4;
(3 rows)
DROP TABLE fpo_part_parent;
+-- test write on foreign tables with remote_plans
+EXPLAIN (remote_plans, verbose, costs off)
+UPDATE ft2 SET c2 = c2 + 300 WHERE c1 % 10 = 3;
+ QUERY PLAN
+---------------------------------------------------------------------------------------
+ Update on public.ft2
+ Plan Node ID: 0
+ -> Foreign Update on public.ft2
+ Remote SQL: UPDATE "S 1"."T 1" SET c2 = (c2 + 300) WHERE ((("C 1" % 10) = 3))
+ Plan Node ID: 1
+ Remote Plans:
+ -------------
+ Plan Node ID 1:
+ Update on "S 1"."T 1"
+ -> Seq Scan on "S 1"."T 1"
+ Output: (c2 + 300), ctid
+ Filter: (("T 1"."C 1" % 10) = 3)
+(12 rows)
+
-- Test UPDATE/DELETE with RETURNING on a three-table join
INSERT INTO ft2 (c1,c2,c3)
SELECT id, id - 1200, to_char(id, 'FM00000') FROM generate_series(1201, 1300) id;
@@ -12422,6 +13020,34 @@ SELECT * FROM insert_tbl ORDER BY a;
2505 | 505 | bar
(2 rows)
+EXPLAIN (REMOTE_PLANS, VERBOSE, COSTS OFF)
+INSERT INTO insert_tbl (SELECT * FROM local_tbl UNION ALL SELECT * FROM remote_tbl);
+ QUERY PLAN
+-------------------------------------------------------------------------
+ Insert on public.insert_tbl
+ Remote SQL: INSERT INTO public.base_tbl4(a, b, c) VALUES ($1, $2, $3)
+ Batch Size: 1
+ Plan Node ID: 0
+ -> Append
+ Plan Node ID: 1
+ -> Seq Scan on public.local_tbl
+ Output: local_tbl.a, local_tbl.b, local_tbl.c
+ Plan Node ID: 2
+ -> Async Foreign Scan on public.remote_tbl
+ Output: remote_tbl.a, remote_tbl.b, remote_tbl.c
+ Remote SQL: SELECT a, b, c FROM public.base_tbl3
+ Plan Node ID: 3
+ Remote Plans:
+ -------------
+ Plan Node ID 0:
+ Insert on public.base_tbl4
+ -> Result
+ Output: $1, $2, $3
+ Plan Node ID 3:
+ Seq Scan on public.base_tbl3
+ Output: a, b, c
+(22 rows)
+
-- Check with direct modify
EXPLAIN (VERBOSE, COSTS OFF)
WITH t AS (UPDATE remote_tbl SET c = c || c RETURNING *)
diff --git a/contrib/postgres_fdw/option.c b/contrib/postgres_fdw/option.c
index 79b16c3f318..43cf0196bbb 100644
--- a/contrib/postgres_fdw/option.c
+++ b/contrib/postgres_fdw/option.c
@@ -17,6 +17,7 @@
#include "catalog/pg_foreign_table.h"
#include "catalog/pg_user_mapping.h"
#include "commands/defrem.h"
+#include "commands/explain.h"
#include "commands/extension.h"
#include "libpq/libpq-be.h"
#include "postgres_fdw.h"
@@ -40,6 +41,13 @@ typedef struct PgFdwOption
*/
static PgFdwOption *postgres_fdw_options;
+/*
+ * EXPLAIN hooks
+ */
+static explain_per_node_hook_type prev_explain_per_node_hook;
+static explain_per_plan_hook_type prev_explain_per_plan_hook;
+static explain_validate_options_hook_type prev_explain_validate_options_hook;
+
/*
* GUC parameters
*/
@@ -566,6 +574,61 @@ process_pgfdw_appname(const char *appname)
return buf.data;
}
+/*
+ * Get the PgFdwExplainState structure from an ExplainState; if there is
+ * none, create one, attach it to the ExplainState, and return it.
+ */
+static PgFdwExplainState *
+pgfdw_ensure_options(ExplainState *es)
+{
+ PgFdwExplainState *pgfdw_explain_state;
+
+ pgfdw_explain_state = GetExplainExtensionState(es, GetExplainExtensionId("postgres_fdw"));
+
+ if (pgfdw_explain_state == NULL)
+ {
+ pgfdw_explain_state = palloc0(sizeof(PgFdwExplainState));
+ SetExplainExtensionState(es, GetExplainExtensionId("postgres_fdw"), pgfdw_explain_state);
+ pgfdw_explain_state->all_remote_plans = NIL;
+ }
+
+ return pgfdw_explain_state;
+}
+
+/*
+ * Parse handler for EXPLAIN (REMOTE_PLANS).
+ */
+static void
+pgfdw_remote_plans_apply(ExplainState *es, DefElem *opt, ParseState *pstate)
+{
+ PgFdwExplainState *options = pgfdw_ensure_options(es);
+
+ options->remote_plans = defGetBoolean(opt);
+}
+
+static void
+postgresExplainValidateOptions(ExplainState *es, List *options, ParseState *pstate)
+{
+ ListCell *lc;
+ PgFdwExplainState *pgfdw_explain_state = NULL;
+
+ foreach(lc, options)
+ {
+ DefElem *opt = (DefElem *) lfirst(lc);
+
+ if (strcmp(opt->defname, "remote_plans") == 0)
+ {
+ if (defGetBoolean(opt) && es->analyze)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("EXPLAIN options REMOTE_PLANS and ANALYZE cannot be used together"));
+
+ pgfdw_explain_state = pgfdw_ensure_options(es);
+ pgfdw_explain_state->options = options;
+ }
+ }
+}
+
/*
* Module load callback
*/
@@ -592,4 +655,16 @@ _PG_init(void)
NULL);
MarkGUCPrefixReserved("postgres_fdw");
+
+ RegisterExtensionExplainOption("remote_plans", pgfdw_remote_plans_apply,
+ GUCCheckBooleanExplainOption);
+
+ /* per node EXPLAIN hook */
+ prev_explain_per_node_hook = explain_per_node_hook;
+ explain_per_node_hook = postgresExplainPerNode;
+ prev_explain_per_plan_hook = explain_per_plan_hook;
+ explain_per_plan_hook = postgresExplainPerPlan;
+ prev_explain_validate_options_hook = explain_validate_options_hook;
+ explain_validate_options_hook = postgresExplainValidateOptions;
+
}
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 6fa45773c30..af30222e60a 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -137,6 +137,19 @@ enum FdwDirectModifyPrivateIndex
FdwDirectModifyPrivateSetProcessed,
};
+/*
+ * Track the extension id in the backend.
+ */
+static int extension_id = -1;
+
+static int
+get_extension_id(void)
+{
+ if (extension_id == -1)
+ extension_id = GetExplainExtensionId("postgres_fdw");
+ return extension_id;
+}
+
/*
* Execution state of a foreign scan using postgres_fdw.
*/
@@ -3038,6 +3051,105 @@ postgresEndDirectModify(ForeignScanState *node)
/* MemoryContext will be deleted automatically. */
}
+static void
+postgresExplainStatement(int plan_node_id,
+ ExplainState *es,
+ PgFdwExplainState *pgfdw_explain_state,
+ PGconn *conn,
+ char *sql)
+{
+ PGresult *volatile res = NULL;
+ StringInfoData explain_sql;
+ int remote_version = PQserverVersion(conn);
+
+ /*
+ * GENERIC_PLAN required because deparsed SQL may contain $n placeholders;
+ * only available from PG 16.
+ */
+ if (remote_version < 160000)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("EXPLAIN option REMOTE_PLANS requires a remote server version of 16 or later"),
+ errdetail("The remote server version is %d.", remote_version));
+
+ PG_TRY();
+ {
+ int numrows,
+ i;
+ PgFdwExplainRemotePlans *explain = (PgFdwExplainRemotePlans *) palloc(sizeof(PgFdwExplainRemotePlans));
+ ListCell *lc;
+
+ initStringInfo(&explain_sql);
+ initStringInfo(&explain->explain_plan);
+
+ /* Forward user-specified options as-is; force GENERIC_PLAN on. */
+ appendStringInfoString(&explain_sql, "EXPLAIN (GENERIC_PLAN TRUE");
+
+ foreach(lc, pgfdw_explain_state->options)
+ {
+ DefElem *opt = (DefElem *) lfirst(lc);
+
+ if (strcmp(opt->defname, "remote_plans") == 0 ||
+ strcmp(opt->defname, "generic_plan") == 0)
+ continue;
+
+ if (opt->arg == NULL)
+ appendStringInfo(&explain_sql, ", %s", opt->defname);
+ else
+ appendStringInfo(&explain_sql, ", %s %s", opt->defname,
+ defGetString(opt));
+ }
+
+ appendStringInfo(&explain_sql, ") %s", sql);
+
+ /* Run the query and collect the remote plan */
+ res = pgfdw_exec_query(conn, explain_sql.data, NULL);
+ if (PQresultStatus(res) != PGRES_TUPLES_OK)
+ pgfdw_report_error(res, conn, explain_sql.data);
+
+ numrows = PQntuples(res);
+
+ for (i = 0; i < numrows; i++)
+ appendStringInfo(&explain->explain_plan, "%s\n", PQgetvalue(res, i, 0));
+
+ if (explain->explain_plan.len > 0 && explain->explain_plan.data[explain->explain_plan.len - 1] == '\n')
+ explain->explain_plan.data[--explain->explain_plan.len] = '\0';
+
+ explain->plan_node_id = plan_node_id;
+ pgfdw_explain_state->all_remote_plans = lappend(pgfdw_explain_state->all_remote_plans, explain);
+ }
+ PG_FINALLY();
+ {
+ if (res)
+ PQclear(res);
+
+ if (explain_sql.data)
+ pfree(explain_sql.data);
+ }
+ PG_END_TRY();
+}
+
+/*
+ * explain_remote_query
+ * Helper function to get connection and execute remote EXPLAIN
+ */
+static void
+explain_remote_query(int plan_node_id, ExplainState *es,
+ PgFdwExplainState *pgfdw_explain_state,
+ Oid foreign_table_oid, char *sql)
+{
+ UserMapping *user;
+ PGconn *conn;
+ ForeignTable *table;
+
+ table = GetForeignTable(foreign_table_oid);
+ user = GetUserMapping(GetUserId(), table->serverid);
+ conn = GetConnection(user, false, NULL);
+
+ postgresExplainStatement(plan_node_id, es, pgfdw_explain_state, conn, sql);
+ ReleaseConnection(conn);
+}
+
/*
* postgresExplainForeignScan
* Produce extra output for EXPLAIN of a ForeignScan on a foreign table
@@ -3047,6 +3159,12 @@ postgresExplainForeignScan(ForeignScanState *node, ExplainState *es)
{
ForeignScan *plan = castNode(ForeignScan, node->ss.ps.plan);
List *fdw_private = plan->fdw_private;
+ PgFdwExplainState *pgfdw_explain_state;
+ char *sql;
+ Oid foreign_table_oid = InvalidOid;
+
+ if (node->ss.ss_currentRelation)
+ foreign_table_oid = RelationGetRelid(node->ss.ss_currentRelation);
/*
* Identify foreign scans that are really joins or upper relations. The
@@ -3108,6 +3226,13 @@ postgresExplainForeignScan(ForeignScanState *node, ExplainState *es)
Assert(rte->rtekind == RTE_RELATION);
/* This logic should agree with explain.c's ExplainTargetRel */
relname = get_rel_name(rte->relid);
+
+ /*
+ * Save first table OID for getting server connection
+ */
+ if (!OidIsValid(foreign_table_oid))
+ foreign_table_oid = rte->relid;
+
if (es->verbose)
{
char *namespace;
@@ -3133,16 +3258,25 @@ postgresExplainForeignScan(ForeignScanState *node, ExplainState *es)
ExplainPropertyText("Relations", relations.data, es);
}
+ sql = strVal(list_nth(fdw_private, FdwScanPrivateSelectSql));
+
/*
* Add remote query, when VERBOSE option is specified.
*/
if (es->verbose)
- {
- char *sql;
-
- sql = strVal(list_nth(fdw_private, FdwScanPrivateSelectSql));
ExplainPropertyText("Remote SQL", sql, es);
- }
+
+ pgfdw_explain_state = GetExplainExtensionState(es, get_extension_id());
+
+ /* If we don't have a foreign table oid by now, something went wrong */
+ Assert(foreign_table_oid);
+
+ if (pgfdw_explain_state && pgfdw_explain_state->remote_plans)
+ explain_remote_query(node->ss.ps.plan->plan_node_id,
+ es,
+ pgfdw_explain_state,
+ foreign_table_oid,
+ sql);
}
/*
@@ -3156,11 +3290,12 @@ postgresExplainForeignModify(ModifyTableState *mtstate,
int subplan_index,
ExplainState *es)
{
+ char *sql = strVal(list_nth(fdw_private,
+ FdwModifyPrivateUpdateSql));
+ PgFdwExplainState *pgfdw_explain_state;
+
if (es->verbose)
{
- char *sql = strVal(list_nth(fdw_private,
- FdwModifyPrivateUpdateSql));
-
ExplainPropertyText("Remote SQL", sql, es);
/*
@@ -3170,6 +3305,14 @@ postgresExplainForeignModify(ModifyTableState *mtstate,
if (rinfo->ri_BatchSize > 0)
ExplainPropertyInteger("Batch Size", NULL, rinfo->ri_BatchSize, es);
}
+
+ pgfdw_explain_state = GetExplainExtensionState(es, get_extension_id());
+ if (pgfdw_explain_state && pgfdw_explain_state->remote_plans)
+ explain_remote_query(mtstate->ps.plan->plan_node_id,
+ es,
+ pgfdw_explain_state,
+ rinfo->ri_RelationDesc->rd_rel->oid,
+ sql);
}
/*
@@ -3182,13 +3325,22 @@ postgresExplainDirectModify(ForeignScanState *node, ExplainState *es)
{
List *fdw_private;
char *sql;
+ PgFdwExplainState *pgfdw_explain_state;
+
+ fdw_private = ((ForeignScan *) node->ss.ps.plan)->fdw_private;
+ sql = strVal(list_nth(fdw_private, FdwDirectModifyPrivateUpdateSql));
if (es->verbose)
- {
- fdw_private = ((ForeignScan *) node->ss.ps.plan)->fdw_private;
- sql = strVal(list_nth(fdw_private, FdwDirectModifyPrivateUpdateSql));
ExplainPropertyText("Remote SQL", sql, es);
- }
+
+ pgfdw_explain_state = GetExplainExtensionState(es, get_extension_id());
+
+ if (pgfdw_explain_state && pgfdw_explain_state->remote_plans)
+ explain_remote_query(node->ss.ps.plan->plan_node_id,
+ es,
+ pgfdw_explain_state,
+ RelationGetRelid(node->ss.ss_currentRelation),
+ sql);
}
/*
@@ -8835,3 +8987,95 @@ get_batch_size_option(Relation rel)
return batch_size;
}
+
+void
+postgresExplainPerNode(PlanState *planstate, List *ancestors,
+ const char *relationship, const char *plan_name,
+ ExplainState *es)
+{
+ PgFdwExplainState *pgfdw_explain_state;
+
+ pgfdw_explain_state = GetExplainExtensionState(es, get_extension_id());
+
+ if (pgfdw_explain_state == NULL ||
+ !pgfdw_explain_state->remote_plans)
+ return;
+
+ if (pgfdw_explain_state && pgfdw_explain_state->remote_plans)
+ ExplainPropertyInteger("Plan Node ID", NULL, planstate->plan->plan_node_id, es);
+}
+
+static void
+pgfdwFormatRemotePlan(PgFdwExplainRemotePlans *explain,
+ ExplainState *es,
+ int plan_node_id)
+{
+ char *token;
+ StringInfoData remote_plan_name;
+
+ initStringInfo(&remote_plan_name);
+ appendStringInfo(&remote_plan_name, "Plan Node ID %d", plan_node_id);
+
+ ExplainOpenGroup(remote_plan_name.data, remote_plan_name.data, false, es);
+
+ if (es->format == EXPLAIN_FORMAT_TEXT)
+ {
+ appendStringInfo(es->str, "Plan Node ID %d:", plan_node_id);
+ appendStringInfoString(es->str, "\n");
+ }
+
+ while ((token = strsep(&explain->explain_plan.data, "\n")) != NULL)
+ {
+ if (es->format == EXPLAIN_FORMAT_JSON ||
+ es->format == EXPLAIN_FORMAT_YAML)
+ appendStringInfoString(es->str, "\n");
+
+ appendStringInfoSpaces(es->str, (es->indent == 0) ? 2 : es->indent * 2);
+ appendStringInfoString(es->str, token);
+
+ if (es->format == EXPLAIN_FORMAT_XML ||
+ es->format == EXPLAIN_FORMAT_TEXT)
+ appendStringInfoString(es->str, "\n");
+ }
+
+ ExplainCloseGroup(remote_plan_name.data, remote_plan_name.data, false, es);
+ pfree(remote_plan_name.data);
+}
+
+void
+postgresExplainPerPlan(PlannedStmt *plannedstmt,
+ IntoClause *into,
+ ExplainState *es,
+ const char *queryString,
+ ParamListInfo params,
+ QueryEnvironment *queryEnv)
+{
+ ListCell *lc;
+ PgFdwExplainState *pgfdw_explain_state;
+
+ pgfdw_explain_state = GetExplainExtensionState(es, get_extension_id());
+
+ if (pgfdw_explain_state == NULL ||
+ pgfdw_explain_state->all_remote_plans == NIL ||
+ !pgfdw_explain_state->remote_plans)
+ return;
+
+ ExplainOpenGroup("Remote Plans", "Remote Plans", true, es);
+ if (es->format == EXPLAIN_FORMAT_TEXT)
+ {
+ appendStringInfo(es->str, "Remote Plans:\n");
+ appendStringInfo(es->str, "-------------\n");
+ }
+
+ /* Process every remote plan captured */
+ foreach(lc, pgfdw_explain_state->all_remote_plans)
+ {
+ PgFdwExplainRemotePlans *explain = (PgFdwExplainRemotePlans *) lfirst(lc);
+
+ pgfdwFormatRemotePlan(explain,
+ es,
+ explain->plan_node_id);
+ }
+
+ ExplainCloseGroup("Remote Plans", "Remote Plans", true, es);
+}
diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h
index a2bb1ff352c..6ede7a4e41f 100644
--- a/contrib/postgres_fdw/postgres_fdw.h
+++ b/contrib/postgres_fdw/postgres_fdw.h
@@ -13,6 +13,7 @@
#ifndef POSTGRES_FDW_H
#define POSTGRES_FDW_H
+#include "commands/explain_state.h"
#include "foreign/foreign.h"
#include "lib/stringinfo.h"
#include "libpq/libpq-be-fe.h"
@@ -151,6 +152,22 @@ typedef enum PgFdwSamplingMethod
ANALYZE_SAMPLE_BERNOULLI, /* TABLESAMPLE bernoulli */
} PgFdwSamplingMethod;
+typedef struct PgFdwExplainRemotePlans
+{
+ int plan_node_id;
+ StringInfoData explain_plan;
+
+} PgFdwExplainRemotePlans;
+
+typedef struct PgFdwExplainState
+{
+ List *all_remote_plans;
+
+ /* EXPLAIN options */
+ bool remote_plans;
+ List *options; /* raw user DefElem list, for forwarding */
+} PgFdwExplainState;
+
/* in postgres_fdw.c */
extern int set_transmission_modes(void);
extern void reset_transmission_modes(int nestlevel);
@@ -178,6 +195,16 @@ extern int ExtractConnectionOptions(List *defelems,
extern List *ExtractExtensionList(const char *extensionsString,
bool warnOnMissing);
extern char *process_pgfdw_appname(const char *appname);
+extern void postgresExplainPerNode(PlanState *planstate, List *ancestors,
+ const char *relationship,
+ const char *plan_name,
+ ExplainState *es);
+extern void postgresExplainPerPlan(PlannedStmt *plannedstmt,
+ IntoClause *into,
+ ExplainState *es,
+ const char *queryString,
+ ParamListInfo params,
+ QueryEnvironment *queryEnv);
extern char *pgfdw_application_name;
/* in deparse.c */
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 8162c5496bf..9e36697ade1 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -311,6 +311,11 @@ SELECT * FROM ft1 t1 WHERE t1.c3 = (SELECT MAX(c3) FROM ft2 t2) ORDER BY c1;
WITH t1 AS (SELECT * FROM ft1 WHERE c1 <= 10) SELECT t2.c1, t2.c2, t2.c3, t2.c4 FROM t1, ft2 t2 WHERE t1.c1 = t2.c1 ORDER BY t1.c1;
-- fixed values
SELECT 'fixed', NULL FROM ft1 t1 WHERE c1 = 1;
+-- with WHERE clause and remote_plans with different formats
+EXPLAIN (REMOTE_PLANS, FORMAT YAML, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+EXPLAIN (REMOTE_PLANS TRUE, FORMAT XML, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+EXPLAIN (REMOTE_PLANS, FORMAT JSON, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+EXPLAIN (REMOTE_PLANS TRUE, FORMAT TEXT, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
-- Test forcing the remote server to produce sorted data for a merge join.
SET enable_hashjoin TO false;
SET enable_nestloop TO false;
@@ -338,6 +343,12 @@ SELECT t1."C 1", t2.c1, t3.c1 FROM "S 1"."T 1" t1 left join ft1 t2 full join ft2
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1."C 1", t2.c1, t3.c1 FROM "S 1"."T 1" t1 full join ft1 t2 full join ft2 t3 on (t2.c1 = t3.c1) on (t3.c1 = t1."C 1") OFFSET 100 LIMIT 10;
SELECT t1."C 1", t2.c1, t3.c1 FROM "S 1"."T 1" t1 full join ft1 t2 full join ft2 t3 on (t2.c1 = t3.c1) on (t3.c1 = t1."C 1") OFFSET 100 LIMIT 10;
+
+-- Join push-down test
+-- Ensure join conditions are pushed down to the foreign server
+EXPLAIN (REMOTE_PLANS, VERBOSE, COSTS OFF)
+ SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) WHERE t1.c2 = 10;
+
RESET enable_hashjoin;
RESET enable_nestloop;
@@ -379,6 +390,11 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c8 = 'foo'; -- can't be
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM "S 1"."T 1" a, ft2 b WHERE a."C 1" = 47 AND b.c1 = a.c2;
SELECT * FROM "S 1"."T 1" a, ft2 b WHERE a."C 1" = 47 AND b.c1 = a.c2;
+-- REMOTE_PLANS over a parameterized foreign scan: the deparsed remote SQL
+-- carries a $1 placeholder, which only plans on the remote side because
+-- REMOTE_PLANS forces GENERIC_PLAN.
+EXPLAIN (REMOTE_PLANS, VERBOSE, COSTS OFF)
+ SELECT * FROM "S 1"."T 1" a, ft2 b WHERE a."C 1" = 47 AND b.c1 = a.c2;
-- check both safe and unsafe join conditions
EXPLAIN (VERBOSE, COSTS OFF)
@@ -1527,6 +1543,33 @@ SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
ORDER BY ft1.c1 LIMIT 5;
+-- EXPLAIN remote_plans
+EXPLAIN (remote_plans, format text, costs off, analyze)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+EXPLAIN (remote_plans, format text, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+EXPLAIN (remote_plans, format xml, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+EXPLAIN (remote_plans, format json, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+EXPLAIN (remote_plans, format yaml, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+
-- ===================================================================
-- test writable foreign table stuff
-- ===================================================================
@@ -1610,6 +1653,9 @@ DELETE FROM fpo_part_parent
FOR PORTION OF c4 FROM '2024-06-01' TO '2024-06-15' WHERE c2 = 1; -- okay
SELECT c1, c2, c3, c4 FROM fpo_part_local ORDER BY c4;
DROP TABLE fpo_part_parent;
+-- test write on foreign tables with remote_plans
+EXPLAIN (remote_plans, verbose, costs off)
+UPDATE ft2 SET c2 = c2 + 300 WHERE c1 % 10 = 3;
-- Test UPDATE/DELETE with RETURNING on a three-table join
INSERT INTO ft2 (c1,c2,c3)
@@ -4252,6 +4298,9 @@ INSERT INTO insert_tbl (SELECT * FROM local_tbl UNION ALL SELECT * FROM remote_t
SELECT * FROM insert_tbl ORDER BY a;
+EXPLAIN (REMOTE_PLANS, VERBOSE, COSTS OFF)
+INSERT INTO insert_tbl (SELECT * FROM local_tbl UNION ALL SELECT * FROM remote_tbl);
+
-- Check with direct modify
EXPLAIN (VERBOSE, COSTS OFF)
WITH t AS (UPDATE remote_tbl SET c = c || c RETURNING *)
diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index b9e1b04463e..b457611db04 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -1197,6 +1197,93 @@ CREATE SUBSCRIPTION my_subscription SERVER subscription_server PUBLICATION testp
The query that is actually sent to the remote server for execution can
be examined using <command>EXPLAIN VERBOSE</command>.
</para>
+
+ <para>
+ In addition to the remote query itself, the plan that the remote server
+ chooses for that query can be examined with the
+ <literal>REMOTE_PLANS</literal> option of <command>EXPLAIN</command>.
+ For every foreign scan or foreign modification in the local plan,
+ <filename>postgres_fdw</filename> runs <command>EXPLAIN</command> on the
+ remote server for the corresponding remote query and prints the result in a
+ <literal>Remote Plans</literal> section, keyed by the plan node id of the
+ local node. For example:
+<programlisting>
+EXPLAIN (VERBOSE, REMOTE_PLANS) SELECT * FROM foreign_tbl WHERE id = 42;
+ QUERY PLAN
+-----------------------------------------------------------
+ Foreign Scan on public.foreign_tbl
+ Output: id, val
+ Remote SQL: SELECT id, val FROM public.tbl WHERE ((id = 42))
+ Plan Node ID: 0
+ Remote Plans:
+ -------------
+ Plan Node ID 0:
+ Index Scan using tbl_pkey on public.tbl
+ Output: id, val
+ Index Cond: (tbl.id = 42)
+</programlisting>
+ When the local plan uses a parameterized foreign scan (for example, as
+ the inner side of a nested loop join), the remote SQL contains parameter
+ placeholders. The remote plan is a generic plan in this case:
+<programlisting>
+EXPLAIN (VERBOSE, REMOTE_PLANS, COSTS OFF)
+ SELECT * FROM local_tbl a, foreign_tbl b WHERE a.id = 1 AND b.id = a.val;
+ QUERY PLAN
+-----------------------------------------------------------
+ Nested Loop
+ Output: a.id, a.val, b.id, b.val
+ -> Seq Scan on public.local_tbl a
+ Output: a.id, a.val
+ Filter: (a.id = 1)
+ -> Foreign Scan on public.foreign_tbl b
+ Output: b.id, b.val
+ Remote SQL: SELECT id, val FROM public.tbl WHERE ((id = $1::integer))
+ Plan Node ID: 1
+ Remote Plans:
+ -------------
+ Plan Node ID 1:
+ Index Scan using tbl_pkey on public.tbl
+ Output: id, val
+ Index Cond: (tbl.id = $1)
+</programlisting>
+ </para>
+
+ <para>
+ The <literal>REMOTE_PLANS</literal> option is registered when
+ <filename>postgres_fdw</filename> is loaded, so the module must be loaded
+ (for example with <command>CREATE EXTENSION postgres_fdw</command>) before
+ the option can be used. The following restrictions apply:
+ <itemizedlist>
+ <listitem>
+ <para>
+ It cannot be combined with <literal>ANALYZE</literal>; doing so raises an
+ error. The remote query is only planned, never executed, so the foreign
+ server is not affected and no remote rows are fetched.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ The remote server must be running <productname>PostgreSQL</productname>
+ 16 or later, because <literal>REMOTE_PLANS</literal> relies on the
+ <literal>GENERIC_PLAN</literal> option of <command>EXPLAIN</command>,
+ which was introduced in that release. As shown in the parameterized
+ example above, the plan shown by <literal>REMOTE_PLANS</literal> is
+ always a generic plan, which may differ from the plan the remote
+ server actually uses at execution time when specific parameter values
+ are available.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ All <command>EXPLAIN</command> options specified by the user (other than
+ <literal>REMOTE_PLANS</literal> itself and
+ <literal>GENERIC_PLAN</literal>) are forwarded to the remote server.
+ If the remote server is running an older version that does not recognize
+ a forwarded option, it will report an error.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
</sect2>
<sect2 id="postgres-fdw-remote-query-execution-environment">
diff --git a/src/include/commands/explain_state.h b/src/include/commands/explain_state.h
index 97bc7ed49f6..7e4dc6bca9c 100644
--- a/src/include/commands/explain_state.h
+++ b/src/include/commands/explain_state.h
@@ -45,6 +45,7 @@ typedef struct ExplainWorkersState
typedef struct ExplainState
{
StringInfo str; /* output buffer */
+
/* options */
bool verbose; /* be verbose */
bool analyze; /* print actual times */
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 117e7379f10..9f5ee7c8bfb 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2305,6 +2305,8 @@ PgChecksumMode
PgFdwAnalyzeState
PgFdwConnState
PgFdwDirectModifyState
+PgFdwExplainRemotePlans
+PgFdwExplainState
PgFdwModifyState
PgFdwOption
PgFdwPathExtraData
--
2.50.1 (Apple Git-155)
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: explain plans for foreign servers
@ 2026-07-04 17:07 dinesh salve <[email protected]>
parent: Sami Imseih <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: dinesh salve @ 2026-07-04 17:07 UTC (permalink / raw)
To: Sami Imseih <[email protected]>; +Cc: Tom Lane <[email protected]>; pgsql-hackers; Jeff Davis <[email protected]>
Just took a look and it looks great, thanks Sami for trimming down the
documentation and avoiding maintenance of version and their options. I have
moved the patch to the current commitfest PG20-2.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: explain plans for foreign servers
@ 2026-07-08 18:12 dinesh salve <[email protected]>
parent: dinesh salve <[email protected]>
0 siblings, 0 replies; 11+ messages in thread
From: dinesh salve @ 2026-07-08 18:12 UTC (permalink / raw)
To: Sami Imseih <[email protected]>; +Cc: Tom Lane <[email protected]>; pgsql-hackers; Jeff Davis <[email protected]>
As discussed offline, also added a regression test that joins two foreign
tables living on different foreign servers (loopback and loopback2), which
exercises REMOTE_PLANS over multiple fdw connections.
>
Attachments:
[application/octet-stream] v4-0001-postgres_fdw-show-remote-EXPLAIN-plans-via-REMOTE.patch (70.9K, ../../CAP+B4TANKj+1h1iJc86vj2XFs075ghXNkjzruRAL6G1yZLX3GQ@mail.gmail.com/3-v4-0001-postgres_fdw-show-remote-EXPLAIN-plans-via-REMOTE.patch)
download | inline diff:
From ff4c2b1a4e5828c9dd89f7b7a6413fdafc530b8e Mon Sep 17 00:00:00 2001
From: Dinesh Salve <[email protected]>
Date: Wed, 8 Jul 2026 23:02:34 +0530
Subject: [PATCH v4] postgres_fdw: show remote EXPLAIN plans via REMOTE_PLANS
option
Add a REMOTE_PLANS option to EXPLAIN that, for every foreign scan or
foreign modification, runs EXPLAIN on the remote server for the
corresponding remote query and prints the result in a "Remote Plans"
section keyed by the local plan node id.
The remote query that postgres_fdw sends often contains $n parameter
placeholders (parameterized foreign scans and joins, subquery outputs,
and INSERT/UPDATE/DELETE), which a plain remote EXPLAIN cannot plan and
fails with "there is no parameter $1". REMOTE_PLANS therefore forces
GENERIC_PLAN on the remote EXPLAIN, which plans such statements without
bound values. GENERIC_PLAN is only available from PostgreSQL 16, so
REMOTE_PLANS errors out on older remote servers.
REMOTE_PLANS cannot be combined with ANALYZE. All user-specified EXPLAIN
options (other than REMOTE_PLANS and GENERIC_PLAN) are forwarded to the
remote server as-is. If the remote server does not recognize a forwarded
option, it reports an error.
The option is registered when postgres_fdw is loaded.
---
.../postgres_fdw/expected/postgres_fdw.out | 658 ++++++++++++++++++
contrib/postgres_fdw/option.c | 75 ++
contrib/postgres_fdw/postgres_fdw.c | 268 ++++++-
contrib/postgres_fdw/postgres_fdw.h | 27 +
contrib/postgres_fdw/sql/postgres_fdw.sql | 56 ++
doc/src/sgml/postgres-fdw.sgml | 87 +++
src/include/commands/explain_state.h | 1 +
src/tools/pgindent/typedefs.list | 2 +
8 files changed, 1162 insertions(+), 12 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 5ebae1c..37709e9 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -471,6 +471,173 @@ SELECT 'fixed', NULL FROM ft1 t1 WHERE c1 = 1;
fixed |
(1 row)
+-- with WHERE clause and remote_plans with different formats
+EXPLAIN (REMOTE_PLANS, FORMAT YAML, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------
+ - Plan: +
+ Node Type: "Foreign Scan" +
+ Operation: "Select" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Relation Name: "ft1" +
+ Schema: "public" +
+ Alias: "t1" +
+ Disabled: false +
+ Output: +
+ - "c1" +
+ - "c2" +
+ - "c3" +
+ - "c4" +
+ - "c5" +
+ - "c6" +
+ - "c7" +
+ - "c8" +
+ Remote SQL: "SELECT \"C 1\", c2, c3, c4, c5, c6, c7, c8 FROM \"S 1\".\"T 1\" WHERE ((\"C 1\" = 101))"+
+ Plan Node ID: 0 +
+ Remote Plans: +
+ Plan Node ID 0: +
+ - Plan: +
+ Node Type: "Index Scan" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Scan Direction: "Forward" +
+ Index Name: "t1_pkey" +
+ Relation Name: "T 1" +
+ Schema: "S 1" +
+ Alias: "T 1" +
+ Disabled: false +
+ Output: +
+ - "\"C 1\"" +
+ - "c2" +
+ - "c3" +
+ - "c4" +
+ - "c5" +
+ - "c6" +
+ - "c7" +
+ - "c8" +
+ Index Cond: "(\"T 1\".\"C 1\" = 101)"
+(1 row)
+
+EXPLAIN (REMOTE_PLANS TRUE, FORMAT XML, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------
+ <explain xmlns="http://www.postgresql.org/2009/explain"> +
+ <Query> +
+ <Plan> +
+ <Node-Type>Foreign Scan</Node-Type> +
+ <Operation>Select</Operation> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Relation-Name>ft1</Relation-Name> +
+ <Schema>public</Schema> +
+ <Alias>t1</Alias> +
+ <Disabled>false</Disabled> +
+ <Output> +
+ <Item>c1</Item> +
+ <Item>c2</Item> +
+ <Item>c3</Item> +
+ <Item>c4</Item> +
+ <Item>c5</Item> +
+ <Item>c6</Item> +
+ <Item>c7</Item> +
+ <Item>c8</Item> +
+ </Output> +
+ <Remote-SQL>SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 101))</Remote-SQL>+
+ <Plan-Node-ID>0</Plan-Node-ID> +
+ </Plan> +
+ <Remote-Plans> +
+ <Plan-Node-ID-0> +
+ <explain xmlns="http://www.postgresql.org/2009/explain"> +
+ <Query> +
+ <Plan> +
+ <Node-Type>Index Scan</Node-Type> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Scan-Direction>Forward</Scan-Direction> +
+ <Index-Name>t1_pkey</Index-Name> +
+ <Relation-Name>T 1</Relation-Name> +
+ <Schema>S 1</Schema> +
+ <Alias>T 1</Alias> +
+ <Disabled>false</Disabled> +
+ <Output> +
+ <Item>"C 1"</Item> +
+ <Item>c2</Item> +
+ <Item>c3</Item> +
+ <Item>c4</Item> +
+ <Item>c5</Item> +
+ <Item>c6</Item> +
+ <Item>c7</Item> +
+ <Item>c8</Item> +
+ </Output> +
+ <Index-Cond>("T 1"."C 1" = 101)</Index-Cond> +
+ </Plan> +
+ </Query> +
+ </explain> +
+ </Plan-Node-ID-0> +
+ </Remote-Plans> +
+ </Query> +
+ </explain>
+(1 row)
+
+EXPLAIN (REMOTE_PLANS, FORMAT JSON, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Foreign Scan", +
+ "Operation": "Select", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Relation Name": "ft1", +
+ "Schema": "public", +
+ "Alias": "t1", +
+ "Disabled": false, +
+ "Output": ["c1", "c2", "c3", "c4", "c5", "c6", "c7", "c8"], +
+ "Remote SQL": "SELECT \"C 1\", c2, c3, c4, c5, c6, c7, c8 FROM \"S 1\".\"T 1\" WHERE ((\"C 1\" = 101))",+
+ "Plan Node ID": 0 +
+ }, +
+ "Remote Plans": { +
+ "Plan Node ID 0": [ +
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Index Scan", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Scan Direction": "Forward", +
+ "Index Name": "t1_pkey", +
+ "Relation Name": "T 1", +
+ "Schema": "S 1", +
+ "Alias": "T 1", +
+ "Disabled": false, +
+ "Output": ["\"C 1\"", "c2", "c3", "c4", "c5", "c6", "c7", "c8"], +
+ "Index Cond": "(\"T 1\".\"C 1\" = 101)" +
+ } +
+ } +
+ ] +
+ ] +
+ } +
+ } +
+ ]
+(1 row)
+
+EXPLAIN (REMOTE_PLANS TRUE, FORMAT TEXT, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------
+ Foreign Scan on public.ft1 t1
+ Output: c1, c2, c3, c4, c5, c6, c7, c8
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 101))
+ Plan Node ID: 0
+ Remote Plans:
+ -------------
+ Plan Node ID 0:
+ Index Scan using t1_pkey on "S 1"."T 1"
+ Output: "C 1", c2, c3, c4, c5, c6, c7, c8
+ Index Cond: ("T 1"."C 1" = 101)
+(10 rows)
+
-- Test forcing the remote server to produce sorted data for a merge join.
SET enable_hashjoin TO false;
SET enable_nestloop TO false;
@@ -646,6 +813,57 @@ SELECT t1."C 1", t2.c1, t3.c1 FROM "S 1"."T 1" t1 full join ft1 t2 full join ft2
110 | 110 | 110
(10 rows)
+-- Join push-down test
+-- Ensure join conditions are pushed down to the foreign server
+EXPLAIN (REMOTE_PLANS, VERBOSE, COSTS OFF)
+ SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) WHERE t1.c2 = 10;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+ Output: t1.c1, t2.c1
+ Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
+ Remote SQL: SELECT r1."C 1", r2."C 1" FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r2."C 1" = r1."C 1")) AND ((r1.c2 = 10))))
+ Plan Node ID: 0
+ Remote Plans:
+ -------------
+ Plan Node ID 0:
+ Seq Scan on "S 1"."T 1" r2
+ Output: r2."C 1", r2."C 1"
+ Filter: (r2.c2 = 10)
+(11 rows)
+
+-- Tables on multiple foreign connections: ft5 lives on server loopback and
+-- ft6 on server loopback2, so the join cannot be pushed down as a single
+-- foreign join. Each side is scanned over its own connection, so REMOTE_PLANS
+-- must collect and print one remote plan per connection.
+EXPLAIN (REMOTE_PLANS, VERBOSE, COSTS OFF)
+ SELECT t1.c1, t2.c1 FROM ft5 t1 JOIN ft6 t2 ON (t1.c1 = t2.c1) WHERE t1.c1 = 10;
+ QUERY PLAN
+------------------------------------------------------------------
+ Nested Loop
+ Disabled: true
+ Output: t1.c1, t2.c1
+ Plan Node ID: 0
+ -> Foreign Scan on public.ft5 t1
+ Output: t1.c1, t1.c2, t1.c3
+ Remote SQL: SELECT c1 FROM "S 1"."T 4" WHERE ((c1 = 10))
+ Plan Node ID: 1
+ -> Foreign Scan on public.ft6 t2
+ Output: t2.c1, t2.c2, t2.c3
+ Remote SQL: SELECT c1 FROM "S 1"."T 4" WHERE ((c1 = 10))
+ Plan Node ID: 2
+ Remote Plans:
+ -------------
+ Plan Node ID 1:
+ Seq Scan on "S 1"."T 4"
+ Output: c1
+ Filter: ("T 4".c1 = 10)
+ Plan Node ID 2:
+ Seq Scan on "S 1"."T 4"
+ Output: c1
+ Filter: ("T 4".c1 = 10)
+(22 rows)
+
RESET enable_hashjoin;
RESET enable_nestloop;
-- Test executing assertion in estimate_path_cost_size() that makes sure that
@@ -790,6 +1008,32 @@ SELECT * FROM "S 1"."T 1" a, ft2 b WHERE a."C 1" = 47 AND b.c1 = a.c2;
47 | 7 | 00047 | Tue Feb 17 00:00:00 1970 PST | Tue Feb 17 00:00:00 1970 | 7 | 7 | foo | 7 | 7 | 00007 | Thu Jan 08 00:00:00 1970 PST | Thu Jan 08 00:00:00 1970 | 7 | 7 | foo
(1 row)
+-- REMOTE_PLANS over a parameterized foreign scan: the deparsed remote SQL
+-- carries a $1 placeholder, which only plans on the remote side because
+-- REMOTE_PLANS forces GENERIC_PLAN.
+EXPLAIN (REMOTE_PLANS, VERBOSE, COSTS OFF)
+ SELECT * FROM "S 1"."T 1" a, ft2 b WHERE a."C 1" = 47 AND b.c1 = a.c2;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------
+ Nested Loop
+ Output: a."C 1", a.c2, a.c3, a.c4, a.c5, a.c6, a.c7, a.c8, b.c1, b.c2, b.c3, b.c4, b.c5, b.c6, b.c7, b.c8
+ Plan Node ID: 0
+ -> Index Scan using t1_pkey on "S 1"."T 1" a
+ Output: a."C 1", a.c2, a.c3, a.c4, a.c5, a.c6, a.c7, a.c8
+ Index Cond: (a."C 1" = 47)
+ Plan Node ID: 1
+ -> Foreign Scan on public.ft2 b
+ Output: b.c1, b.c2, b.c3, b.c4, b.c5, b.c6, b.c7, b.c8
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = $1::integer))
+ Plan Node ID: 2
+ Remote Plans:
+ -------------
+ Plan Node ID 2:
+ Index Scan using t1_pkey on "S 1"."T 1"
+ Output: "C 1", c2, c3, c4, c5, c6, c7, c8
+ Index Cond: ("T 1"."C 1" = $1)
+(17 rows)
+
-- check both safe and unsafe join conditions
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM ft2 a, ft2 b
@@ -5116,6 +5360,373 @@ SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
Remote SQL: SELECT r5."C 1", r6.c1 FROM ("S 1"."T 1" r5 INNER JOIN "S 1"."T 3" r6 ON (((r5."C 1" = r6.c1)))) ORDER BY r5."C 1" ASC NULLS LAST
(13 rows)
+-- EXPLAIN remote_plans
+EXPLAIN (remote_plans, format text, costs off, analyze)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+ERROR: EXPLAIN options REMOTE_PLANS and ANALYZE cannot be used together
+EXPLAIN (remote_plans, format text, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+ QUERY PLAN
+-------------------------------------------------------
+ Limit
+ Plan Node ID: 0
+ -> Merge Semi Join
+ Merge Cond: (ft1.c1 = ft2_1.c1)
+ Plan Node ID: 1
+ -> Foreign Scan
+ Relations: (ft1) INNER JOIN (ft2)
+ Plan Node ID: 2
+ -> Foreign Scan
+ Relations: (ft2 ft2_1) INNER JOIN (ft4)
+ Plan Node ID: 3
+ Remote Plans:
+ -------------
+ Plan Node ID 2:
+ Index Only Scan using t1_pkey on "T 1" r2
+ Plan Node ID 3:
+ Merge Join
+ Merge Cond: (r5."C 1" = r6.c1)
+ -> Index Only Scan using t1_pkey on "T 1" r5
+ -> Sort
+ Sort Key: r6.c1
+ -> Seq Scan on "T 3" r6
+(22 rows)
+
+EXPLAIN (remote_plans, format xml, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+ QUERY PLAN
+------------------------------------------------------------------------
+ <explain xmlns="http://www.postgresql.org/2009/explain"> +
+ <Query> +
+ <Plan> +
+ <Node-Type>Limit</Node-Type> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Disabled>false</Disabled> +
+ <Plan-Node-ID>0</Plan-Node-ID> +
+ <Plans> +
+ <Plan> +
+ <Node-Type>Merge Join</Node-Type> +
+ <Parent-Relationship>Outer</Parent-Relationship> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Join-Type>Semi</Join-Type> +
+ <Disabled>false</Disabled> +
+ <Inner-Unique>false</Inner-Unique> +
+ <Merge-Cond>(ft1.c1 = ft2_1.c1)</Merge-Cond> +
+ <Plan-Node-ID>1</Plan-Node-ID> +
+ <Plans> +
+ <Plan> +
+ <Node-Type>Foreign Scan</Node-Type> +
+ <Operation>Select</Operation> +
+ <Parent-Relationship>Outer</Parent-Relationship> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Disabled>false</Disabled> +
+ <Relations>(ft1) INNER JOIN (ft2)</Relations> +
+ <Plan-Node-ID>2</Plan-Node-ID> +
+ </Plan> +
+ <Plan> +
+ <Node-Type>Foreign Scan</Node-Type> +
+ <Operation>Select</Operation> +
+ <Parent-Relationship>Inner</Parent-Relationship> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Disabled>false</Disabled> +
+ <Relations>(ft2 ft2_1) INNER JOIN (ft4)</Relations> +
+ <Plan-Node-ID>3</Plan-Node-ID> +
+ </Plan> +
+ </Plans> +
+ </Plan> +
+ </Plans> +
+ </Plan> +
+ <Remote-Plans> +
+ <Plan-Node-ID-2> +
+ <explain xmlns="http://www.postgresql.org/2009/explain"> +
+ <Query> +
+ <Plan> +
+ <Node-Type>Index Only Scan</Node-Type> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Scan-Direction>Forward</Scan-Direction> +
+ <Index-Name>t1_pkey</Index-Name> +
+ <Relation-Name>T 1</Relation-Name> +
+ <Alias>r2</Alias> +
+ <Disabled>false</Disabled> +
+ </Plan> +
+ </Query> +
+ </explain> +
+ </Plan-Node-ID-2> +
+ <Plan-Node-ID-3> +
+ <explain xmlns="http://www.postgresql.org/2009/explain"> +
+ <Query> +
+ <Plan> +
+ <Node-Type>Merge Join</Node-Type> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Join-Type>Inner</Join-Type> +
+ <Disabled>false</Disabled> +
+ <Inner-Unique>true</Inner-Unique> +
+ <Merge-Cond>(r5."C 1" = r6.c1)</Merge-Cond> +
+ <Plans> +
+ <Plan> +
+ <Node-Type>Index Only Scan</Node-Type> +
+ <Parent-Relationship>Outer</Parent-Relationship> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Scan-Direction>Forward</Scan-Direction> +
+ <Index-Name>t1_pkey</Index-Name> +
+ <Relation-Name>T 1</Relation-Name> +
+ <Alias>r5</Alias> +
+ <Disabled>false</Disabled> +
+ </Plan> +
+ <Plan> +
+ <Node-Type>Sort</Node-Type> +
+ <Parent-Relationship>Inner</Parent-Relationship> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Disabled>false</Disabled> +
+ <Sort-Key> +
+ <Item>r6.c1</Item> +
+ </Sort-Key> +
+ <Plans> +
+ <Plan> +
+ <Node-Type>Seq Scan</Node-Type> +
+ <Parent-Relationship>Outer</Parent-Relationship>+
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Relation-Name>T 3</Relation-Name> +
+ <Alias>r6</Alias> +
+ <Disabled>false</Disabled> +
+ </Plan> +
+ </Plans> +
+ </Plan> +
+ </Plans> +
+ </Plan> +
+ </Query> +
+ </explain> +
+ </Plan-Node-ID-3> +
+ </Remote-Plans> +
+ </Query> +
+ </explain>
+(1 row)
+
+EXPLAIN (remote_plans, format json, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+ QUERY PLAN
+------------------------------------------------------------
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Limit", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Disabled": false, +
+ "Plan Node ID": 0, +
+ "Plans": [ +
+ { +
+ "Node Type": "Merge Join", +
+ "Parent Relationship": "Outer", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Join Type": "Semi", +
+ "Disabled": false, +
+ "Inner Unique": false, +
+ "Merge Cond": "(ft1.c1 = ft2_1.c1)", +
+ "Plan Node ID": 1, +
+ "Plans": [ +
+ { +
+ "Node Type": "Foreign Scan", +
+ "Operation": "Select", +
+ "Parent Relationship": "Outer", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Disabled": false, +
+ "Relations": "(ft1) INNER JOIN (ft2)", +
+ "Plan Node ID": 2 +
+ }, +
+ { +
+ "Node Type": "Foreign Scan", +
+ "Operation": "Select", +
+ "Parent Relationship": "Inner", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Disabled": false, +
+ "Relations": "(ft2 ft2_1) INNER JOIN (ft4)",+
+ "Plan Node ID": 3 +
+ } +
+ ] +
+ } +
+ ] +
+ }, +
+ "Remote Plans": { +
+ "Plan Node ID 2": [ +
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Index Only Scan", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Scan Direction": "Forward", +
+ "Index Name": "t1_pkey", +
+ "Relation Name": "T 1", +
+ "Alias": "r2", +
+ "Disabled": false +
+ } +
+ } +
+ ] +
+ ], +
+ "Plan Node ID 3": [ +
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Merge Join", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Join Type": "Inner", +
+ "Disabled": false, +
+ "Inner Unique": true, +
+ "Merge Cond": "(r5.\"C 1\" = r6.c1)", +
+ "Plans": [ +
+ { +
+ "Node Type": "Index Only Scan", +
+ "Parent Relationship": "Outer", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Scan Direction": "Forward", +
+ "Index Name": "t1_pkey", +
+ "Relation Name": "T 1", +
+ "Alias": "r5", +
+ "Disabled": false +
+ }, +
+ { +
+ "Node Type": "Sort", +
+ "Parent Relationship": "Inner", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Disabled": false, +
+ "Sort Key": ["r6.c1"], +
+ "Plans": [ +
+ { +
+ "Node Type": "Seq Scan", +
+ "Parent Relationship": "Outer", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Relation Name": "T 3", +
+ "Alias": "r6", +
+ "Disabled": false +
+ } +
+ ] +
+ } +
+ ] +
+ } +
+ } +
+ ] +
+ ] +
+ } +
+ } +
+ ]
+(1 row)
+
+EXPLAIN (remote_plans, format yaml, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+ QUERY PLAN
+-------------------------------------------------------
+ - Plan: +
+ Node Type: "Limit" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Disabled: false +
+ Plan Node ID: 0 +
+ Plans: +
+ - Node Type: "Merge Join" +
+ Parent Relationship: "Outer" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Join Type: "Semi" +
+ Disabled: false +
+ Inner Unique: false +
+ Merge Cond: "(ft1.c1 = ft2_1.c1)" +
+ Plan Node ID: 1 +
+ Plans: +
+ - Node Type: "Foreign Scan" +
+ Operation: "Select" +
+ Parent Relationship: "Outer" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Disabled: false +
+ Relations: "(ft1) INNER JOIN (ft2)" +
+ Plan Node ID: 2 +
+ - Node Type: "Foreign Scan" +
+ Operation: "Select" +
+ Parent Relationship: "Inner" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Disabled: false +
+ Relations: "(ft2 ft2_1) INNER JOIN (ft4)"+
+ Plan Node ID: 3 +
+ Remote Plans: +
+ Plan Node ID 2: +
+ - Plan: +
+ Node Type: "Index Only Scan" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Scan Direction: "Forward" +
+ Index Name: "t1_pkey" +
+ Relation Name: "T 1" +
+ Alias: "r2" +
+ Disabled: false +
+ Plan Node ID 3: +
+ - Plan: +
+ Node Type: "Merge Join" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Join Type: "Inner" +
+ Disabled: false +
+ Inner Unique: true +
+ Merge Cond: "(r5.\"C 1\" = r6.c1)" +
+ Plans: +
+ - Node Type: "Index Only Scan" +
+ Parent Relationship: "Outer" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Scan Direction: "Forward" +
+ Index Name: "t1_pkey" +
+ Relation Name: "T 1" +
+ Alias: "r5" +
+ Disabled: false +
+ - Node Type: "Sort" +
+ Parent Relationship: "Inner" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Disabled: false +
+ Sort Key: +
+ - "r6.c1" +
+ Plans: +
+ - Node Type: "Seq Scan" +
+ Parent Relationship: "Outer" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Relation Name: "T 3" +
+ Alias: "r6" +
+ Disabled: false
+(1 row)
+
-- ===================================================================
-- test writable foreign table stuff
-- ===================================================================
@@ -6390,6 +7001,25 @@ SELECT c1, c2, c3, c4 FROM fpo_part_local ORDER BY c4;
(3 rows)
DROP TABLE fpo_part_parent;
+-- test write on foreign tables with remote_plans
+EXPLAIN (remote_plans, verbose, costs off)
+UPDATE ft2 SET c2 = c2 + 300 WHERE c1 % 10 = 3;
+ QUERY PLAN
+---------------------------------------------------------------------------------------
+ Update on public.ft2
+ Plan Node ID: 0
+ -> Foreign Update on public.ft2
+ Remote SQL: UPDATE "S 1"."T 1" SET c2 = (c2 + 300) WHERE ((("C 1" % 10) = 3))
+ Plan Node ID: 1
+ Remote Plans:
+ -------------
+ Plan Node ID 1:
+ Update on "S 1"."T 1"
+ -> Seq Scan on "S 1"."T 1"
+ Output: (c2 + 300), ctid
+ Filter: (("T 1"."C 1" % 10) = 3)
+(12 rows)
+
-- Test UPDATE/DELETE with RETURNING on a three-table join
INSERT INTO ft2 (c1,c2,c3)
SELECT id, id - 1200, to_char(id, 'FM00000') FROM generate_series(1201, 1300) id;
@@ -12422,6 +13052,34 @@ SELECT * FROM insert_tbl ORDER BY a;
2505 | 505 | bar
(2 rows)
+EXPLAIN (REMOTE_PLANS, VERBOSE, COSTS OFF)
+INSERT INTO insert_tbl (SELECT * FROM local_tbl UNION ALL SELECT * FROM remote_tbl);
+ QUERY PLAN
+-------------------------------------------------------------------------
+ Insert on public.insert_tbl
+ Remote SQL: INSERT INTO public.base_tbl4(a, b, c) VALUES ($1, $2, $3)
+ Batch Size: 1
+ Plan Node ID: 0
+ -> Append
+ Plan Node ID: 1
+ -> Seq Scan on public.local_tbl
+ Output: local_tbl.a, local_tbl.b, local_tbl.c
+ Plan Node ID: 2
+ -> Async Foreign Scan on public.remote_tbl
+ Output: remote_tbl.a, remote_tbl.b, remote_tbl.c
+ Remote SQL: SELECT a, b, c FROM public.base_tbl3
+ Plan Node ID: 3
+ Remote Plans:
+ -------------
+ Plan Node ID 0:
+ Insert on public.base_tbl4
+ -> Result
+ Output: $1, $2, $3
+ Plan Node ID 3:
+ Seq Scan on public.base_tbl3
+ Output: a, b, c
+(22 rows)
+
-- Check with direct modify
EXPLAIN (VERBOSE, COSTS OFF)
WITH t AS (UPDATE remote_tbl SET c = c || c RETURNING *)
diff --git a/contrib/postgres_fdw/option.c b/contrib/postgres_fdw/option.c
index 79b16c3..43cf019 100644
--- a/contrib/postgres_fdw/option.c
+++ b/contrib/postgres_fdw/option.c
@@ -17,6 +17,7 @@
#include "catalog/pg_foreign_table.h"
#include "catalog/pg_user_mapping.h"
#include "commands/defrem.h"
+#include "commands/explain.h"
#include "commands/extension.h"
#include "libpq/libpq-be.h"
#include "postgres_fdw.h"
@@ -40,6 +41,13 @@ typedef struct PgFdwOption
*/
static PgFdwOption *postgres_fdw_options;
+/*
+ * EXPLAIN hooks
+ */
+static explain_per_node_hook_type prev_explain_per_node_hook;
+static explain_per_plan_hook_type prev_explain_per_plan_hook;
+static explain_validate_options_hook_type prev_explain_validate_options_hook;
+
/*
* GUC parameters
*/
@@ -566,6 +574,61 @@ process_pgfdw_appname(const char *appname)
return buf.data;
}
+/*
+ * Get the PgFdwExplainState structure from an ExplainState; if there is
+ * none, create one, attach it to the ExplainState, and return it.
+ */
+static PgFdwExplainState *
+pgfdw_ensure_options(ExplainState *es)
+{
+ PgFdwExplainState *pgfdw_explain_state;
+
+ pgfdw_explain_state = GetExplainExtensionState(es, GetExplainExtensionId("postgres_fdw"));
+
+ if (pgfdw_explain_state == NULL)
+ {
+ pgfdw_explain_state = palloc0(sizeof(PgFdwExplainState));
+ SetExplainExtensionState(es, GetExplainExtensionId("postgres_fdw"), pgfdw_explain_state);
+ pgfdw_explain_state->all_remote_plans = NIL;
+ }
+
+ return pgfdw_explain_state;
+}
+
+/*
+ * Parse handler for EXPLAIN (REMOTE_PLANS).
+ */
+static void
+pgfdw_remote_plans_apply(ExplainState *es, DefElem *opt, ParseState *pstate)
+{
+ PgFdwExplainState *options = pgfdw_ensure_options(es);
+
+ options->remote_plans = defGetBoolean(opt);
+}
+
+static void
+postgresExplainValidateOptions(ExplainState *es, List *options, ParseState *pstate)
+{
+ ListCell *lc;
+ PgFdwExplainState *pgfdw_explain_state = NULL;
+
+ foreach(lc, options)
+ {
+ DefElem *opt = (DefElem *) lfirst(lc);
+
+ if (strcmp(opt->defname, "remote_plans") == 0)
+ {
+ if (defGetBoolean(opt) && es->analyze)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("EXPLAIN options REMOTE_PLANS and ANALYZE cannot be used together"));
+
+ pgfdw_explain_state = pgfdw_ensure_options(es);
+ pgfdw_explain_state->options = options;
+ }
+ }
+}
+
/*
* Module load callback
*/
@@ -592,4 +655,16 @@ _PG_init(void)
NULL);
MarkGUCPrefixReserved("postgres_fdw");
+
+ RegisterExtensionExplainOption("remote_plans", pgfdw_remote_plans_apply,
+ GUCCheckBooleanExplainOption);
+
+ /* per node EXPLAIN hook */
+ prev_explain_per_node_hook = explain_per_node_hook;
+ explain_per_node_hook = postgresExplainPerNode;
+ prev_explain_per_plan_hook = explain_per_plan_hook;
+ explain_per_plan_hook = postgresExplainPerPlan;
+ prev_explain_validate_options_hook = explain_validate_options_hook;
+ explain_validate_options_hook = postgresExplainValidateOptions;
+
}
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 0ea72a6..ff8c76e 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -139,6 +139,19 @@ enum FdwDirectModifyPrivateIndex
FdwDirectModifyPrivateSetProcessed,
};
+/*
+ * Track the extension id in the backend.
+ */
+static int extension_id = -1;
+
+static int
+get_extension_id(void)
+{
+ if (extension_id == -1)
+ extension_id = GetExplainExtensionId("postgres_fdw");
+ return extension_id;
+}
+
/*
* Execution state of a foreign scan using postgres_fdw.
*/
@@ -3042,6 +3055,105 @@ postgresEndDirectModify(ForeignScanState *node)
/* MemoryContext will be deleted automatically. */
}
+static void
+postgresExplainStatement(int plan_node_id,
+ ExplainState *es,
+ PgFdwExplainState *pgfdw_explain_state,
+ PGconn *conn,
+ char *sql)
+{
+ PGresult *volatile res = NULL;
+ StringInfoData explain_sql;
+ int remote_version = PQserverVersion(conn);
+
+ /*
+ * GENERIC_PLAN required because deparsed SQL may contain $n placeholders;
+ * only available from PG 16.
+ */
+ if (remote_version < 160000)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("EXPLAIN option REMOTE_PLANS requires a remote server version of 16 or later"),
+ errdetail("The remote server version is %d.", remote_version));
+
+ PG_TRY();
+ {
+ int numrows,
+ i;
+ PgFdwExplainRemotePlans *explain = (PgFdwExplainRemotePlans *) palloc(sizeof(PgFdwExplainRemotePlans));
+ ListCell *lc;
+
+ initStringInfo(&explain_sql);
+ initStringInfo(&explain->explain_plan);
+
+ /* Forward user-specified options as-is; force GENERIC_PLAN on. */
+ appendStringInfoString(&explain_sql, "EXPLAIN (GENERIC_PLAN TRUE");
+
+ foreach(lc, pgfdw_explain_state->options)
+ {
+ DefElem *opt = (DefElem *) lfirst(lc);
+
+ if (strcmp(opt->defname, "remote_plans") == 0 ||
+ strcmp(opt->defname, "generic_plan") == 0)
+ continue;
+
+ if (opt->arg == NULL)
+ appendStringInfo(&explain_sql, ", %s", opt->defname);
+ else
+ appendStringInfo(&explain_sql, ", %s %s", opt->defname,
+ defGetString(opt));
+ }
+
+ appendStringInfo(&explain_sql, ") %s", sql);
+
+ /* Run the query and collect the remote plan */
+ res = pgfdw_exec_query(conn, explain_sql.data, NULL);
+ if (PQresultStatus(res) != PGRES_TUPLES_OK)
+ pgfdw_report_error(res, conn, explain_sql.data);
+
+ numrows = PQntuples(res);
+
+ for (i = 0; i < numrows; i++)
+ appendStringInfo(&explain->explain_plan, "%s\n", PQgetvalue(res, i, 0));
+
+ if (explain->explain_plan.len > 0 && explain->explain_plan.data[explain->explain_plan.len - 1] == '\n')
+ explain->explain_plan.data[--explain->explain_plan.len] = '\0';
+
+ explain->plan_node_id = plan_node_id;
+ pgfdw_explain_state->all_remote_plans = lappend(pgfdw_explain_state->all_remote_plans, explain);
+ }
+ PG_FINALLY();
+ {
+ if (res)
+ PQclear(res);
+
+ if (explain_sql.data)
+ pfree(explain_sql.data);
+ }
+ PG_END_TRY();
+}
+
+/*
+ * explain_remote_query
+ * Helper function to get connection and execute remote EXPLAIN
+ */
+static void
+explain_remote_query(int plan_node_id, ExplainState *es,
+ PgFdwExplainState *pgfdw_explain_state,
+ Oid foreign_table_oid, char *sql)
+{
+ UserMapping *user;
+ PGconn *conn;
+ ForeignTable *table;
+
+ table = GetForeignTable(foreign_table_oid);
+ user = GetUserMapping(GetUserId(), table->serverid);
+ conn = GetConnection(user, false, NULL);
+
+ postgresExplainStatement(plan_node_id, es, pgfdw_explain_state, conn, sql);
+ ReleaseConnection(conn);
+}
+
/*
* postgresExplainForeignScan
* Produce extra output for EXPLAIN of a ForeignScan on a foreign table
@@ -3051,6 +3163,12 @@ postgresExplainForeignScan(ForeignScanState *node, ExplainState *es)
{
ForeignScan *plan = castNode(ForeignScan, node->ss.ps.plan);
List *fdw_private = plan->fdw_private;
+ PgFdwExplainState *pgfdw_explain_state;
+ char *sql;
+ Oid foreign_table_oid = InvalidOid;
+
+ if (node->ss.ss_currentRelation)
+ foreign_table_oid = RelationGetRelid(node->ss.ss_currentRelation);
/*
* Identify foreign scans that are really joins or upper relations. The
@@ -3112,6 +3230,13 @@ postgresExplainForeignScan(ForeignScanState *node, ExplainState *es)
Assert(rte->rtekind == RTE_RELATION);
/* This logic should agree with explain.c's ExplainTargetRel */
relname = get_rel_name(rte->relid);
+
+ /*
+ * Save first table OID for getting server connection
+ */
+ if (!OidIsValid(foreign_table_oid))
+ foreign_table_oid = rte->relid;
+
if (es->verbose)
{
char *namespace;
@@ -3137,16 +3262,25 @@ postgresExplainForeignScan(ForeignScanState *node, ExplainState *es)
ExplainPropertyText("Relations", relations.data, es);
}
+ sql = strVal(list_nth(fdw_private, FdwScanPrivateSelectSql));
+
/*
* Add remote query, when VERBOSE option is specified.
*/
if (es->verbose)
- {
- char *sql;
-
- sql = strVal(list_nth(fdw_private, FdwScanPrivateSelectSql));
ExplainPropertyText("Remote SQL", sql, es);
- }
+
+ pgfdw_explain_state = GetExplainExtensionState(es, get_extension_id());
+
+ /* If we don't have a foreign table oid by now, something went wrong */
+ Assert(foreign_table_oid);
+
+ if (pgfdw_explain_state && pgfdw_explain_state->remote_plans)
+ explain_remote_query(node->ss.ps.plan->plan_node_id,
+ es,
+ pgfdw_explain_state,
+ foreign_table_oid,
+ sql);
}
/*
@@ -3160,11 +3294,12 @@ postgresExplainForeignModify(ModifyTableState *mtstate,
int subplan_index,
ExplainState *es)
{
+ char *sql = strVal(list_nth(fdw_private,
+ FdwModifyPrivateUpdateSql));
+ PgFdwExplainState *pgfdw_explain_state;
+
if (es->verbose)
{
- char *sql = strVal(list_nth(fdw_private,
- FdwModifyPrivateUpdateSql));
-
ExplainPropertyText("Remote SQL", sql, es);
/*
@@ -3174,6 +3309,14 @@ postgresExplainForeignModify(ModifyTableState *mtstate,
if (rinfo->ri_BatchSize > 0)
ExplainPropertyInteger("Batch Size", NULL, rinfo->ri_BatchSize, es);
}
+
+ pgfdw_explain_state = GetExplainExtensionState(es, get_extension_id());
+ if (pgfdw_explain_state && pgfdw_explain_state->remote_plans)
+ explain_remote_query(mtstate->ps.plan->plan_node_id,
+ es,
+ pgfdw_explain_state,
+ rinfo->ri_RelationDesc->rd_rel->oid,
+ sql);
}
/*
@@ -3186,13 +3329,22 @@ postgresExplainDirectModify(ForeignScanState *node, ExplainState *es)
{
List *fdw_private;
char *sql;
+ PgFdwExplainState *pgfdw_explain_state;
+
+ fdw_private = ((ForeignScan *) node->ss.ps.plan)->fdw_private;
+ sql = strVal(list_nth(fdw_private, FdwDirectModifyPrivateUpdateSql));
if (es->verbose)
- {
- fdw_private = ((ForeignScan *) node->ss.ps.plan)->fdw_private;
- sql = strVal(list_nth(fdw_private, FdwDirectModifyPrivateUpdateSql));
ExplainPropertyText("Remote SQL", sql, es);
- }
+
+ pgfdw_explain_state = GetExplainExtensionState(es, get_extension_id());
+
+ if (pgfdw_explain_state && pgfdw_explain_state->remote_plans)
+ explain_remote_query(node->ss.ps.plan->plan_node_id,
+ es,
+ pgfdw_explain_state,
+ RelationGetRelid(node->ss.ss_currentRelation),
+ sql);
}
/*
@@ -8851,3 +9003,95 @@ get_batch_size_option(Relation rel)
return batch_size;
}
+
+void
+postgresExplainPerNode(PlanState *planstate, List *ancestors,
+ const char *relationship, const char *plan_name,
+ ExplainState *es)
+{
+ PgFdwExplainState *pgfdw_explain_state;
+
+ pgfdw_explain_state = GetExplainExtensionState(es, get_extension_id());
+
+ if (pgfdw_explain_state == NULL ||
+ !pgfdw_explain_state->remote_plans)
+ return;
+
+ if (pgfdw_explain_state && pgfdw_explain_state->remote_plans)
+ ExplainPropertyInteger("Plan Node ID", NULL, planstate->plan->plan_node_id, es);
+}
+
+static void
+pgfdwFormatRemotePlan(PgFdwExplainRemotePlans *explain,
+ ExplainState *es,
+ int plan_node_id)
+{
+ char *token;
+ StringInfoData remote_plan_name;
+
+ initStringInfo(&remote_plan_name);
+ appendStringInfo(&remote_plan_name, "Plan Node ID %d", plan_node_id);
+
+ ExplainOpenGroup(remote_plan_name.data, remote_plan_name.data, false, es);
+
+ if (es->format == EXPLAIN_FORMAT_TEXT)
+ {
+ appendStringInfo(es->str, "Plan Node ID %d:", plan_node_id);
+ appendStringInfoString(es->str, "\n");
+ }
+
+ while ((token = strsep(&explain->explain_plan.data, "\n")) != NULL)
+ {
+ if (es->format == EXPLAIN_FORMAT_JSON ||
+ es->format == EXPLAIN_FORMAT_YAML)
+ appendStringInfoString(es->str, "\n");
+
+ appendStringInfoSpaces(es->str, (es->indent == 0) ? 2 : es->indent * 2);
+ appendStringInfoString(es->str, token);
+
+ if (es->format == EXPLAIN_FORMAT_XML ||
+ es->format == EXPLAIN_FORMAT_TEXT)
+ appendStringInfoString(es->str, "\n");
+ }
+
+ ExplainCloseGroup(remote_plan_name.data, remote_plan_name.data, false, es);
+ pfree(remote_plan_name.data);
+}
+
+void
+postgresExplainPerPlan(PlannedStmt *plannedstmt,
+ IntoClause *into,
+ ExplainState *es,
+ const char *queryString,
+ ParamListInfo params,
+ QueryEnvironment *queryEnv)
+{
+ ListCell *lc;
+ PgFdwExplainState *pgfdw_explain_state;
+
+ pgfdw_explain_state = GetExplainExtensionState(es, get_extension_id());
+
+ if (pgfdw_explain_state == NULL ||
+ pgfdw_explain_state->all_remote_plans == NIL ||
+ !pgfdw_explain_state->remote_plans)
+ return;
+
+ ExplainOpenGroup("Remote Plans", "Remote Plans", true, es);
+ if (es->format == EXPLAIN_FORMAT_TEXT)
+ {
+ appendStringInfo(es->str, "Remote Plans:\n");
+ appendStringInfo(es->str, "-------------\n");
+ }
+
+ /* Process every remote plan captured */
+ foreach(lc, pgfdw_explain_state->all_remote_plans)
+ {
+ PgFdwExplainRemotePlans *explain = (PgFdwExplainRemotePlans *) lfirst(lc);
+
+ pgfdwFormatRemotePlan(explain,
+ es,
+ explain->plan_node_id);
+ }
+
+ ExplainCloseGroup("Remote Plans", "Remote Plans", true, es);
+}
diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h
index a2bb1ff..6ede7a4 100644
--- a/contrib/postgres_fdw/postgres_fdw.h
+++ b/contrib/postgres_fdw/postgres_fdw.h
@@ -13,6 +13,7 @@
#ifndef POSTGRES_FDW_H
#define POSTGRES_FDW_H
+#include "commands/explain_state.h"
#include "foreign/foreign.h"
#include "lib/stringinfo.h"
#include "libpq/libpq-be-fe.h"
@@ -151,6 +152,22 @@ typedef enum PgFdwSamplingMethod
ANALYZE_SAMPLE_BERNOULLI, /* TABLESAMPLE bernoulli */
} PgFdwSamplingMethod;
+typedef struct PgFdwExplainRemotePlans
+{
+ int plan_node_id;
+ StringInfoData explain_plan;
+
+} PgFdwExplainRemotePlans;
+
+typedef struct PgFdwExplainState
+{
+ List *all_remote_plans;
+
+ /* EXPLAIN options */
+ bool remote_plans;
+ List *options; /* raw user DefElem list, for forwarding */
+} PgFdwExplainState;
+
/* in postgres_fdw.c */
extern int set_transmission_modes(void);
extern void reset_transmission_modes(int nestlevel);
@@ -178,6 +195,16 @@ extern int ExtractConnectionOptions(List *defelems,
extern List *ExtractExtensionList(const char *extensionsString,
bool warnOnMissing);
extern char *process_pgfdw_appname(const char *appname);
+extern void postgresExplainPerNode(PlanState *planstate, List *ancestors,
+ const char *relationship,
+ const char *plan_name,
+ ExplainState *es);
+extern void postgresExplainPerPlan(PlannedStmt *plannedstmt,
+ IntoClause *into,
+ ExplainState *es,
+ const char *queryString,
+ ParamListInfo params,
+ QueryEnvironment *queryEnv);
extern char *pgfdw_application_name;
/* in deparse.c */
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index e868da0..198b894 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -311,6 +311,11 @@ SELECT * FROM ft1 t1 WHERE t1.c3 = (SELECT MAX(c3) FROM ft2 t2) ORDER BY c1;
WITH t1 AS (SELECT * FROM ft1 WHERE c1 <= 10) SELECT t2.c1, t2.c2, t2.c3, t2.c4 FROM t1, ft2 t2 WHERE t1.c1 = t2.c1 ORDER BY t1.c1;
-- fixed values
SELECT 'fixed', NULL FROM ft1 t1 WHERE c1 = 1;
+-- with WHERE clause and remote_plans with different formats
+EXPLAIN (REMOTE_PLANS, FORMAT YAML, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+EXPLAIN (REMOTE_PLANS TRUE, FORMAT XML, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+EXPLAIN (REMOTE_PLANS, FORMAT JSON, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+EXPLAIN (REMOTE_PLANS TRUE, FORMAT TEXT, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
-- Test forcing the remote server to produce sorted data for a merge join.
SET enable_hashjoin TO false;
SET enable_nestloop TO false;
@@ -338,6 +343,19 @@ SELECT t1."C 1", t2.c1, t3.c1 FROM "S 1"."T 1" t1 left join ft1 t2 full join ft2
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1."C 1", t2.c1, t3.c1 FROM "S 1"."T 1" t1 full join ft1 t2 full join ft2 t3 on (t2.c1 = t3.c1) on (t3.c1 = t1."C 1") OFFSET 100 LIMIT 10;
SELECT t1."C 1", t2.c1, t3.c1 FROM "S 1"."T 1" t1 full join ft1 t2 full join ft2 t3 on (t2.c1 = t3.c1) on (t3.c1 = t1."C 1") OFFSET 100 LIMIT 10;
+
+-- Join push-down test
+-- Ensure join conditions are pushed down to the foreign server
+EXPLAIN (REMOTE_PLANS, VERBOSE, COSTS OFF)
+ SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) WHERE t1.c2 = 10;
+
+-- Tables on multiple foreign connections: ft5 lives on server loopback and
+-- ft6 on server loopback2, so the join cannot be pushed down as a single
+-- foreign join. Each side is scanned over its own connection, so REMOTE_PLANS
+-- must collect and print one remote plan per connection.
+EXPLAIN (REMOTE_PLANS, VERBOSE, COSTS OFF)
+ SELECT t1.c1, t2.c1 FROM ft5 t1 JOIN ft6 t2 ON (t1.c1 = t2.c1) WHERE t1.c1 = 10;
+
RESET enable_hashjoin;
RESET enable_nestloop;
@@ -379,6 +397,11 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c8 = 'foo'; -- can't be
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM "S 1"."T 1" a, ft2 b WHERE a."C 1" = 47 AND b.c1 = a.c2;
SELECT * FROM "S 1"."T 1" a, ft2 b WHERE a."C 1" = 47 AND b.c1 = a.c2;
+-- REMOTE_PLANS over a parameterized foreign scan: the deparsed remote SQL
+-- carries a $1 placeholder, which only plans on the remote side because
+-- REMOTE_PLANS forces GENERIC_PLAN.
+EXPLAIN (REMOTE_PLANS, VERBOSE, COSTS OFF)
+ SELECT * FROM "S 1"."T 1" a, ft2 b WHERE a."C 1" = 47 AND b.c1 = a.c2;
-- check both safe and unsafe join conditions
EXPLAIN (VERBOSE, COSTS OFF)
@@ -1527,6 +1550,33 @@ SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
ORDER BY ft1.c1 LIMIT 5;
+-- EXPLAIN remote_plans
+EXPLAIN (remote_plans, format text, costs off, analyze)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+EXPLAIN (remote_plans, format text, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+EXPLAIN (remote_plans, format xml, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+EXPLAIN (remote_plans, format json, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+EXPLAIN (remote_plans, format yaml, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+
-- ===================================================================
-- test writable foreign table stuff
-- ===================================================================
@@ -1610,6 +1660,9 @@ DELETE FROM fpo_part_parent
FOR PORTION OF c4 FROM '2024-06-01' TO '2024-06-15' WHERE c2 = 1; -- okay
SELECT c1, c2, c3, c4 FROM fpo_part_local ORDER BY c4;
DROP TABLE fpo_part_parent;
+-- test write on foreign tables with remote_plans
+EXPLAIN (remote_plans, verbose, costs off)
+UPDATE ft2 SET c2 = c2 + 300 WHERE c1 % 10 = 3;
-- Test UPDATE/DELETE with RETURNING on a three-table join
INSERT INTO ft2 (c1,c2,c3)
@@ -4252,6 +4305,9 @@ INSERT INTO insert_tbl (SELECT * FROM local_tbl UNION ALL SELECT * FROM remote_t
SELECT * FROM insert_tbl ORDER BY a;
+EXPLAIN (REMOTE_PLANS, VERBOSE, COSTS OFF)
+INSERT INTO insert_tbl (SELECT * FROM local_tbl UNION ALL SELECT * FROM remote_tbl);
+
-- Check with direct modify
EXPLAIN (VERBOSE, COSTS OFF)
WITH t AS (UPDATE remote_tbl SET c = c || c RETURNING *)
diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index b9e1b04..b457611 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -1197,6 +1197,93 @@ CREATE SUBSCRIPTION my_subscription SERVER subscription_server PUBLICATION testp
The query that is actually sent to the remote server for execution can
be examined using <command>EXPLAIN VERBOSE</command>.
</para>
+
+ <para>
+ In addition to the remote query itself, the plan that the remote server
+ chooses for that query can be examined with the
+ <literal>REMOTE_PLANS</literal> option of <command>EXPLAIN</command>.
+ For every foreign scan or foreign modification in the local plan,
+ <filename>postgres_fdw</filename> runs <command>EXPLAIN</command> on the
+ remote server for the corresponding remote query and prints the result in a
+ <literal>Remote Plans</literal> section, keyed by the plan node id of the
+ local node. For example:
+<programlisting>
+EXPLAIN (VERBOSE, REMOTE_PLANS) SELECT * FROM foreign_tbl WHERE id = 42;
+ QUERY PLAN
+-----------------------------------------------------------
+ Foreign Scan on public.foreign_tbl
+ Output: id, val
+ Remote SQL: SELECT id, val FROM public.tbl WHERE ((id = 42))
+ Plan Node ID: 0
+ Remote Plans:
+ -------------
+ Plan Node ID 0:
+ Index Scan using tbl_pkey on public.tbl
+ Output: id, val
+ Index Cond: (tbl.id = 42)
+</programlisting>
+ When the local plan uses a parameterized foreign scan (for example, as
+ the inner side of a nested loop join), the remote SQL contains parameter
+ placeholders. The remote plan is a generic plan in this case:
+<programlisting>
+EXPLAIN (VERBOSE, REMOTE_PLANS, COSTS OFF)
+ SELECT * FROM local_tbl a, foreign_tbl b WHERE a.id = 1 AND b.id = a.val;
+ QUERY PLAN
+-----------------------------------------------------------
+ Nested Loop
+ Output: a.id, a.val, b.id, b.val
+ -> Seq Scan on public.local_tbl a
+ Output: a.id, a.val
+ Filter: (a.id = 1)
+ -> Foreign Scan on public.foreign_tbl b
+ Output: b.id, b.val
+ Remote SQL: SELECT id, val FROM public.tbl WHERE ((id = $1::integer))
+ Plan Node ID: 1
+ Remote Plans:
+ -------------
+ Plan Node ID 1:
+ Index Scan using tbl_pkey on public.tbl
+ Output: id, val
+ Index Cond: (tbl.id = $1)
+</programlisting>
+ </para>
+
+ <para>
+ The <literal>REMOTE_PLANS</literal> option is registered when
+ <filename>postgres_fdw</filename> is loaded, so the module must be loaded
+ (for example with <command>CREATE EXTENSION postgres_fdw</command>) before
+ the option can be used. The following restrictions apply:
+ <itemizedlist>
+ <listitem>
+ <para>
+ It cannot be combined with <literal>ANALYZE</literal>; doing so raises an
+ error. The remote query is only planned, never executed, so the foreign
+ server is not affected and no remote rows are fetched.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ The remote server must be running <productname>PostgreSQL</productname>
+ 16 or later, because <literal>REMOTE_PLANS</literal> relies on the
+ <literal>GENERIC_PLAN</literal> option of <command>EXPLAIN</command>,
+ which was introduced in that release. As shown in the parameterized
+ example above, the plan shown by <literal>REMOTE_PLANS</literal> is
+ always a generic plan, which may differ from the plan the remote
+ server actually uses at execution time when specific parameter values
+ are available.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ All <command>EXPLAIN</command> options specified by the user (other than
+ <literal>REMOTE_PLANS</literal> itself and
+ <literal>GENERIC_PLAN</literal>) are forwarded to the remote server.
+ If the remote server is running an older version that does not recognize
+ a forwarded option, it will report an error.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
</sect2>
<sect2 id="postgres-fdw-remote-query-execution-environment">
diff --git a/src/include/commands/explain_state.h b/src/include/commands/explain_state.h
index 97bc7ed..7e4dc6b 100644
--- a/src/include/commands/explain_state.h
+++ b/src/include/commands/explain_state.h
@@ -45,6 +45,7 @@ typedef struct ExplainWorkersState
typedef struct ExplainState
{
StringInfo str; /* output buffer */
+
/* options */
bool verbose; /* be verbose */
bool analyze; /* print actual times */
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index ffb413a..6f77701 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2305,6 +2305,8 @@ PgChecksumMode
PgFdwAnalyzeState
PgFdwConnState
PgFdwDirectModifyState
+PgFdwExplainRemotePlans
+PgFdwExplainState
PgFdwModifyState
PgFdwOption
PgFdwPathExtraData
base-commit: ad7877b00a4232d71bb4cb63c22f9d748e65e3f7
--
2.50.1 (Apple Git-155)
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2026-07-08 18:12 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-05-25 04:46 [PATCH v3 1/1] Handle a NULL short_desc in ShowAllGUCConfig Steve Chavez <[email protected]>
2025-11-18 03:25 Re: explain plans for foreign servers dinesh salve <[email protected]>
2025-12-03 22:40 ` Re: explain plans for foreign servers Sami Imseih <[email protected]>
2025-12-04 00:34 ` Re: explain plans for foreign servers Sami Imseih <[email protected]>
2025-12-09 21:08 ` Re: explain plans for foreign servers Sami Imseih <[email protected]>
2026-01-07 10:00 ` Re: explain plans for foreign servers dinesh salve <[email protected]>
2026-01-10 00:16 ` Re: explain plans for foreign servers Sami Imseih <[email protected]>
2026-06-19 19:04 ` Re: explain plans for foreign servers dinesh salve <[email protected]>
2026-07-03 18:35 ` Re: explain plans for foreign servers Sami Imseih <[email protected]>
2026-07-04 17:07 ` Re: explain plans for foreign servers dinesh salve <[email protected]>
2026-07-08 18:12 ` Re: explain plans for foreign servers dinesh salve <[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