public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v3 1/1] Handle a NULL short_desc in ShowAllGUCConfig
14+ messages / 7 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; 14+ 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] 14+ messages in thread
* [PATCH v3 1/1] Handle a NULL short_desc in ShowAllGUCConfig
@ 2022-05-25 04:46 Steve Chavez <[email protected]>
0 siblings, 0 replies; 14+ 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] 14+ messages in thread
* [PATCH v3 1/1] Handle a NULL short_desc in ShowAllGUCConfig
@ 2022-05-25 04:46 Steve Chavez <[email protected]>
0 siblings, 0 replies; 14+ 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] 14+ messages in thread
* Re: AIO writes vs hint bits vs checksums
@ 2025-04-01 15:08 ` Andres Freund <[email protected]>
2 siblings, 0 replies; 14+ messages in thread
From: Andres Freund @ 2025-04-01 15:08 UTC (permalink / raw)
To: Heikki Linnakangas <[email protected]>; +Cc: pgsql-hackers; Noah Misch <[email protected]>; Robert Haas <[email protected]>; Thomas Munro <[email protected]>
Hi,
On 2025-04-01 13:34:53 +0300, Heikki Linnakangas wrote:
> Here's a rebase of these patches.
Thanks!
Curious what made you do this? Do you need any parts of this soon?
I've been hacking on this a bit more, btw, just haven't gotten around to the
necessary README rephrasings... :(
> I went ahead and committed the "heapam: Only set tuple's block once per page
> in pagemode" patch, because it was trivial and independent of the rest.
Thanks!
I think I should just commit the kill_prior_tuples test, it's better than
nothing.
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: AIO writes vs hint bits vs checksums
@ 2025-04-01 15:29 ` Nico Williams <[email protected]>
2 siblings, 0 replies; 14+ messages in thread
From: Nico Williams @ 2025-04-01 15:29 UTC (permalink / raw)
To: Heikki Linnakangas <[email protected]>; +Cc: Andres Freund <[email protected]>; pgsql-hackers
On Wed, Oct 30, 2024 at 02:16:51PM +0200, Heikki Linnakangas wrote:
> Acquiring the exclusive lock in step 4 is just a way to wait for all the
> existing share-lockers to release the lock. You wouldn't need to block new
> share-lockers. We have LW_WAIT_UNTIL_FREE, which is almost what we need, but
> it currently ignores share-lockers. So doing this "properly" would require
> more changes to LWLocks. Briefly acquiring an exclusive lock seems
> acceptable though.
The problem is starvation. For this you really want something more like
rwlocks that do not have the writer starvation problem. But rwlocks
have other problems too, like in this case forcing readers to wait.
What you want here is something more like a barrier where readers that
did not see that the page has BM_IO_IN_PROGRESS set get to act as though
it's not set while readers that did see that the page has
BM_IO_IN_PROGRESS set don't, and the process that did set that bit gets
to wait for the first set of readers all without blocking the second set
of readers. That's something akin to an rwlock, but better -- in fact,
it resembles RCU.
Nico
--
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: AIO writes vs hint bits vs checksums
@ 2025-08-13 19:38 ` Andres Freund <[email protected]>
2025-10-27 11:48 ` Re: AIO writes vs hint bits vs checksums Maxim Orlov <[email protected]>
2 siblings, 1 reply; 14+ messages in thread
From: Andres Freund @ 2025-08-13 19:38 UTC (permalink / raw)
To: Heikki Linnakangas <[email protected]>; +Cc: Noah Misch <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>; Thomas Munro <[email protected]>
Hi,
On 2024-10-30 12:45:27 -0400, Andres Freund wrote:
> On 2024-10-30 13:29:01 +0200, Heikki Linnakangas wrote:
> > On 30/10/2024 04:21, Andres Freund wrote:
> > > Attached is a, unfortunately long, series of patches implementing what I
> > > described upthread.
> >
> > Review of the preparatory patches:
> >
> > > 0001 Add very basic test for kill_prior_tuples
> > >
> > > We currently don't exercise this patch for gist and hash, which seems
> > > somewhat criminal.
> >
> > Interesting to use the isolationtester for this. There's just one session,
> > so you're just using it to define reusable steps with handy names.
>
> Yea. I had started out writing it as a pg_regress style test and it quickly got very
> hard to understand.
>
>
> > I'm fine with that, but please add a comment to explain it.
>
> Makes sense.
>
>
> > I wonder if it'd be more straightforward to make it a regular pg_regress
> > test though. There would be some repetition, but would it be so bad?
>
> I found it to be quite bad. If testing just one AM it's ok-ish, but once you
> test 2-3 it gets very long and repetitive. I guess we could use functions or
> such to make it a bit less painful - but that point, is it actually simpler?
>
>
> > You forgot to add the new test to 'isolation_schedule'.
>
> Oops.
>
> > typos:
> > "inex" -> "index"
> > "does something approximately reasonble" -> "do something approximately
> > reasonable"
>
> Oops^2.
Pushed the test with these changes.
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: AIO writes vs hint bits vs checksums
2025-08-13 19:38 ` Re: AIO writes vs hint bits vs checksums Andres Freund <[email protected]>
@ 2025-10-27 11:48 ` Maxim Orlov <[email protected]>
2025-10-28 04:06 ` Re: AIO writes vs hint bits vs checksums Michael Paquier <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Maxim Orlov @ 2025-10-27 11:48 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Heikki Linnakangas <[email protected]>; Noah Misch <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>; Thomas Munro <[email protected]>
On Wed, 13 Aug 2025 at 22:38, Andres Freund <[email protected]> wrote:
>
> Pushed the test with these changes.
I think there is one thing that could be improved here. The isolation
test index-killtuples (377b7ab1452) adds a new dependency:
contrib/btree_gin. contrib/btree_gist. This must be taken into
account in the makefile.
Prior to this patch, isolation tests could be carried out independently
using "make check -C src/test/isolation", but they now require make
check-world to execute successfully, otherwise this test fails with:
====
step create_ext_btree_gist: CREATE EXTENSION btree_gist;
+ERROR: extension "btree_gist" is not available
step create_gist: CREATE INDEX kill_prior_tuple_gist ON kill_prior_tuple
USING gist (key);
+ERROR: data type integer has no default operator class for access method
"gist"
step flush: SELECT FROM pg_stat_force_next_flush();
====
--
Best regards,
Maxim Orlov.
Attachments:
[application/octet-stream] 0001-Fix-isolation-tests-run-after-adding-kill_prior_tupl.patch (758B, ../../CACG=ezYQf0Np-DN0DYehYoisVRiRK1RnmZFkr-0d8RoD1NUBXQ@mail.gmail.com/3-0001-Fix-isolation-tests-run-after-adding-kill_prior_tupl.patch)
download | inline diff:
From d161c5d493b8851da9141282350ce6d77bb235a5 Mon Sep 17 00:00:00 2001
From: Maxim Orlov <[email protected]>
Date: Mon, 27 Oct 2025 14:28:31 +0300
Subject: [PATCH] Fix isolation tests run after adding kill_prior_tuples test
---
src/test/isolation/Makefile | 2 ++
1 file changed, 2 insertions(+)
diff --git a/src/test/isolation/Makefile b/src/test/isolation/Makefile
index ade2256ed3a..63594a18030 100644
--- a/src/test/isolation/Makefile
+++ b/src/test/isolation/Makefile
@@ -5,6 +5,8 @@
PGFILEDESC = "pg_isolation_regress/isolationtester - multi-client test driver"
PGAPPICON = win32
+EXTRA_INSTALL=contrib/btree_gin contrib/btree_gist
+
subdir = src/test/isolation
top_builddir = ../../..
include $(top_builddir)/src/Makefile.global
--
2.43.0
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: AIO writes vs hint bits vs checksums
2025-08-13 19:38 ` Re: AIO writes vs hint bits vs checksums Andres Freund <[email protected]>
2025-10-27 11:48 ` Re: AIO writes vs hint bits vs checksums Maxim Orlov <[email protected]>
@ 2025-10-28 04:06 ` Michael Paquier <[email protected]>
0 siblings, 0 replies; 14+ messages in thread
From: Michael Paquier @ 2025-10-28 04:06 UTC (permalink / raw)
To: Maxim Orlov <[email protected]>; +Cc: Andres Freund <[email protected]>; Heikki Linnakangas <[email protected]>; Noah Misch <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>; Thomas Munro <[email protected]>
On Mon, Oct 27, 2025 at 02:48:43PM +0300, Maxim Orlov wrote:
> I think there is one thing that could be improved here. The isolation
> test index-killtuples (377b7ab1452) adds a new dependency:
> contrib/btree_gin. contrib/btree_gist. This must be taken into
> account in the makefile.
Also mentioned here:
https://postgr.es/m/[email protected]
It looks that the conclusion would be to move this test into its own
test module to avoid the external contrib/ dependency in
src/test/isolation/, as duplicated Makefile rules in
src/test/isolation/Makefile are unpleasant.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, ../../[email protected]/2-signature.asc)
download
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: explain plans for foreign servers
@ 2026-01-07 10:00 dinesh salve <[email protected]>
2026-01-10 00:16 ` Re: explain plans for foreign servers Sami Imseih <[email protected]>
0 siblings, 1 reply; 14+ 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] 14+ messages in thread
* Re: explain plans for foreign servers
2026-01-07 10:00 Re: explain plans for foreign servers dinesh salve <[email protected]>
@ 2026-01-10 00:16 ` Sami Imseih <[email protected]>
2026-06-19 19:04 ` Re: explain plans for foreign servers dinesh salve <[email protected]>
0 siblings, 1 reply; 14+ 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] 14+ messages in thread
* Re: explain plans for foreign servers
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 ` dinesh salve <[email protected]>
2026-07-03 18:35 ` Re: explain plans for foreign servers Sami Imseih <[email protected]>
0 siblings, 1 reply; 14+ 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] 14+ messages in thread
* Re: explain plans for foreign servers
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 ` Sami Imseih <[email protected]>
2026-07-04 17:07 ` Re: explain plans for foreign servers dinesh salve <[email protected]>
0 siblings, 1 reply; 14+ 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] 14+ messages in thread
* Re: explain plans for foreign servers
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 ` dinesh salve <[email protected]>
2026-07-08 18:12 ` Re: explain plans for foreign servers dinesh salve <[email protected]>
0 siblings, 1 reply; 14+ 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] 14+ messages in thread
* Re: explain plans for foreign servers
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 ` dinesh salve <[email protected]>
0 siblings, 0 replies; 14+ 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] 14+ messages in thread
end of thread, other threads:[~2026-07-08 18:12 UTC | newest]
Thread overview: 14+ 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]>
2022-05-25 04:46 [PATCH v3 1/1] Handle a NULL short_desc in ShowAllGUCConfig Steve Chavez <[email protected]>
2022-05-25 04:46 [PATCH v3 1/1] Handle a NULL short_desc in ShowAllGUCConfig Steve Chavez <[email protected]>
2025-04-01 15:08 ` Re: AIO writes vs hint bits vs checksums Andres Freund <[email protected]>
2025-04-01 15:29 ` Re: AIO writes vs hint bits vs checksums Nico Williams <[email protected]>
2025-08-13 19:38 ` Re: AIO writes vs hint bits vs checksums Andres Freund <[email protected]>
2025-10-27 11:48 ` Re: AIO writes vs hint bits vs checksums Maxim Orlov <[email protected]>
2025-10-28 04:06 ` Re: AIO writes vs hint bits vs checksums Michael Paquier <[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