explain plans for foreign servers
Hi Hackers,
I am working on a feature in postgres_fdw extension to show plans used by
remote postgresql servers in the output of the EXPLAIN command.
I think this will help end users understand query execution plans used by
remote servers. Sample output for table people where people_1 is local
partition and people_2 is remote partition would look like -
postgres:5432> explain select * from "test"."people";
QUERY PLAN
Append (cost=0.00..399.75 rows=2270 width=46)
→ Seq Scan on "people.people_1" people_1 (cost=0.00..21.00 rows=1100
width=46)
→ Foreign Scan on "people.people_2" people_2 (cost=100.00..367.40
rows=1170 width=46)
Remote Plan
Seq Scan on "people.people_2" (cost=0.00..21.00 rows=1100
width=46)
(5 rows)
I would like community inputs on below high level thoughts:
1. To enable this feature, either we can introduce a new option in EXPLAIN
command e.g. (fetch_remote_plans true) or control this behaviour using a
guc defined in postgres_fdw extension. I am more inclined towards guc
as this feature is for extension postgres_fdw. Adding the EXPLAIN command
option might force other FDW extensions to handle this.
2. For ANALYZE = false, the idea is that postgres_fdw would create a
connection to a remote server, prepare SQL to send over connection and
store received plans in ExplainState.
3. For ANALYZE = true, idea is that postgres_fdw would set a new guc over
connection to remote server, remote server postgres_fdw would read this guc
and send back used query plan as a NOTICE (similar to auto_explain
extension does) with custom header which postgres_fdw extension
understands. . We also have an opportunity to introduce a new message type
in the protocol to send back explain plans but it might look like too much
work for this feature. Open to ideas here.
Dinesh Salve
SDE@AWS
dinesh salve <cooltodinesh@gmail.com> writes:
Hi,
I am working on a feature in postgres_fdw extension to show plans used
by remote postgresql servers in the output of the EXPLAIN command.I think this will help end users understand query execution plans used
by remote servers. Sample output for table people where people_1 is
local partition and people_2 is remote partition would look like
This looks nice! Especially for the people who want a FDW based sharding
cluster.
I would like community inputs on below high level thoughts:
1. To enable this feature, either we can introduce a new option in
EXPLAIN command e.g. (fetch_remote_plans true) or control this
behaviour using a guc defined in postgres_fdw extension. I am more
inclined towards guc as this feature is for extension
postgres_fdw. Adding the EXPLAIN command option might force other FDW
extensions to handle this.
+1.
2. For ANALYZE = false, the idea is that postgres_fdw would create a
connection to a remote server, prepare SQL to send over connection and
store received plans in ExplainState.
3. For ANALYZE = true, idea is that postgres_fdw would set a new guc
over connection to remote server, remote server postgres_fdw would
read this guc and send back used query plan as a NOTICE (similar to
auto_explain extension does) with custom header which postgres_fdw
extension understands. We also have an opportunity to introduce a new
message type in the protocol to send back explain plans but it might
look like too much work for this feature. Open to ideas here.
This generally looks good to me. Looking forward a patch for the
details.
--
Best Regards
Andy Fan
On Mon, Nov 11, 2024 at 9:12 PM dinesh salve <cooltodinesh@gmail.com> wrote:
Hi Hackers,
I am working on a feature in postgres_fdw extension to show plans used by remote postgresql servers in the output of the EXPLAIN command.
I think this will help end users understand query execution plans used by remote servers. Sample output for table people where people_1 is local partition and people_2 is remote partition would look like -postgres:5432> explain select * from "test"."people";
QUERY PLAN
Append (cost=0.00..399.75 rows=2270 width=46)
→ Seq Scan on "people.people_1" people_1 (cost=0.00..21.00 rows=1100 width=46)
→ Foreign Scan on "people.people_2" people_2 (cost=100.00..367.40 rows=1170 width=46)
Remote Plan
Seq Scan on "people.people_2" (cost=0.00..21.00 rows=1100 width=46)
(5 rows)I would like community inputs on below high level thoughts:
1. To enable this feature, either we can introduce a new option in EXPLAIN command e.g. (fetch_remote_plans true) or control this behaviour using a guc defined in postgres_fdw extension. I am more inclined towards guc as this feature is for extension postgres_fdw. Adding the EXPLAIN command option might force other FDW extensions to handle this.
2. For ANALYZE = false, the idea is that postgres_fdw would create a connection to a remote server, prepare SQL to send over connection and store received plans in ExplainState.
3. For ANALYZE = true, idea is that postgres_fdw would set a new guc over connection to remote server, remote server postgres_fdw would read this guc and send back used query plan as a NOTICE (similar to auto_explain extension does) with custom header which postgres_fdw extension understands. . We also have an opportunity to introduce a new message type in the protocol to send back explain plans but it might look like too much work for this feature. Open to ideas here.
If use_remote_estimates is enabled for a given foreign server,
postgres_fdw fetches EXPLAIN output and plugs those costs into the
local plan's costs. You could use that - display the remote plan only
when use_remote_estimates is enabled. However, there's no guarantee
that the plan so fetched will be the plan used by foreign server when
actually executing the query. Mostly likely that is true but no
guarantee. That's also true if the plan is fetched only for the final
query. Of course the EXPLAIN output differences between server
versions need to taken care of.
But the real question is usability. How do you plan to use it?
--
Best Wishes,
Ashutosh Bapat
On Tue, Nov 12, 2024 at 4:16 PM Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
wrote:
On Mon, Nov 11, 2024 at 9:12 PM dinesh salve <cooltodinesh@gmail.com>
wrote:Hi Hackers,
I am working on a feature in postgres_fdw extension to show plans used
by remote postgresql servers in the output of the EXPLAIN command.
I think this will help end users understand query execution plans used
by remote servers. Sample output for table people where people_1 is local
partition and people_2 is remote partition would look like -postgres:5432> explain select * from "test"."people";
QUERY PLAN
Append (cost=0.00..399.75 rows=2270 width=46)
→ Seq Scan on "people.people_1" people_1 (cost=0.00..21.00 rows=1100width=46)
→ Foreign Scan on "people.people_2" people_2 (cost=100.00..367.40
rows=1170 width=46)
Remote Plan
Seq Scan on "people.people_2" (cost=0.00..21.00 rows=1100width=46)
(5 rows)
I would like community inputs on below high level thoughts:
1. To enable this feature, either we can introduce a new option in
EXPLAIN command e.g. (fetch_remote_plans true) or control this behaviour
using a guc defined in postgres_fdw extension. I am more inclined
towards guc as this feature is for extension postgres_fdw. Adding the
EXPLAIN command option might force other FDW extensions to handle this.2. For ANALYZE = false, the idea is that postgres_fdw would create a
connection to a remote server, prepare SQL to send over connection and
store received plans in ExplainState.3. For ANALYZE = true, idea is that postgres_fdw would set a new guc
over connection to remote server, remote server postgres_fdw would read
this guc and send back used query plan as a NOTICE (similar to auto_explain
extension does) with custom header which postgres_fdw extension
understands. . We also have an opportunity to introduce a new message type
in the protocol to send back explain plans but it might look like too much
work for this feature. Open to ideas here.If use_remote_estimates is enabled for a given foreign server,
postgres_fdw fetches EXPLAIN output and plugs those costs into the
local plan's costs. You could use that - display the remote plan only
when use_remote_estimates is enabled. However, there's no guarantee
that the plan so fetched will be the plan used by foreign server when
actually executing the query. Mostly likely that is true but no
guarantee. That's also true if the plan is fetched only for the final
query. Of course the EXPLAIN output differences between server
versions need to taken care of.But the real question is usability. How do you plan to use it?
--
Best Wishes,
Ashutosh Bapat
Hi Ashutosh,
Thanks for the feedback.
1. As admins and devs need to look at plans from time to time, if a remote
plan is displayed only when use_remote_estimates is enabled, either the end
user needs to keep it enabled (at table or server level) all the time or
enable/disable when they want to view remote plans. I actually wanted to
decouple that action for the user and make it easy by just setting a guc ->
SET postgres_fdw.show_remote_explain_plans = on;
2. Yeah, plans are not guaranteed but this feature would give a high level
idea on overall query execution at one place. This could be pretty useful
when postgresql is used for sharding and tables are set up as partitions
and the end user wants to view overall query execution.
Dinesh Salve
SDE@AWS
Hi Hackers,
I am working on a feature in postgres_fdw extension to show plans used by remote postgresql servers in the output of the EXPLAIN command.
I think this will help end users understand query execution plans used by remote servers. Sample output for table people where people_1 is local partition and people_2 is remote partition would look like -postgres:5432> explain select * from "test"."people";
QUERY PLAN
Append (cost=0.00..399.75 rows=2270 width=46)
→ Seq Scan on "people.people_1" people_1 (cost=0.00..21.00 rows=1100 width=46)
→ Foreign Scan on "people.people_2" people_2 (cost=100.00..367.40 rows=1170 width=46)
Remote Plan
Seq Scan on "people.people_2" (cost=0.00..21.00 rows=1100 width=46)
(5 rows)I would like community inputs on below high level thoughts:
1. To enable this feature, either we can introduce a new option in EXPLAIN command e.g. (fetch_remote_plans true) or control this behaviour using a guc defined in postgres_fdw extension. I am more inclined towards guc as this feature is for extension postgres_fdw. Adding the EXPLAIN command option might force other FDW extensions to handle this.
2. For ANALYZE = false, the idea is that postgres_fdw would create a connection to a remote server, prepare SQL to send over connection and store received plans in ExplainState.
3. For ANALYZE = true, idea is that postgres_fdw would set a new guc over connection to remote server, remote server postgres_fdw would read this guc and send back used query plan as a NOTICE (similar to auto_explain extension does) with custom header which postgres_fdw extension understands. . We also have an opportunity to introduce a new message type in the protocol to send back explain plans but it might look like too much work for this feature. Open to ideas here.
Dinesh Salve
SDE@AWS
Hi Dinesh,
Thank you for your proposal regarding explain for foreign servers.
I have been working on a similar feature and there are several considerations to take into account.
To enable this feature it is preferable to use GUC rather than the EXPLAIN option, as it simplifies regression testing. You can simply set it to off before most tests that involve plan checking, while leaving the rest unchanged. This leads to a reduction in the size of the differences.
If it is necessary to provide only the execution plan of the foreign query (without actual timing metrics), you should send EXPLAIN with ANALYZE set to OFF, regardless of the initial ANALYZE state. This approach will prevent the re-execution of the remote query (the SQL part of the ForeignScan node), which could potentially lead to side effects. It's safe to send ANALYZE ON during remote EXPLAIN only if your remote SQL is idempotent, i.e. doesn't change anything. That way you can't sent it for *Modify nodes, but it can be applicable for certain ForeignScans, such as those involving FunctionScan. In general, it is safer to enforce ANALYZE OFF in all cases.
Also you can't expose to main EXPLAIN some metrics obtained from remote side through the "remote" explain. For example, values such as actual time, planning time, execution time, and similar metrics cannot be exposed because they relates to events that occurred during the "EXPLAIN" communication, rather than during the actual planning and execution phases. Therefore, these times would likely mislead the user. I suppose it's better to enforce EXPLAIN with TIMING OFF and SUMMARY OFF when obtaining the remote portion of EXPLAIN.
While reconstructing (deparsing) the SQL query to send as part of EXPLAIN to the remote server, you can obtain SQL statements with placeholders (i.e. $1, $2, etc) instead of actual parameter values. It's syntactically incorrect SQL, which will lead to an error on the remote side. There are two ways to avoid this. You can use GENERIC_PLAN feature (v16+), which accepts dollar-parameters here. Another option is to use params_list == NULL in the deparseSelectStmtForRel() function to substitute dummy null values for placeholders, thereby generating syntactically correct SQL. The downside of this approach is the need to perform an additional deparse stage, which can be redundant.
However looking forward a patch, it is likely that some (or all) of my thoughts may become irrelevant.
--
Best regards,
Anton Shmigirilov,
Postgres Professional
On Mon, Nov 25, 2024 at 10:23 PM Anton Shmigirilov <
a.shmigirilov@postgrespro.ru> wrote:
Hi Hackers,
I am working on a feature in postgres_fdw extension to show plans used
by remote postgresql servers in the output of the EXPLAIN command.
I think this will help end users understand query execution plans used
by remote servers. Sample output for table people where people_1 is local
partition and people_2 is remote partition would look like -postgres:5432> explain select * from "test"."people";
QUERY PLAN
Append (cost=0.00..399.75 rows=2270 width=46)
→ Seq Scan on "people.people_1" people_1 (cost=0.00..21.00 rows=1100width=46)
→ Foreign Scan on "people.people_2" people_2 (cost=100.00..367.40
rows=1170 width=46)
Remote Plan
Seq Scan on "people.people_2" (cost=0.00..21.00 rows=1100width=46)
(5 rows)
I would like community inputs on below high level thoughts:
1. To enable this feature, either we can introduce a new option in
EXPLAIN command e.g. (fetch_remote_plans true) or control this behaviour
using a guc defined in postgres_fdw extension. I am more inclined
towards guc as this feature is for extension postgres_fdw. Adding the
EXPLAIN command option might force other FDW extensions to handle this.2. For ANALYZE = false, the idea is that postgres_fdw would create a
connection to a remote server, prepare SQL to send over connection and
store received plans in ExplainState.3. For ANALYZE = true, idea is that postgres_fdw would set a new guc
over connection to remote server, remote server postgres_fdw would read
this guc and send back used query plan as a NOTICE (similar to auto_explain
extension does) with custom header which postgres_fdw extension
understands. . We also have an opportunity to introduce a new message type
in the protocol to send back explain plans but it might look like too much
work for this feature. Open to ideas here.Dinesh Salve
SDE@AWSHi Dinesh,
Thank you for your proposal regarding explain for foreign servers.
I have been working on a similar feature and there are several
considerations to take into account.To enable this feature it is preferable to use GUC rather than the EXPLAIN
option, as it simplifies regression testing. You can simply set it to off
before most tests that involve plan checking, while leaving the rest
unchanged. This leads to a reduction in the size of the differences.If it is necessary to provide only the execution plan of the foreign query
(without actual timing metrics), you should send EXPLAIN with ANALYZE set
to OFF, regardless of the initial ANALYZE state. This approach will prevent
the re-execution of the remote query (the SQL part of the ForeignScan
node), which could potentially lead to side effects. It's safe to send
ANALYZE ON during remote EXPLAIN only if your remote SQL is idempotent,
i.e. doesn't change anything. That way you can't sent it for *Modify nodes,
but it can be applicable for certain ForeignScans, such as those involving
FunctionScan. In general, it is safer to enforce ANALYZE OFF in all cases.Also you can't expose to main EXPLAIN some metrics obtained from remote
side through the "remote" explain. For example, values such as actual time,
planning time, execution time, and similar metrics cannot be exposed
because they relates to events that occurred during the "EXPLAIN"
communication, rather than during the actual planning and execution phases.
Therefore, these times would likely mislead the user. I suppose it's better
to enforce EXPLAIN with TIMING OFF and SUMMARY OFF when obtaining the
remote portion of EXPLAIN.While reconstructing (deparsing) the SQL query to send as part of EXPLAIN
to the remote server, you can obtain SQL statements with placeholders (i.e.
$1, $2, etc) instead of actual parameter values. It's syntactically
incorrect SQL, which will lead to an error on the remote side. There are
two ways to avoid this. You can use GENERIC_PLAN feature (v16+), which
accepts dollar-parameters here. Another option is to use params_list ==
NULL in the deparseSelectStmtForRel() function to substitute dummy null
values for placeholders, thereby generating syntactically correct SQL. The
downside of this approach is the need to perform an additional deparse
stage, which can be redundant.However looking forward a patch, it is likely that some (or all) of my
thoughts may become irrelevant.--
Best regards,
Anton Shmigirilov,
Postgres Professional
Hello Anton,
Yeah, using guc to enable this feature. I am using auto_explain style
design to get a query plan after foreign server executes it. I am
forwarding user EXPLAIN options to foreign as it is so as to ensure the
user gets expected output. I have prepared a patch which works for SELECT
commands and I am planning to work on other commands based on feedback
so that I invest in right direction. Appreciate if you could take a look
and share feedback. Attached the steps I used to test this as well. Looping
in Andy as he expressed interest in review :)
Dinesh Salve
SDE@AWS
Attachments:
0001-enable-fetching-explain-plans-from-foreign-server.patchapplication/octet-stream; name=0001-enable-fetching-explain-plans-from-foreign-server.patchDownload
From cd92b6eb9651956bbce30efb78aa2fc923379448 Mon Sep 17 00:00:00 2001
From: Dinesh Salve <salved@amazon.com>
Date: Sat, 14 Dec 2024 11:45:07 +0000
Subject: [PATCH] enable fetching explain plans from foreign server
---
contrib/postgres_fdw/Makefile | 3 +-
contrib/postgres_fdw/connection.c | 5 +
.../postgres_fdw/expected/postgres_fdw.out | 92 +++++
contrib/postgres_fdw/option.c | 4 +
contrib/postgres_fdw/postgres_fdw.c | 356 +++++++++++++++-
contrib/postgres_fdw/postgres_fdw.h | 3 +
.../postgres_fdw/postgres_fdw_auto_explain.c | 383 ++++++++++++++++++
contrib/postgres_fdw/sql/postgres_fdw.sql | 16 +
src/backend/commands/explain.c | 56 +--
src/include/commands/explain.h | 2 +
src/include/pg_config_ext.h | 8 +
src/include/stamp-ext-h | 1 +
12 files changed, 889 insertions(+), 40 deletions(-)
create mode 100644 contrib/postgres_fdw/postgres_fdw_auto_explain.c
create mode 100644 src/include/pg_config_ext.h
create mode 100644 src/include/stamp-ext-h
diff --git a/contrib/postgres_fdw/Makefile b/contrib/postgres_fdw/Makefile
index 88fdce40d6..a94d04c61e 100644
--- a/contrib/postgres_fdw/Makefile
+++ b/contrib/postgres_fdw/Makefile
@@ -7,7 +7,8 @@ OBJS = \
deparse.o \
option.o \
postgres_fdw.o \
- shippable.o
+ shippable.o \
+ postgres_fdw_auto_explain.o
PGFILEDESC = "postgres_fdw - foreign data wrapper for PostgreSQL"
PG_CPPFLAGS = -I$(libpq_srcdir)
diff --git a/contrib/postgres_fdw/connection.c b/contrib/postgres_fdw/connection.c
index 2326f391d3..5ac51380ae 100644
--- a/contrib/postgres_fdw/connection.c
+++ b/contrib/postgres_fdw/connection.c
@@ -704,6 +704,11 @@ configure_remote_session(PGconn *conn)
do_sql_command(conn, "SET extra_float_digits = 3");
else
do_sql_command(conn, "SET extra_float_digits = 2");
+
+ if (get_postgres_fdw_show_remote_explain_enabled())
+ {
+ do_sql_command(conn, "LOAD 'postgres_fdw'");
+ }
}
/*
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index bf322198a2..d139d2e8e0 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -442,6 +442,98 @@ SELECT 'fixed', NULL FROM ft1 t1 WHERE c1 = 1;
fixed |
(1 row)
+-- run same queries with postgres_fdw.show_remote_explain_plans set on
+SET postgres_fdw.show_remote_explain_plans = on;
+-- single table without alias
+EXPLAIN (COSTS OFF) SELECT * FROM ft1 ORDER BY c3, c1 OFFSET 100 LIMIT 10;
+ QUERY PLAN
+-----------------------------------
+ Foreign Scan on ft1
+ Remote Plan
+ Limit
+ -> Sort
+ Sort Key: c3, "C 1"
+ -> Seq Scan on "T 1"
+(6 rows)
+
+-- single table with alias - also test that tableoid sort is not pushed to remote side
+EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.c1, t1.tableoid OFFSET 100 LIMIT 10;
+ QUERY PLAN
+-------------------------------------------------------------------------------------
+ Limit
+ Output: c1, c2, c3, c4, c5, c6, c7, c8, tableoid
+ -> Sort
+ Output: c1, c2, c3, c4, c5, c6, c7, c8, tableoid
+ Sort Key: t1.c3, t1.c1, t1.tableoid
+ -> Foreign Scan on public.ft1 t1
+ Output: c1, c2, c3, c4, c5, c6, c7, c8, tableoid
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
+ Remote Plan
+ Seq Scan on "S 1"."T 1"
+ Output: "C 1", c2, c3, c4, c5, c6, c7, c8
+(11 rows)
+
+-- whole-row reference
+EXPLAIN (VERBOSE, COSTS OFF) SELECT t1 FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan on public.ft1 t1
+ Output: t1.*, c3, c1
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" ORDER BY c3 ASC NULLS LAST, "C 1" ASC NULLS LAST LIMIT 10::bigint OFFSET 100::bigint
+ Remote Plan
+ Limit
+ Output: "C 1", c2, c3, c4, c5, c6, c7, c8
+ -> Sort
+ Output: "C 1", c2, c3, c4, c5, c6, c7, c8
+ Sort Key: "T 1".c3, "T 1"."C 1"
+ -> Seq Scan on "S 1"."T 1"
+ Output: "C 1", c2, c3, c4, c5, c6, c7, c8
+(11 rows)
+
+-- with WHERE clause
+EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1' AND t1.c7 >= '1';
+ 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 ((c7 >= '1')) AND (("C 1" = 101)) AND ((c6 = '1'))
+ Remote Plan
+ 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)
+ Filter: (("T 1".c7 >= '1'::bpchar) AND (("T 1".c6)::text = '1'::text))
+(8 rows)
+
+-- with FOR UPDATE/SHARE
+EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = 101 FOR UPDATE;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------
+ Foreign Scan on public.ft1 t1
+ Output: c1, c2, c3, c4, c5, c6, c7, c8, t1.*
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 101)) FOR UPDATE
+ Remote Plan
+ LockRows
+ Output: "C 1", c2, c3, c4, c5, c6, c7, c8, ctid
+ -> Index Scan using t1_pkey on "S 1"."T 1"
+ Output: "C 1", c2, c3, c4, c5, c6, c7, c8, ctid
+ Index Cond: ("T 1"."C 1" = 101)
+(9 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = 102 FOR SHARE;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------
+ Foreign Scan on public.ft1 t1
+ Output: c1, c2, c3, c4, c5, c6, c7, c8, t1.*
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 102)) FOR SHARE
+ Remote Plan
+ LockRows
+ Output: "C 1", c2, c3, c4, c5, c6, c7, c8, ctid
+ -> Index Scan using t1_pkey on "S 1"."T 1"
+ Output: "C 1", c2, c3, c4, c5, c6, c7, c8, ctid
+ Index Cond: ("T 1"."C 1" = 102)
+(9 rows)
+
+SET postgres_fdw.show_remote_explain_plans = off;
-- Test forcing the remote server to produce sorted data for a merge join.
SET enable_hashjoin TO false;
SET enable_nestloop TO false;
diff --git a/contrib/postgres_fdw/option.c b/contrib/postgres_fdw/option.c
index 232d85354b..f5762eeb6e 100644
--- a/contrib/postgres_fdw/option.c
+++ b/contrib/postgres_fdw/option.c
@@ -586,4 +586,8 @@ _PG_init(void)
NULL);
MarkGUCPrefixReserved("postgres_fdw");
+
+ /* Setup hooks and gucs. */
+ setup_postgres_fdw_gucs();
+ setup_postgres_fdw_hooks();
}
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index c0810fbd7c..7ebf65df6e 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -59,6 +59,9 @@ PG_MODULE_MAGIC;
/* If no remote estimates, assume a sort costs 20% extra */
#define DEFAULT_FDW_SORT_MULTIPLIER 1.2
+/* This is set implementation of post_parse_analyze_hook so that we know EXPLAIN command options. */
+extern ExplainState *pg_fdw_parsed_explain_state;
+
/*
* Indexes of FDW-private information stored in fdw_private lists.
*
@@ -170,6 +173,7 @@ typedef struct PgFdwScanState
MemoryContext temp_cxt; /* context for per-tuple temporary data */
int fetch_size; /* number of tuples per fetch */
+ StringInfo remote_explain_plan; /* EXPLAIN plan received from foreign server */
} PgFdwScanState;
/*
@@ -541,7 +545,14 @@ static void merge_fdw_options(PgFdwRelationInfo *fpinfo,
const PgFdwRelationInfo *fpinfo_o,
const PgFdwRelationInfo *fpinfo_i);
static int get_batch_size_option(Relation rel);
-
+static void set_guc_boolean(PGconn *conn, char* guc, bool value);
+static void set_guc_string(PGconn *conn, char* guc, char* value);
+static void subscribe_postgres_fdw_notices(PGconn *conn, StringInfo explain_plans);
+static void postgres_fdw_explain_notice_processor(void *arg, const char *notice);
+bool static is_explain_query(const char *sql);
+static void enrich_foreign_plans(char *sql, ExplainState *es, UserMapping *inputUser);
+static UserMapping* get_remote_user(ForeignScanState *node);
+static void append_foreign_explain_plan(ExplainState *es, StringInfo explainPlan);
/*
* Foreign-data wrapper handler function: return a struct with pointers
@@ -1516,20 +1527,7 @@ postgresBeginForeignScan(ForeignScanState *node, int eflags)
fsstate = (PgFdwScanState *) palloc0(sizeof(PgFdwScanState));
node->fdw_state = fsstate;
- /*
- * Identify which user to do the remote access as. This should match what
- * ExecCheckPermissions() does.
- */
- userid = OidIsValid(fsplan->checkAsUser) ? fsplan->checkAsUser : GetUserId();
- if (fsplan->scan.scanrelid > 0)
- rtindex = fsplan->scan.scanrelid;
- else
- rtindex = bms_next_member(fsplan->fs_base_relids, -1);
- rte = exec_rt_fetch(rtindex, estate);
-
- /* Get info about foreign table. */
- table = GetForeignTable(rte->relid);
- user = GetUserMapping(userid, table->serverid);
+ user = get_remote_user(node);
/*
* Get connection to the foreign server. Connection manager will
@@ -1537,6 +1535,37 @@ postgresBeginForeignScan(ForeignScanState *node, int eflags)
*/
fsstate->conn = GetConnection(user, false, &fsstate->conn_state);
+ /*
+ * If this is EXPLAIN command, set guc on remote connection to provide options to foreign server for plan generation.
+ */
+ if (is_explain_query(estate->es_sourceText) && get_postgres_fdw_show_remote_explain_enabled()) {
+ set_guc_boolean(fsstate->conn, "postgres_fdw.auto_explain_enabled", true);
+ set_guc_boolean(fsstate->conn, "postgres_fdw.analyze_enabled", pg_fdw_parsed_explain_state->analyze);
+
+ char *expected_explain_format;
+ switch (pg_fdw_parsed_explain_state->format)
+ {
+ case EXPLAIN_FORMAT_TEXT:
+ expected_explain_format = "text";
+ break;
+ case EXPLAIN_FORMAT_JSON:
+ expected_explain_format = "json";
+ break;
+ case EXPLAIN_FORMAT_YAML:
+ expected_explain_format = "yaml";
+ break;
+ case EXPLAIN_FORMAT_XML:
+ expected_explain_format = "xml";
+ break;
+ }
+ set_guc_string(fsstate->conn, "postgres_fdw.format_enabled", expected_explain_format);
+ set_guc_boolean(fsstate->conn, "postgres_fdw.settings_enabled", pg_fdw_parsed_explain_state->settings);
+ set_guc_boolean(fsstate->conn, "postgres_fdw.verbose_enabled", pg_fdw_parsed_explain_state->verbose);
+ set_guc_boolean(fsstate->conn, "postgres_fdw.buffers_enabled", pg_fdw_parsed_explain_state->buffers);
+ set_guc_boolean(fsstate->conn, "postgres_fdw.wal_enabled", pg_fdw_parsed_explain_state->wal);
+ set_guc_boolean(fsstate->conn, "postgres_fdw.timing_enabled", pg_fdw_parsed_explain_state->timing);
+ }
+
/* Assign a unique ID for my cursor */
fsstate->cursor_number = GetCursorNumber(fsstate->conn);
fsstate->cursor_exists = false;
@@ -1574,6 +1603,14 @@ postgresBeginForeignScan(ForeignScanState *node, int eflags)
fsstate->attinmeta = TupleDescGetAttInMetadata(fsstate->tupdesc);
+ /*
+ * Subscribe to NOTICES received from foreign server.
+ */
+ if (get_postgres_fdw_show_remote_explain_enabled() && is_explain_query(estate->es_sourceText)) {
+ fsstate->remote_explain_plan = makeStringInfo();
+ subscribe_postgres_fdw_notices(fsstate->conn, fsstate->remote_explain_plan);
+ }
+
/*
* Prepare for processing of parameters used in remote query, if any.
*/
@@ -2822,6 +2859,114 @@ postgresEndDirectModify(ForeignScanState *node)
/* MemoryContext will be deleted automatically. */
}
+/*
+ * Append plan received from foreign server to ExplainState.
+ */
+static void
+append_foreign_explain_plan(ExplainState *es, StringInfo explainPlan)
+{
+ if (explainPlan->len)
+ {
+ /* Append "Remote Plan" header */
+ switch (es->format)
+ {
+ case EXPLAIN_FORMAT_TEXT:
+ appendStringInfoSpaces(es->str, es->indent * 2);
+ appendStringInfo(es->str, "Remote Plan\n");
+ break;
+ case EXPLAIN_FORMAT_JSON:
+ appendStringInfo(es->str, ",\n");
+ appendStringInfoSpaces(es->str, es->indent * 2);
+ appendStringInfo(es->str, "\"Remote Plan\": ");
+ break;
+ case EXPLAIN_FORMAT_XML:
+ appendStringInfoSpaces(es->str, es->indent * 2);
+ appendStringInfo(es->str, "<Remote-Plan>\n");
+ break;
+ case EXPLAIN_FORMAT_YAML:
+ appendStringInfo(es->str, "\n");
+ appendStringInfoSpaces(es->str, es->indent * 2);
+ appendStringInfo(es->str, "Remote-Plan:\n");
+ break;
+ }
+
+ /* Append fetched remote plan */
+ char *explainPlans = explainPlan->data;
+ if (explainPlans) {
+ // remove extra newlines at the end
+ char *last = explainPlans + explainPlan->len;
+ while (last > explainPlans && (*last == '\n' || *last == '\0'))
+ {
+ *last = '\0';
+ last--;
+ }
+ }
+
+ /* Fetch explain plan from { */
+ // why formatting changes are required
+ if (explainPlans && es->format == EXPLAIN_FORMAT_JSON)
+ {
+ // explainPlans = strchr(explainPlans, '{');
+ }
+ else if (es->format == EXPLAIN_FORMAT_YAML) {
+ explainPlans = strchr(explainPlans, 'P');
+ }
+ bool firstLine = true;
+ while (explainPlans && strlen(explainPlans) > 0)
+ {
+ // this is last line in JSON format
+ if (es->format == EXPLAIN_FORMAT_JSON && strcmp(explainPlans, "]") == 0) {
+ // appendStringInfoSpaces(es->str, es->indent * 2);
+ // appendStringInfoString(es->str,"}");
+ appendStringInfoSpaces(es->str, es->indent * 2);
+ appendStringInfoString(es->str,"]");
+ break;
+ }
+
+ /* add extra indent if not first line in json */
+ if (es->format == EXPLAIN_FORMAT_JSON && firstLine) {
+ firstLine = false;
+ }
+ else if (es->format == EXPLAIN_FORMAT_JSON) {
+ appendStringInfoSpaces(es->str, es->indent * 2);
+ }
+ else if (es->format == EXPLAIN_FORMAT_YAML) {
+ appendStringInfoSpaces(es->str, es->indent * 2 + 4);
+ }
+ else if (es->format == EXPLAIN_FORMAT_XML) {
+ appendStringInfoSpaces(es->str, es->indent * 2);
+ }
+ else {
+ appendStringInfoSpaces(es->str, es->indent * 2 + 2);
+ }
+
+ /* Temporarily replace earliest '\n' with '\0' to get current line */
+ char *curLine = strchr(explainPlans, '\n');
+ if (curLine)
+ *curLine = '\0';
+
+ if (curLine) /* if newline in curline, add '\n' at end */
+ appendStringInfo(es->str,"%s\n", explainPlans);
+ else
+ appendStringInfo(es->str,"%s", explainPlans);
+
+ /* Restore '\n' */
+ if (curLine)
+ *curLine = '\n';
+
+ explainPlans = curLine ? (curLine+1) : NULL;
+ }
+
+ /* Append remote plan footer */
+ switch (es->format)
+ {
+ case EXPLAIN_FORMAT_XML:
+ appendStringInfo(es->str, "</Remote-Plan>\n");
+ break;
+ }
+ }
+}
+
/*
* postgresExplainForeignScan
* Produce extra output for EXPLAIN of a ForeignScan on a foreign table
@@ -2831,6 +2976,7 @@ postgresExplainForeignScan(ForeignScanState *node, ExplainState *es)
{
ForeignScan *plan = castNode(ForeignScan, node->ss.ps.plan);
List *fdw_private = plan->fdw_private;
+ EState *estate = node->ss.ps.state;
/*
* Identify foreign scans that are really joins or upper relations. The
@@ -2927,6 +3073,28 @@ postgresExplainForeignScan(ForeignScanState *node, ExplainState *es)
sql = strVal(list_nth(fdw_private, FdwScanPrivateSelectSql));
ExplainPropertyText("Remote SQL", sql, es);
}
+
+ if (get_postgres_fdw_show_remote_explain_enabled())
+ {
+ /* For analyze = false, we explicitly fetch query plans by executing EXPLAIN on remote shard. */
+ if (!es->analyze)
+ {
+ int rtindex;
+ char *sql;
+ sql = strVal(list_nth(fdw_private, FdwScanPrivateSelectSql));
+ UserMapping *user = get_remote_user(node);
+ enrich_foreign_plans(sql, es, user);
+ }
+ else if (is_explain_query(node->ss.ps.state->es_sourceText)) {
+ PgFdwScanState *fsstate = (PgFdwScanState *) node->fdw_state;
+ if (fsstate->remote_explain_plan->len > 0) {
+ append_foreign_explain_plan(es, fsstate->remote_explain_plan);
+ if (es->format == EXPLAIN_FORMAT_TEXT) {
+ appendStringInfo(es->str, "\n");
+ }
+ }
+ }
+ }
}
/*
@@ -3804,8 +3972,15 @@ static void
fetch_more_data(ForeignScanState *node)
{
PgFdwScanState *fsstate = (PgFdwScanState *) node->fdw_state;
+ ForeignScan *fsplan = (ForeignScan *) node->ss.ps.plan;
+ EState *estate = node->ss.ps.state;
PGresult *volatile res = NULL;
MemoryContext oldcontext;
+ RangeTblEntry *rte;
+ Oid userid;
+ ForeignTable *table;
+ UserMapping *user;
+ int rtindex;
/*
* We'll store the tuples in the batch_cxt. First, flush the previous
@@ -7963,3 +8138,154 @@ get_batch_size_option(Relation rel)
return batch_size;
}
+
+/*
+ * Identify which user to do the remote access as. This should match what
+ * ExecCheckPermissions() does.
+ */
+static UserMapping*
+get_remote_user(ForeignScanState *node)
+{
+ ForeignScan *fsplan = (ForeignScan *) node->ss.ps.plan;
+ EState *estate = node->ss.ps.state;
+ Oid userid;
+ int rtindex;
+ RangeTblEntry *rte;
+ ForeignTable *table;
+
+ userid = OidIsValid(fsplan->checkAsUser) ? fsplan->checkAsUser : GetUserId();
+ if (fsplan->scan.scanrelid > 0)
+ rtindex = fsplan->scan.scanrelid;
+ else
+ rtindex = bms_next_member(fsplan->fs_base_relids, -1);
+ rte = exec_rt_fetch(rtindex, estate);
+
+ /* Get info about foreign table. */
+ table = GetForeignTable(rte->relid);
+ return GetUserMapping(userid, table->serverid);
+}
+
+/*
+ * Connect to remote shards and retreive the explain plans for the given sql.
+ */
+static void
+enrich_foreign_plans(char *sql, ExplainState *es, UserMapping *inputUser) {
+ PGresult *volatile res = NULL;
+ PGconn *conn;
+ StringInfoData sqlE, multiLineplans;
+
+ /* Prepare EXPLAIN command to be sent to foreign server. */
+ initStringInfo(&sqlE);
+ appendStringInfoString(&sqlE, "EXPLAIN (");
+ appendStringInfo(&sqlE, "ANALYZE %s", es->analyze? "true" : "false");
+ appendStringInfo(&sqlE, ", VERBOSE %s", es->verbose? "true" : "false");
+ appendStringInfo(&sqlE, ", COSTS %s", es->costs? "true" : "false");
+ appendStringInfo(&sqlE, ", SETTINGS %s", es->settings? "true" : "false");
+ appendStringInfo(&sqlE, ", BUFFERS %s", es->buffers? "true" : "false");
+
+ if (es->serialize == EXPLAIN_SERIALIZE_NONE) {
+ appendStringInfoString(&sqlE, ", SERIALIZE OFF");
+ }
+ else if (es->serialize == EXPLAIN_SERIALIZE_TEXT) {
+ appendStringInfoString(&sqlE, ", SERIALIZE TEXT");
+ }
+ else if (es->serialize == EXPLAIN_SERIALIZE_BINARY) {
+ appendStringInfoString(&sqlE, ", SERIALIZE BINARY");
+ }
+
+ appendStringInfo(&sqlE, ", WAL %s", es->wal? "true" : "false");
+ appendStringInfo(&sqlE, ", TIMING %s", es->timing? "true" : "false");
+ appendStringInfo(&sqlE, ", SUMMARY %s", es->summary? "true" : "false");
+ appendStringInfo(&sqlE, ", MEMORY %s", es->memory? "true" : "false");
+
+ if (es->format == EXPLAIN_FORMAT_TEXT)
+ {
+ appendStringInfoString(&sqlE, ", FORMAT TEXT");
+ }
+ else if (es->format == EXPLAIN_FORMAT_JSON)
+ {
+ appendStringInfoString(&sqlE, ", FORMAT JSON");
+ }
+ else if (es->format == EXPLAIN_FORMAT_XML)
+ {
+ appendStringInfoString(&sqlE, ", FORMAT XML");
+ }
+ else if (es->format == EXPLAIN_FORMAT_YAML)
+ {
+ appendStringInfoString(&sqlE, ", FORMAT YAML");
+ }
+ appendStringInfoString(&sqlE, ")");
+ appendStringInfoString(&sqlE, sql);
+
+ conn = GetConnection(inputUser, false, NULL);
+
+ PG_TRY();
+ {
+ // Run the query and collect the remote plan
+ res = pgfdw_exec_query(conn, sqlE.data, NULL);
+ if (PQresultStatus(res) != PGRES_TUPLES_OK)
+ pgfdw_report_error(ERROR, res, conn, false, sql);
+ int numrows = PQntuples(res);
+
+ initStringInfo(&multiLineplans);
+ for (int i = 0; i < numrows; i++) {
+ appendStringInfoString(&multiLineplans, PQgetvalue(res, i, 0));
+ if (i != numrows - 1)
+ appendStringInfoString(&multiLineplans, "\n");
+ }
+ append_foreign_explain_plan(es, &multiLineplans);
+ }
+ PG_FINALLY();
+ {
+ if (res)
+ PQclear(res);
+ }
+ PG_END_TRY();
+
+ ReleaseConnection(conn);
+}
+
+static void set_guc_boolean(PGconn *conn, char* guc, bool value)
+{
+ int guc_sql_len = ((strlen("SET LOCAL = ") + strlen(guc)) * sizeof(char)) + sizeof(int);
+ char* guc_sql = palloc0(guc_sql_len + 1);
+ snprintf(guc_sql, guc_sql_len, "SET LOCAL %s = %d", guc, value);
+ do_sql_command(conn, guc_sql);
+}
+
+static void set_guc_string(PGconn *conn, char* guc, char* value)
+{
+ int guc_sql_len = ((strlen("SET LOCAL = ") + strlen(guc) + strlen(value)) * sizeof(char)) + sizeof(int);
+ char* guc_sql = palloc0(guc_sql_len + 1);
+ snprintf(guc_sql, guc_sql_len, "SET LOCAL %s = %s", guc, value);
+ do_sql_command(conn, guc_sql);
+}
+
+/*
+ * This is callback function for NOTICEs from remote shards for EXPLAIN ANALYZE queries.
+ */
+static void postgres_fdw_explain_notice_processor(void *arg, const char *notice) {
+ StringInfo explain_plans = (char **) arg;
+ // We might receive plans per batch of cursor, but we only need to store one.
+ // do we really need to handle len==0. report warn if we still recived. have test around this warn.
+ if (strstr(notice, "postgres_fdw_explain_plan") && explain_plans->len == 0) {
+ char *explain_plan_str = strchr(strchr(notice, ':') + 1, ':') + 1;
+ appendStringInfoString(explain_plans, explain_plan_str);
+ }
+}
+
+/*
+ * Remote shards sends the EXPLAIN PLANS as a NOTICE to the host shard when a guc is set for EXPLAIN ANALYZE queries.
+ * To listen to those NOTICEs, here we subscribe to NOTICEs on this connection and register callback so that
+ * callback function is called instead of defaultNoticeProcessor.
+ */
+static void subscribe_postgres_fdw_notices(PGconn *conn, StringInfo explain_plans) {
+ PQsetNoticeProcessor(conn, postgres_fdw_explain_notice_processor, explain_plans);
+}
+
+/*
+ * Return true if this is EXPLAIN query.
+ */
+bool static is_explain_query(const char *sql) {
+ return pg_strncasecmp("EXPLAIN", sql, 7) == 0;
+}
\ No newline at end of file
diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h
index 9e501660d1..7c105ade83 100644
--- a/contrib/postgres_fdw/postgres_fdw.h
+++ b/contrib/postgres_fdw/postgres_fdw.h
@@ -257,4 +257,7 @@ extern const char *get_jointype_name(JoinType jointype);
extern bool is_builtin(Oid objectId);
extern bool is_shippable(Oid objectId, Oid classId, PgFdwRelationInfo *fpinfo);
+extern void setup_postgres_fdw_hooks(void);
+extern void setup_postgres_fdw_gucs(void);
+
#endif /* POSTGRES_FDW_H */
diff --git a/contrib/postgres_fdw/postgres_fdw_auto_explain.c b/contrib/postgres_fdw/postgres_fdw_auto_explain.c
new file mode 100644
index 0000000000..699792e031
--- /dev/null
+++ b/contrib/postgres_fdw/postgres_fdw_auto_explain.c
@@ -0,0 +1,383 @@
+/*-------------------------------------------------------------------------
+ *
+ * postgres_fdw_auto_explain.c
+ *
+ *
+ * Copyright (c) 2008-2024, PostgreSQL Global Development Group
+ *
+ * IDENTIFICATION
+ * contrib/postgres_fdw/postgres_fdw_auto_explain.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include <limits.h>
+
+#include "access/parallel.h"
+#include "commands/explain.h"
+#include "common/pg_prng.h"
+#include "executor/instrument.h"
+#include "utils/guc.h"
+#include "parser/analyze.h"
+
+/* GUC variables */
+static bool postgres_fdw_show_remote_explain_plans = false;
+static bool postgres_fdw_auto_explain_enabled = false;
+static bool postgres_fdw_analyze = false;
+static bool postgres_fdw_verbose = false;
+static bool postgres_fdw_buffers = false;
+static bool postgres_fdw_wal = false;
+static bool postgres_fdw_triggers = false;
+static bool postgres_fdw_timing = true;
+static bool postgres_fdw_settings = false;
+static int postgres_fdw_format = EXPLAIN_FORMAT_TEXT;
+
+
+static const struct config_enum_entry format_options[] = {
+ {"text", EXPLAIN_FORMAT_TEXT, false},
+ {"xml", EXPLAIN_FORMAT_XML, false},
+ {"json", EXPLAIN_FORMAT_JSON, false},
+ {"yaml", EXPLAIN_FORMAT_YAML, false},
+ {NULL, 0, false}
+};
+
+/* Current nesting depth of ExecutorRun calls */
+static int nesting_level = 0;
+
+#define auto_explain_enabled() \
+ (postgres_fdw_auto_explain_enabled && nesting_level == 1)
+
+
+/* Saved hook values in case of unload */
+static ExecutorStart_hook_type prev_ExecutorStart = NULL;
+static ExecutorRun_hook_type prev_ExecutorRun = NULL;
+static ExecutorFinish_hook_type prev_ExecutorFinish = NULL;
+static ExecutorEnd_hook_type prev_ExecutorEnd = NULL;
+static post_parse_analyze_hook_type prev_post_parse_analyze_hook = NULL;
+
+ExplainState *pg_fdw_parsed_explain_state;
+static bool pg_fdw_load_initialized = false;
+
+static void explain_ExecutorStart(QueryDesc *queryDesc, int eflags);
+static void explain_ExecutorRun(QueryDesc *queryDesc,
+ ScanDirection direction,
+ uint64 count, bool execute_once);
+static void explain_ExecutorFinish(QueryDesc *queryDesc);
+static void explain_ExecutorEnd(QueryDesc *queryDesc);
+static void postgres_fdw_post_parse_analyze(ParseState *pstate, Query *query, JumbleState *jstate);
+bool get_postgres_fdw_show_remote_explain_enabled();
+
+void setup_postgres_fdw_hooks()
+{
+ prev_ExecutorStart = ExecutorStart_hook;
+ ExecutorStart_hook = explain_ExecutorStart;
+ prev_ExecutorRun = ExecutorRun_hook;
+ ExecutorRun_hook = explain_ExecutorRun;
+ prev_ExecutorFinish = ExecutorFinish_hook;
+ ExecutorFinish_hook = explain_ExecutorFinish;
+ prev_ExecutorEnd = ExecutorEnd_hook;
+ ExecutorEnd_hook = explain_ExecutorEnd;
+
+ prev_post_parse_analyze_hook = post_parse_analyze_hook;
+ post_parse_analyze_hook = postgres_fdw_post_parse_analyze;
+ pg_fdw_load_initialized = true;
+}
+
+void setup_postgres_fdw_gucs()
+{
+ DefineCustomBoolVariable("postgres_fdw.show_remote_explain_plans",
+ "If enabled, plan from foreign server is embeded in EXPLAIN command output",
+ NULL,
+ &postgres_fdw_show_remote_explain_plans,
+ false,
+ PGC_SIGHUP | PGC_USERSET,
+ 0,
+ NULL,
+ NULL,
+ NULL);
+
+ DefineCustomBoolVariable("postgres_fdw.auto_explain_enabled",
+ "If enabled, explain plan is sent as a NOTICE",
+ NULL,
+ &postgres_fdw_auto_explain_enabled,
+ false,
+ PGC_USERSET,
+ 0,
+ NULL,
+ NULL,
+ NULL);
+
+ DefineCustomBoolVariable("postgres_fdw.analyze_enabled",
+ "Use EXPLAIN ANALYZE for plan logging.",
+ NULL,
+ &postgres_fdw_analyze,
+ false,
+ PGC_USERSET,
+ 0,
+ NULL,
+ NULL,
+ NULL);
+
+ DefineCustomBoolVariable("postgres_fdw.settings_enabled",
+ "Log modified configuration parameters affecting query planning.",
+ NULL,
+ &postgres_fdw_settings,
+ false,
+ PGC_USERSET,
+ 0,
+ NULL,
+ NULL,
+ NULL);
+
+ DefineCustomBoolVariable("postgres_fdw.verbose_enabled",
+ "Use EXPLAIN VERBOSE for plan logging.",
+ NULL,
+ &postgres_fdw_verbose,
+ false,
+ PGC_SUSET,
+ 0,
+ NULL,
+ NULL,
+ NULL);
+
+ DefineCustomBoolVariable("postgres_fdw.buffers_enabled",
+ "Log buffers usage.",
+ NULL,
+ &postgres_fdw_buffers,
+ false,
+ PGC_SUSET,
+ 0,
+ NULL,
+ NULL,
+ NULL);
+
+ DefineCustomBoolVariable("postgres_fdw.wal_enabled",
+ "Log WAL usage.",
+ NULL,
+ &postgres_fdw_wal,
+ false,
+ PGC_SUSET,
+ 0,
+ NULL,
+ NULL,
+ NULL);
+
+ DefineCustomBoolVariable("postgres_fdw.triggers_enabled",
+ "Include trigger statistics in plans.",
+ "This has no effect unless analyze is also set.",
+ &postgres_fdw_triggers,
+ false,
+ PGC_SUSET,
+ 0,
+ NULL,
+ NULL,
+ NULL);
+
+ DefineCustomEnumVariable("postgres_fdw.format_enabled",
+ "EXPLAIN format to be used for plan logging.",
+ NULL,
+ &postgres_fdw_format,
+ EXPLAIN_FORMAT_TEXT,
+ format_options,
+ PGC_SUSET,
+ 0,
+ NULL,
+ NULL,
+ NULL);
+
+ DefineCustomBoolVariable("postgres_fdw.timing_enabled",
+ "Collect timing data, not just row counts.",
+ NULL,
+ &postgres_fdw_timing,
+ true,
+ PGC_SUSET,
+ 0,
+ NULL,
+ NULL,
+ NULL);
+}
+
+static void
+postgres_fdw_post_parse_analyze(ParseState *pstate, Query *query, JumbleState *jstate)
+{
+ if (prev_post_parse_analyze_hook)
+ prev_post_parse_analyze_hook(pstate, query, jstate);
+
+ if (query->utilityStmt != NULL && IsA(query->utilityStmt, ExplainStmt)) {
+ pg_fdw_parsed_explain_state = NULL;
+ ExplainStmt *stmt = (ExplainStmt *) query->utilityStmt;
+ pg_fdw_parsed_explain_state = ParseExplainStmtOptions(pstate, stmt->options);
+ }
+}
+
+/*
+ * ExecutorStart hook: start up logging if needed
+ */
+static void
+explain_ExecutorStart(QueryDesc *queryDesc, int eflags)
+{
+ if (auto_explain_enabled())
+ {
+ /* Enable per-node instrumentation iff analyze is required. */
+ if (postgres_fdw_analyze && (eflags & EXEC_FLAG_EXPLAIN_ONLY) == 0)
+ {
+ if (postgres_fdw_timing)
+ queryDesc->instrument_options |= INSTRUMENT_TIMER;
+ else
+ queryDesc->instrument_options |= INSTRUMENT_ROWS;
+ if (postgres_fdw_buffers)
+ queryDesc->instrument_options |= INSTRUMENT_BUFFERS;
+ if (postgres_fdw_wal)
+ queryDesc->instrument_options |= INSTRUMENT_WAL;
+ }
+ }
+
+ if (prev_ExecutorStart)
+ prev_ExecutorStart(queryDesc, eflags);
+ else
+ standard_ExecutorStart(queryDesc, eflags);
+
+ if (auto_explain_enabled())
+ {
+ /*
+ * Set up to track total elapsed time in ExecutorRun. Make sure the
+ * space is allocated in the per-query context so it will go away at
+ * ExecutorEnd.
+ */
+ if (queryDesc->totaltime == NULL)
+ {
+ MemoryContext oldcxt;
+
+ oldcxt = MemoryContextSwitchTo(queryDesc->estate->es_query_cxt);
+ queryDesc->totaltime = InstrAlloc(1, INSTRUMENT_ALL, false);
+ MemoryContextSwitchTo(oldcxt);
+ }
+ }
+}
+
+/*
+ * ExecutorRun hook: all we need do is track nesting depth
+ */
+static void
+explain_ExecutorRun(QueryDesc *queryDesc, ScanDirection direction,
+ uint64 count, bool execute_once)
+{
+ nesting_level++;
+ PG_TRY();
+ {
+ if (prev_ExecutorRun)
+ prev_ExecutorRun(queryDesc, direction, count);
+ else
+ standard_ExecutorRun(queryDesc, direction, count);
+
+ if (auto_explain_enabled()) {
+ // send explain plans
+ ExplainState *es = NewExplainState();
+
+ es->analyze = (queryDesc->instrument_options && postgres_fdw_analyze);
+ es->verbose = postgres_fdw_verbose;
+ es->buffers = (es->analyze && postgres_fdw_buffers);
+ es->wal = (es->analyze && postgres_fdw_wal);
+ es->timing = (es->analyze && postgres_fdw_timing);
+ es->summary = es->analyze;
+ /* No support for MEMORY */
+ /* es->memory = false; */
+ es->format = postgres_fdw_format;
+ es->settings = postgres_fdw_settings;
+
+ ExplainBeginOutput(es);
+ ExplainQueryParameters(es, queryDesc->params, log_parameter_max_length);
+ ExplainPrintPlan(es, queryDesc);
+ if (es->analyze && postgres_fdw_triggers)
+ ExplainPrintTriggers(es, queryDesc);
+ if (es->costs)
+ ExplainPrintJITSummary(es, queryDesc);
+ ExplainEndOutput(es);
+
+ /* Remove last line break */
+ if (es->str->len > 0 && es->str->data[es->str->len - 1] == '\n')
+ es->str->data[--es->str->len] = '\0';
+
+ /* Fix JSON to output an object */
+ if (postgres_fdw_format == EXPLAIN_FORMAT_JSON)
+ {
+ es->str->data[0] = '{';
+ es->str->data[es->str->len - 1] = '}';
+ }
+
+ /*
+ * Note: we rely on the existing logging of context or
+ * debug_query_string to identify just which statement is being
+ * reported. This isn't ideal but trying to do it here would
+ * often result in duplication.
+ */
+ ereport(LOG,
+ (errmsg("postgres_fdw_auto_explain_enabled:%d", postgres_fdw_auto_explain_enabled),
+ errhidestmt(true)));
+ ereport(LOG,
+ (errmsg("sending explain plan to caller:%s", es->str->data),
+ errhidestmt(true)));
+ // receive token from source server and add that in response so that no other code can impact this.
+ ereport(NOTICE,
+ (errmsg("postgres_fdw_explain_plan:%s", es->str->data),
+ errhidestmt(true)));
+ }
+ }
+ PG_FINALLY();
+ {
+ nesting_level--;
+ }
+ PG_END_TRY();
+}
+
+/*
+ * ExecutorFinish hook: all we need do is track nesting depth
+ */
+static void
+explain_ExecutorFinish(QueryDesc *queryDesc)
+{
+ nesting_level++;
+ PG_TRY();
+ {
+ if (prev_ExecutorFinish)
+ prev_ExecutorFinish(queryDesc);
+ else
+ standard_ExecutorFinish(queryDesc);
+ }
+ PG_FINALLY();
+ {
+ nesting_level--;
+ }
+ PG_END_TRY();
+}
+
+/*
+ * ExecutorEnd hook: log results if needed
+ */
+static void
+explain_ExecutorEnd(QueryDesc *queryDesc)
+{
+ if (prev_ExecutorEnd)
+ prev_ExecutorEnd(queryDesc);
+ else
+ standard_ExecutorEnd(queryDesc);
+}
+
+bool get_postgres_fdw_show_remote_explain_enabled()
+{
+ if (postgres_fdw_show_remote_explain_plans) {
+ /*
+ * Check if extension is loaded and we have required hooks initialized. This happens when postgres_fdw wasn't
+ * already loaded and this is first SQL who need to access foreign server.
+ */
+ if (!pg_fdw_load_initialized) {
+ ereport(ERROR,
+ (errmsg("postgres_fdw.show_remote_explain_plans is set but extension postgres_fdw is not loaded yet."),
+ errhidestmt(true)));
+ return false;
+ }
+ return true;
+ }
+ return false;
+}
\ No newline at end of file
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 3900522ccb..caf4264f0d 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -285,6 +285,22 @@ 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;
+
+-- run same queries with postgres_fdw.show_remote_explain_plans set on
+SET postgres_fdw.show_remote_explain_plans = on;
+-- single table without alias
+EXPLAIN (COSTS OFF) SELECT * FROM ft1 ORDER BY c3, c1 OFFSET 100 LIMIT 10;
+-- single table with alias - also test that tableoid sort is not pushed to remote side
+EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.c1, t1.tableoid OFFSET 100 LIMIT 10;
+-- whole-row reference
+EXPLAIN (VERBOSE, COSTS OFF) SELECT t1 FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
+-- with WHERE clause
+EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1' AND t1.c7 >= '1';
+-- with FOR UPDATE/SHARE
+EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = 101 FOR UPDATE;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = 102 FOR SHARE;
+SET postgres_fdw.show_remote_explain_plans = off;
+
-- Test forcing the remote server to produce sorted data for a merge join.
SET enable_hashjoin TO false;
SET enable_nestloop TO false;
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index a201ed3082..d3b2386572 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -181,28 +181,20 @@ static void ExplainYAMLLineStarting(ExplainState *es);
static void escape_yaml(StringInfo buf, const char *str);
static SerializeMetrics GetSerializationMetrics(DestReceiver *dest);
-
-
-/*
- * ExplainQuery -
- * execute an EXPLAIN command
- */
-void
-ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
- ParamListInfo params, DestReceiver *dest)
+ExplainState *
+ParseExplainStmtOptions(ParseState *pstate, List *options)
{
- ExplainState *es = NewExplainState();
- TupOutputState *tstate;
- JumbleState *jstate = NULL;
- Query *query;
- List *rewritten;
ListCell *lc;
bool timing_set = false;
bool buffers_set = false;
bool summary_set = false;
+ ExplainState *es = NewExplainState();
+
+ if (!options)
+ return es;
/* Parse options list. */
- foreach(lc, stmt->options)
+ foreach(lc, options)
{
DefElem *opt = (DefElem *) lfirst(lc);
@@ -287,18 +279,37 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
parser_errposition(pstate, opt->location)));
}
+ /* if the timing was not set explicitly, set default value */
+ es->timing = (timing_set) ? es->timing : es->analyze;
+
+ /* if the summary was not set explicitly, set default value */
+ es->summary = (summary_set) ? es->summary : es->analyze;
+
+ return es;
+}
+
+/*
+ * ExplainQuery -
+ * execute an EXPLAIN command
+ */
+void
+ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
+ ParamListInfo params, DestReceiver *dest)
+{
+ ExplainState *es;
+ TupOutputState *tstate;
+ JumbleState *jstate = NULL;
+ Query *query;
+ List *rewritten;
+
+ es = ParseExplainStmtOptions(pstate, stmt->options);
+
/* check that WAL is used with EXPLAIN ANALYZE */
if (es->wal && !es->analyze)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("EXPLAIN option %s requires ANALYZE", "WAL")));
- /* if the timing was not set explicitly, set default value */
- es->timing = (timing_set) ? es->timing : es->analyze;
-
- /* if the buffers was not set explicitly, set default value */
- es->buffers = (buffers_set) ? es->buffers : es->analyze;
-
/* check that timing is used with EXPLAIN ANALYZE */
if (es->timing && !es->analyze)
ereport(ERROR,
@@ -317,9 +328,6 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("EXPLAIN options ANALYZE and GENERIC_PLAN cannot be used together")));
- /* if the summary was not set explicitly, set default value */
- es->summary = (summary_set) ? es->summary : es->analyze;
-
query = castNode(Query, stmt->query);
if (IsQueryIdEnabled())
jstate = JumbleQuery(query);
diff --git a/src/include/commands/explain.h b/src/include/commands/explain.h
index aa5872bc15..d1faac97e8 100644
--- a/src/include/commands/explain.h
+++ b/src/include/commands/explain.h
@@ -144,4 +144,6 @@ extern void ExplainCloseGroup(const char *objtype, const char *labelname,
extern DestReceiver *CreateExplainSerializeDestReceiver(ExplainState *es);
+extern ExplainState *ParseExplainStmtOptions(ParseState *pstate, List *options);
+
#endif /* EXPLAIN_H */
diff --git a/src/include/pg_config_ext.h b/src/include/pg_config_ext.h
new file mode 100644
index 0000000000..b4c07dd857
--- /dev/null
+++ b/src/include/pg_config_ext.h
@@ -0,0 +1,8 @@
+/* src/include/pg_config_ext.h. Generated from pg_config_ext.h.in by configure. */
+/*
+ * src/include/pg_config_ext.h.in. This is generated manually, not by
+ * autoheader, since we want to limit which symbols get defined here.
+ */
+
+/* Define to the name of a signed 64-bit integer type. */
+#define PG_INT64_TYPE long int
diff --git a/src/include/stamp-ext-h b/src/include/stamp-ext-h
new file mode 100644
index 0000000000..8b13789179
--- /dev/null
+++ b/src/include/stamp-ext-h
@@ -0,0 +1 @@
+
--
2.40.1
Hi,
Thanks for working on this patch!
I looked at the patch from and I have several comments. There are
some others, but wanted to start with the most important I found, in order
of importance.
1/ The use of NOTICE to propagate the explain plan.
I see the message content is checked, but this does not look robust
and could lead to
some strange results if another ExecutorRun hook emits a similar notice message.
+ // We might receive plans per batch of cursor, but we only need
to store one.
+ // do we really need to handle len==0. report warn if we still
recived. have test around this warn.
+ if (strstr(notice, "postgres_fdw_explain_plan") &&
explain_plans->len == 0) {2/ The current patch requires that the remote side has postgres_fdw
enabled. This seems very much
against the philosophy of FDWs. Only the side which creates the
foreign tables should require
the extension to be installed.
Also, if auto_explain_plan is enabled on the foreign table side, it
seems the explain_ExecutorRun
is exercised. This code path should only be taken on the remote side. right?
3/ ExecutorRun is the wrong place to send the plans from, because
postgres_fdw performs
FETCHES from a SQL declared cursor, and each fetch will hit
ExecutorRun. If you return the
first plan from ExecutorRun and stop consuming the rest of the plans,
you will only get the
results from a single fetch only. The plan should be generated and
sent back at CLOSE cursor time.
4/ As far as presenting the remote plans, I think adding them inline
in the EXPLAIN output
will make the plans hard to read, especially fas the plans become more complex.
What about they get added to a new section called "remote plans" and
the remote plans will
be identified by the plan_node_id, which we can add.
Below is a sketch-up to make it clear what I am thinking.
"""
Sort (cost=1..10 rows=10 width=120) (actual time=1..10 rows=10 loops=1)
-> Foreign Scan on prices (cost=100.00..200.00 rows=10width=59)
(actual time=1..100 rows=10 loops=1) (node=1)
Planning Time: 10 ms
Execution Time: 100 ms
Remote Plans
---------------
node 1:
Seq Scan on prices (cost=100.00..4576146.22 rows=467980 width=59)
(actual time=8737.505..2258486.086 rows=31752 loops=1)
"""
Here is a thought about how to generate and consume the plans.
What if we do something like a new EXPLAIN option which returns all the rows
back to the client, and then writes out the plan to some local memory. We would
then be able to fetch the last plan through a sql function, i.e.
SELECT pg_last_explain().
This may have applications beyond postgre_fdw; but in the case of
postgres_fdw, it will
call the remote sql using this EXPLAIN option and at the end of
execution, it will be
responsible to fetch the plans from pg_last_explain. I Have not fully
formulated this idea,
but wanted to share it.
Regards,
Sami Imseih
Amazon Web Services (AWS)
On Wed, 2025-02-26 at 13:13 -0600, Sami Imseih wrote:
1/ The use of NOTICE to propagate the explain plan.
I see the message content is checked, but this does not look robust
and could lead to
some strange results if another ExecutorRun hook emits a similar
notice message.
Fundamentally, EXPLAIN ANALYZE needs to return two result sets for this
patch to work: the ordinary result, and the EXPLAIN ANALYZE result. The
current patch hacks around that by returning the ordinary result set
from the foreign server, and then returning the EXPLAIN ANALYZE result
as a NOTICE.
Ideally, we'd have EXPLAIN ANALYZE return two result sets, kind of like
how a query with a semicolon returns two result sets. That changes the
expected message flow for EXPLAIN ANALYZE, though, so we'd need a new
option so we are sure the client is expecting it (is this a sane
idea?). I wonder if Robert's extensible EXPLAIN work[1]/messages/by-id/CA+TgmoYSzg58hPuBmei46o8D3SKX+SZoO4K_aGQGwiRzvRApLg@mail.gmail.com could be useful
here? We'd also need a DestReceiver capable of returning two result
sets. These problems sound solvable, but would require some more
discussion.
What if we do something like a new EXPLAIN option which returns all
the rows
back to the client, and then writes out the plan to some local
memory.
That's another idea, but I am starting to think returning two result
sets from EXPLAIN ANALYZE would be generally useful.
Regards,
Jeff Davis
[1]: /messages/by-id/CA+TgmoYSzg58hPuBmei46o8D3SKX+SZoO4K_aGQGwiRzvRApLg@mail.gmail.com
/messages/by-id/CA+TgmoYSzg58hPuBmei46o8D3SKX+SZoO4K_aGQGwiRzvRApLg@mail.gmail.com
Jeff Davis <pgsql@j-davis.com> writes:
Ideally, we'd have EXPLAIN ANALYZE return two result sets, kind of like
how a query with a semicolon returns two result sets. That changes the
expected message flow for EXPLAIN ANALYZE, though, so we'd need a new
option so we are sure the client is expecting it (is this a sane
idea?).
I'm afraid not. That pretty fundamentally breaks the wire protocol,
I think. Also (1) there could be more than two, no, if the query
touches more than one foreign table? How would the client know
how many to expect? (2) there would be no particularly compelling
ordering for the multiple resultsets.
I wonder if Robert's extensible EXPLAIN work[1] could be useful
here?
I'm wondering the same. You could certainly imagine cramming
all of the foreign EXPLAIN output into some new field attached
to the ForeignScan node. Readability and indentation would
require some thought, but the other approaches don't have any
mechanism for addressing that at all.
regards, tom lane
What if we do something like a new EXPLAIN option which returns all
the rows
back to the client, and then writes out the plan to some local
memory.
That's another idea, but I am starting to think returning two result
sets from EXPLAIN ANALYZE would be generally useful.
I did not think that would be doable. Because a
ForeignScanNode for postgres_fdw is a DECLARE CURSOR
followed by a serious of FETCH statements and finally a CLOSE,
I suspect we can store the plan in memory when the cursor is closed
and then it's up to the fdw to call a remote sql to fetch the plan to the
other side to append it on top of the explain output.
I also thought about 2 options 1/ new EXPLAIN option to do this -or-
2/ put in core GUCs to allow storing the last plan in memory at
ExecutorEnd.
I wonder if Robert's extensible EXPLAIN work[1] could be useful
here?
I'm wondering the same. You could certainly imagine cramming
all of the foreign EXPLAIN output into some new field attached
to the ForeignScan node. Readability and indentation would
require some thought, but the other approaches don't have any
mechanism for addressing that at al
FWIW, I had the same thought [0]/messages/by-id/CAA5RZ0tLrNOw-OgPkv49kbNmZS4nFn9vzpN5HXX_xvOaM9=5ww@mail.gmail.com and planned on doing the investigation.
[0]: /messages/by-id/CAA5RZ0tLrNOw-OgPkv49kbNmZS4nFn9vzpN5HXX_xvOaM9=5ww@mail.gmail.com
--
Sami Imseih
On Wed, 2025-03-05 at 14:12 -0500, Tom Lane wrote:
I'm afraid not. That pretty fundamentally breaks the wire protocol,
I think.
The extended protocol docs say: "The possible responses to Execute are
the same as those described above for queries issued via simple query
protocol, except that Execute doesn't cause ReadyForQuery or
RowDescription to be issued."
Each result set needs a RowDescription, so I think you're right that it
breaks the extended protocol. I missed that the first time.
Regards,
Jeff Davis
Hi Sami,
Thanks for the feedback. I have refactored the commit on the latest version
of PG and added a few more tests. To simplify the roll out of this feature,
I decided to work on analyze=false use case first. Please find the attached
patch for the same.
Regards,
Dinesh
On Thu, Mar 6, 2025 at 4:54 AM Jeff Davis <pgsql@j-davis.com> wrote:
On Wed, 2025-03-05 at 14:12 -0500, Tom Lane wrote:
I'm afraid not. That pretty fundamentally breaks the wire protocol,
I think.The extended protocol docs say: "The possible responses to Execute are
the same as those described above for queries issued via simple query
protocol, except that Execute doesn't cause ReadyForQuery or
RowDescription to be issued."Each result set needs a RowDescription, so I think you're right that it
breaks the extended protocol. I missed that the first time.Regards,
Jeff Davis
--
Regards,
Dinesh Salve
Cell:- 91 8125898845
Attachments:
0001-This-change-adds-capability-to-fetch-explain-plans-f.patchapplication/octet-stream; name=0001-This-change-adds-capability-to-fetch-explain-plans-f.patchDownload
From b5908a7ac4ea6f40493cb0d5638d6a254a4fb768 Mon Sep 17 00:00:00 2001
From: Dinesh Salve <salved@amazon.com>
Date: Wed, 5 Nov 2025 05:49:11 +0000
Subject: [PATCH 1/1] This change adds capability to fetch explain plans for
foreign tables. We have introduced new option "remote_plans" to achieve the
same. This option does not work with ANALYZE option yet.
---
.../postgres_fdw/expected/postgres_fdw.out | 581 ++++++++++++++++++
contrib/postgres_fdw/option.c | 71 +++
contrib/postgres_fdw/postgres_fdw.c | 256 +++++++-
contrib/postgres_fdw/postgres_fdw.h | 26 +
contrib/postgres_fdw/sql/postgres_fdw.sql | 39 ++
5 files changed, 964 insertions(+), 9 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index cd28126049d..5496a6ddea5 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -441,6 +441,173 @@ SELECT 'fixed', NULL FROM ft1 t1 WHERE c1 = 1;
fixed |
(1 row)
+-- with WHERE clause and remote_plans with different formats
+EXPLAIN (REMOTE_PLANS, FORMAT YAML, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------
+ - Plan: +
+ Node Type: "Foreign Scan" +
+ Operation: "Select" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Relation Name: "ft1" +
+ Schema: "public" +
+ Alias: "t1" +
+ Disabled: false +
+ Output: +
+ - "c1" +
+ - "c2" +
+ - "c3" +
+ - "c4" +
+ - "c5" +
+ - "c6" +
+ - "c7" +
+ - "c8" +
+ Remote SQL: "SELECT \"C 1\", c2, c3, c4, c5, c6, c7, c8 FROM \"S 1\".\"T 1\" WHERE ((\"C 1\" = 101))"+
+ Plan Node ID: 0 +
+ Remote Plans: +
+ Plan Node ID 0: +
+ - Plan: +
+ Node Type: "Index Scan" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Scan Direction: "Forward" +
+ Index Name: "t1_pkey" +
+ Relation Name: "T 1" +
+ Schema: "S 1" +
+ Alias: "T 1" +
+ Disabled: false +
+ Output: +
+ - "\"C 1\"" +
+ - "c2" +
+ - "c3" +
+ - "c4" +
+ - "c5" +
+ - "c6" +
+ - "c7" +
+ - "c8" +
+ Index Cond: "(\"T 1\".\"C 1\" = 101)"
+(1 row)
+
+EXPLAIN (REMOTE_PLANS TRUE, FORMAT XML, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------
+ <explain xmlns="http://www.postgresql.org/2009/explain"> +
+ <Query> +
+ <Plan> +
+ <Node-Type>Foreign Scan</Node-Type> +
+ <Operation>Select</Operation> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Relation-Name>ft1</Relation-Name> +
+ <Schema>public</Schema> +
+ <Alias>t1</Alias> +
+ <Disabled>false</Disabled> +
+ <Output> +
+ <Item>c1</Item> +
+ <Item>c2</Item> +
+ <Item>c3</Item> +
+ <Item>c4</Item> +
+ <Item>c5</Item> +
+ <Item>c6</Item> +
+ <Item>c7</Item> +
+ <Item>c8</Item> +
+ </Output> +
+ <Remote-SQL>SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 101))</Remote-SQL>+
+ <Plan-Node-ID>0</Plan-Node-ID> +
+ </Plan> +
+ <Remote-Plans> +
+ <Plan-Node-ID-0> +
+ <explain xmlns="http://www.postgresql.org/2009/explain"> +
+ <Query> +
+ <Plan> +
+ <Node-Type>Index Scan</Node-Type> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Scan-Direction>Forward</Scan-Direction> +
+ <Index-Name>t1_pkey</Index-Name> +
+ <Relation-Name>T 1</Relation-Name> +
+ <Schema>S 1</Schema> +
+ <Alias>T 1</Alias> +
+ <Disabled>false</Disabled> +
+ <Output> +
+ <Item>"C 1"</Item> +
+ <Item>c2</Item> +
+ <Item>c3</Item> +
+ <Item>c4</Item> +
+ <Item>c5</Item> +
+ <Item>c6</Item> +
+ <Item>c7</Item> +
+ <Item>c8</Item> +
+ </Output> +
+ <Index-Cond>("T 1"."C 1" = 101)</Index-Cond> +
+ </Plan> +
+ </Query> +
+ </explain> +
+ </Plan-Node-ID-0> +
+ </Remote-Plans> +
+ </Query> +
+ </explain>
+(1 row)
+
+EXPLAIN (REMOTE_PLANS, FORMAT JSON, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Foreign Scan", +
+ "Operation": "Select", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Relation Name": "ft1", +
+ "Schema": "public", +
+ "Alias": "t1", +
+ "Disabled": false, +
+ "Output": ["c1", "c2", "c3", "c4", "c5", "c6", "c7", "c8"], +
+ "Remote SQL": "SELECT \"C 1\", c2, c3, c4, c5, c6, c7, c8 FROM \"S 1\".\"T 1\" WHERE ((\"C 1\" = 101))",+
+ "Plan Node ID": 0 +
+ }, +
+ "Remote Plans": { +
+ "Plan Node ID 0": [ +
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Index Scan", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Scan Direction": "Forward", +
+ "Index Name": "t1_pkey", +
+ "Relation Name": "T 1", +
+ "Schema": "S 1", +
+ "Alias": "T 1", +
+ "Disabled": false, +
+ "Output": ["\"C 1\"", "c2", "c3", "c4", "c5", "c6", "c7", "c8"], +
+ "Index Cond": "(\"T 1\".\"C 1\" = 101)" +
+ } +
+ } +
+ ] +
+ ] +
+ } +
+ } +
+ ]
+(1 row)
+
+EXPLAIN (REMOTE_PLANS TRUE, FORMAT TEXT, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------
+ Foreign Scan on public.ft1 t1
+ Output: c1, c2, c3, c4, c5, c6, c7, c8
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 101))
+ Plan Node ID: 0
+ Remote Plans:
+ -------------
+ Plan Node ID 0:
+ Index Scan using t1_pkey on "S 1"."T 1"
+ Output: "C 1", c2, c3, c4, c5, c6, c7, c8
+ Index Cond: ("T 1"."C 1" = 101)
+(10 rows)
+
-- Test forcing the remote server to produce sorted data for a merge join.
SET enable_hashjoin TO false;
SET enable_nestloop TO false;
@@ -5086,6 +5253,373 @@ SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
Remote SQL: SELECT r5."C 1", r6.c1 FROM ("S 1"."T 1" r5 INNER JOIN "S 1"."T 3" r6 ON (((r5."C 1" = r6.c1)))) ORDER BY r5."C 1" ASC NULLS LAST
(13 rows)
+-- EXPLAIN remote_plans
+EXPLAIN (remote_plans, format text, costs off, analyze)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+ERROR: EXPLAIN options REMOTE_PLANS and ANALYZE cannot be used together
+EXPLAIN (remote_plans, format text, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+ QUERY PLAN
+-------------------------------------------------------
+ Limit
+ Plan Node ID: 0
+ -> Merge Semi Join
+ Merge Cond: (ft1.c1 = ft2_1.c1)
+ Plan Node ID: 1
+ -> Foreign Scan
+ Relations: (ft1) INNER JOIN (ft2)
+ Plan Node ID: 2
+ -> Foreign Scan
+ Relations: (ft2 ft2_1) INNER JOIN (ft4)
+ Plan Node ID: 3
+ Remote Plans:
+ -------------
+ Plan Node ID 2:
+ Index Only Scan using t1_pkey on "T 1" r2
+ Plan Node ID 3:
+ Merge Join
+ Merge Cond: (r5."C 1" = r6.c1)
+ -> Index Only Scan using t1_pkey on "T 1" r5
+ -> Sort
+ Sort Key: r6.c1
+ -> Seq Scan on "T 3" r6
+(22 rows)
+
+EXPLAIN (remote_plans, format xml, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+ QUERY PLAN
+------------------------------------------------------------------------
+ <explain xmlns="http://www.postgresql.org/2009/explain"> +
+ <Query> +
+ <Plan> +
+ <Node-Type>Limit</Node-Type> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Disabled>false</Disabled> +
+ <Plan-Node-ID>0</Plan-Node-ID> +
+ <Plans> +
+ <Plan> +
+ <Node-Type>Merge Join</Node-Type> +
+ <Parent-Relationship>Outer</Parent-Relationship> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Join-Type>Semi</Join-Type> +
+ <Disabled>false</Disabled> +
+ <Inner-Unique>false</Inner-Unique> +
+ <Merge-Cond>(ft1.c1 = ft2_1.c1)</Merge-Cond> +
+ <Plan-Node-ID>1</Plan-Node-ID> +
+ <Plans> +
+ <Plan> +
+ <Node-Type>Foreign Scan</Node-Type> +
+ <Operation>Select</Operation> +
+ <Parent-Relationship>Outer</Parent-Relationship> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Disabled>false</Disabled> +
+ <Relations>(ft1) INNER JOIN (ft2)</Relations> +
+ <Plan-Node-ID>2</Plan-Node-ID> +
+ </Plan> +
+ <Plan> +
+ <Node-Type>Foreign Scan</Node-Type> +
+ <Operation>Select</Operation> +
+ <Parent-Relationship>Inner</Parent-Relationship> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Disabled>false</Disabled> +
+ <Relations>(ft2 ft2_1) INNER JOIN (ft4)</Relations> +
+ <Plan-Node-ID>3</Plan-Node-ID> +
+ </Plan> +
+ </Plans> +
+ </Plan> +
+ </Plans> +
+ </Plan> +
+ <Remote-Plans> +
+ <Plan-Node-ID-2> +
+ <explain xmlns="http://www.postgresql.org/2009/explain"> +
+ <Query> +
+ <Plan> +
+ <Node-Type>Index Only Scan</Node-Type> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Scan-Direction>Forward</Scan-Direction> +
+ <Index-Name>t1_pkey</Index-Name> +
+ <Relation-Name>T 1</Relation-Name> +
+ <Alias>r2</Alias> +
+ <Disabled>false</Disabled> +
+ </Plan> +
+ </Query> +
+ </explain> +
+ </Plan-Node-ID-2> +
+ <Plan-Node-ID-3> +
+ <explain xmlns="http://www.postgresql.org/2009/explain"> +
+ <Query> +
+ <Plan> +
+ <Node-Type>Merge Join</Node-Type> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Join-Type>Inner</Join-Type> +
+ <Disabled>false</Disabled> +
+ <Inner-Unique>true</Inner-Unique> +
+ <Merge-Cond>(r5."C 1" = r6.c1)</Merge-Cond> +
+ <Plans> +
+ <Plan> +
+ <Node-Type>Index Only Scan</Node-Type> +
+ <Parent-Relationship>Outer</Parent-Relationship> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Scan-Direction>Forward</Scan-Direction> +
+ <Index-Name>t1_pkey</Index-Name> +
+ <Relation-Name>T 1</Relation-Name> +
+ <Alias>r5</Alias> +
+ <Disabled>false</Disabled> +
+ </Plan> +
+ <Plan> +
+ <Node-Type>Sort</Node-Type> +
+ <Parent-Relationship>Inner</Parent-Relationship> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Disabled>false</Disabled> +
+ <Sort-Key> +
+ <Item>r6.c1</Item> +
+ </Sort-Key> +
+ <Plans> +
+ <Plan> +
+ <Node-Type>Seq Scan</Node-Type> +
+ <Parent-Relationship>Outer</Parent-Relationship>+
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Relation-Name>T 3</Relation-Name> +
+ <Alias>r6</Alias> +
+ <Disabled>false</Disabled> +
+ </Plan> +
+ </Plans> +
+ </Plan> +
+ </Plans> +
+ </Plan> +
+ </Query> +
+ </explain> +
+ </Plan-Node-ID-3> +
+ </Remote-Plans> +
+ </Query> +
+ </explain>
+(1 row)
+
+EXPLAIN (remote_plans, format json, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+ QUERY PLAN
+------------------------------------------------------------
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Limit", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Disabled": false, +
+ "Plan Node ID": 0, +
+ "Plans": [ +
+ { +
+ "Node Type": "Merge Join", +
+ "Parent Relationship": "Outer", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Join Type": "Semi", +
+ "Disabled": false, +
+ "Inner Unique": false, +
+ "Merge Cond": "(ft1.c1 = ft2_1.c1)", +
+ "Plan Node ID": 1, +
+ "Plans": [ +
+ { +
+ "Node Type": "Foreign Scan", +
+ "Operation": "Select", +
+ "Parent Relationship": "Outer", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Disabled": false, +
+ "Relations": "(ft1) INNER JOIN (ft2)", +
+ "Plan Node ID": 2 +
+ }, +
+ { +
+ "Node Type": "Foreign Scan", +
+ "Operation": "Select", +
+ "Parent Relationship": "Inner", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Disabled": false, +
+ "Relations": "(ft2 ft2_1) INNER JOIN (ft4)",+
+ "Plan Node ID": 3 +
+ } +
+ ] +
+ } +
+ ] +
+ }, +
+ "Remote Plans": { +
+ "Plan Node ID 2": [ +
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Index Only Scan", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Scan Direction": "Forward", +
+ "Index Name": "t1_pkey", +
+ "Relation Name": "T 1", +
+ "Alias": "r2", +
+ "Disabled": false +
+ } +
+ } +
+ ] +
+ ], +
+ "Plan Node ID 3": [ +
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Merge Join", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Join Type": "Inner", +
+ "Disabled": false, +
+ "Inner Unique": true, +
+ "Merge Cond": "(r5.\"C 1\" = r6.c1)", +
+ "Plans": [ +
+ { +
+ "Node Type": "Index Only Scan", +
+ "Parent Relationship": "Outer", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Scan Direction": "Forward", +
+ "Index Name": "t1_pkey", +
+ "Relation Name": "T 1", +
+ "Alias": "r5", +
+ "Disabled": false +
+ }, +
+ { +
+ "Node Type": "Sort", +
+ "Parent Relationship": "Inner", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Disabled": false, +
+ "Sort Key": ["r6.c1"], +
+ "Plans": [ +
+ { +
+ "Node Type": "Seq Scan", +
+ "Parent Relationship": "Outer", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Relation Name": "T 3", +
+ "Alias": "r6", +
+ "Disabled": false +
+ } +
+ ] +
+ } +
+ ] +
+ } +
+ } +
+ ] +
+ ] +
+ } +
+ } +
+ ]
+(1 row)
+
+EXPLAIN (remote_plans, format yaml, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+ QUERY PLAN
+-------------------------------------------------------
+ - Plan: +
+ Node Type: "Limit" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Disabled: false +
+ Plan Node ID: 0 +
+ Plans: +
+ - Node Type: "Merge Join" +
+ Parent Relationship: "Outer" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Join Type: "Semi" +
+ Disabled: false +
+ Inner Unique: false +
+ Merge Cond: "(ft1.c1 = ft2_1.c1)" +
+ Plan Node ID: 1 +
+ Plans: +
+ - Node Type: "Foreign Scan" +
+ Operation: "Select" +
+ Parent Relationship: "Outer" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Disabled: false +
+ Relations: "(ft1) INNER JOIN (ft2)" +
+ Plan Node ID: 2 +
+ - Node Type: "Foreign Scan" +
+ Operation: "Select" +
+ Parent Relationship: "Inner" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Disabled: false +
+ Relations: "(ft2 ft2_1) INNER JOIN (ft4)"+
+ Plan Node ID: 3 +
+ Remote Plans: +
+ Plan Node ID 2: +
+ - Plan: +
+ Node Type: "Index Only Scan" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Scan Direction: "Forward" +
+ Index Name: "t1_pkey" +
+ Relation Name: "T 1" +
+ Alias: "r2" +
+ Disabled: false +
+ Plan Node ID 3: +
+ - Plan: +
+ Node Type: "Merge Join" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Join Type: "Inner" +
+ Disabled: false +
+ Inner Unique: true +
+ Merge Cond: "(r5.\"C 1\" = r6.c1)" +
+ Plans: +
+ - Node Type: "Index Only Scan" +
+ Parent Relationship: "Outer" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Scan Direction: "Forward" +
+ Index Name: "t1_pkey" +
+ Relation Name: "T 1" +
+ Alias: "r5" +
+ Disabled: false +
+ - Node Type: "Sort" +
+ Parent Relationship: "Inner" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Disabled: false +
+ Sort Key: +
+ - "r6.c1" +
+ Plans: +
+ - Node Type: "Seq Scan" +
+ Parent Relationship: "Outer" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Relation Name: "T 3" +
+ Alias: "r6" +
+ Disabled: false
+(1 row)
+
-- ===================================================================
-- test writable foreign table stuff
-- ===================================================================
@@ -6303,6 +6837,25 @@ DELETE FROM ft2 WHERE c1 = 1200 RETURNING tableoid::regclass;
ft2
(1 row)
+-- test write on foreign tables with remote_plans
+EXPLAIN (remote_plans, verbose, costs off)
+UPDATE ft2 SET c2 = c2 + 300 WHERE c1 % 10 = 3;
+ QUERY PLAN
+---------------------------------------------------------------------------------------
+ Update on public.ft2
+ Plan Node ID: 0
+ -> Foreign Update on public.ft2
+ Remote SQL: UPDATE "S 1"."T 1" SET c2 = (c2 + 300) WHERE ((("C 1" % 10) = 3))
+ Plan Node ID: 1
+ Remote Plans:
+ -------------
+ Plan Node ID 1:
+ Update on "S 1"."T 1"
+ -> Seq Scan on "S 1"."T 1"
+ Output: (c2 + 300), ctid
+ Filter: (("T 1"."C 1" % 10) = 3)
+(12 rows)
+
-- Test UPDATE/DELETE with RETURNING on a three-table join
INSERT INTO ft2 (c1,c2,c3)
SELECT id, id - 1200, to_char(id, 'FM00000') FROM generate_series(1201, 1300) id;
@@ -12260,6 +12813,34 @@ SELECT * FROM insert_tbl ORDER BY a;
2505 | 505 | bar
(2 rows)
+EXPLAIN (REMOTE_PLANS, VERBOSE, COSTS OFF)
+INSERT INTO insert_tbl (SELECT * FROM local_tbl UNION ALL SELECT * FROM remote_tbl);
+ QUERY PLAN
+-------------------------------------------------------------------------
+ Insert on public.insert_tbl
+ Remote SQL: INSERT INTO public.base_tbl4(a, b, c) VALUES ($1, $2, $3)
+ Batch Size: 1
+ Plan Node ID: 0
+ -> Append
+ Plan Node ID: 1
+ -> Seq Scan on public.local_tbl
+ Output: local_tbl.a, local_tbl.b, local_tbl.c
+ Plan Node ID: 2
+ -> Async Foreign Scan on public.remote_tbl
+ Output: remote_tbl.a, remote_tbl.b, remote_tbl.c
+ Remote SQL: SELECT a, b, c FROM public.base_tbl3
+ Plan Node ID: 3
+ Remote Plans:
+ -------------
+ Plan Node ID 0:
+ Insert on public.base_tbl4
+ -> Result
+ Output: $1, $2, $3
+ Plan Node ID 3:
+ Seq Scan on public.base_tbl3
+ Output: a, b, c
+(22 rows)
+
-- Check with direct modify
EXPLAIN (VERBOSE, COSTS OFF)
WITH t AS (UPDATE remote_tbl SET c = c || c RETURNING *)
diff --git a/contrib/postgres_fdw/option.c b/contrib/postgres_fdw/option.c
index 04788b7e8b3..a3b87d2bbaf 100644
--- a/contrib/postgres_fdw/option.c
+++ b/contrib/postgres_fdw/option.c
@@ -17,6 +17,8 @@
#include "catalog/pg_foreign_table.h"
#include "catalog/pg_user_mapping.h"
#include "commands/defrem.h"
+#include "commands/explain.h"
+#include "commands/explain_state.h"
#include "commands/extension.h"
#include "libpq/libpq-be.h"
#include "postgres_fdw.h"
@@ -40,6 +42,13 @@ typedef struct PgFdwOption
*/
static PgFdwOption *postgres_fdw_options;
+/*
+ * EXPLAIN hooks
+ */
+static explain_per_node_hook_type prev_explain_per_node_hook;
+static explain_per_plan_hook_type prev_explain_per_plan_hook;
+static explain_validate_options_hook_type prev_explain_validate_options_hook;
+
/*
* GUC parameters
*/
@@ -561,6 +570,57 @@ process_pgfdw_appname(const char *appname)
return buf.data;
}
+/*
+ * Get the PgFdwExplainState structure from an ExplainState; if there is
+ * none, create one, attach it to the ExplainState, and return it.
+ */
+static PgFdwExplainState *
+pgfdw_ensure_options(ExplainState *es)
+{
+ PgFdwExplainState *pgfdw_explain_state;
+
+ pgfdw_explain_state = GetExplainExtensionState(es, GetExplainExtensionId("postgres_fdw"));
+
+ if (pgfdw_explain_state == NULL)
+ {
+ pgfdw_explain_state = palloc0(sizeof(PgFdwExplainState));
+ SetExplainExtensionState(es, GetExplainExtensionId("postgres_fdw"), pgfdw_explain_state);
+ pgfdw_explain_state->all_remote_plans = NIL;
+ }
+
+ return pgfdw_explain_state;
+}
+
+/*
+ * Parse handler for EXPLAIN (REMOTE_PLANS).
+ */
+static void
+pgfdw_remote_plans_apply(ExplainState *es, DefElem *opt, ParseState *pstate)
+{
+ PgFdwExplainState *options = pgfdw_ensure_options(es);
+
+ options->remote_plans = defGetBoolean(opt);
+}
+
+static void
+postgresExplainValidateOptions(ExplainState *es, List *options, ParseState *pstate)
+{
+ ListCell *lc;
+
+ foreach(lc, options)
+ {
+ DefElem *opt = (DefElem *) lfirst(lc);
+
+ if (strcmp(opt->defname, "remote_plans") == 0)
+ {
+ if (defGetBoolean(opt) && es->analyze)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("EXPLAIN options REMOTE_PLANS and ANALYZE cannot be used together"));
+ }
+ }
+}
+
/*
* Module load callback
*/
@@ -587,4 +647,15 @@ _PG_init(void)
NULL);
MarkGUCPrefixReserved("postgres_fdw");
+
+ RegisterExtensionExplainOption("remote_plans", pgfdw_remote_plans_apply);
+
+ /* per node EXPLAIN hook */
+ prev_explain_per_node_hook = explain_per_node_hook;
+ explain_per_node_hook = postgresExplainPerNode;
+ prev_explain_per_plan_hook = explain_per_plan_hook;
+ explain_per_plan_hook = postgresExplainPerPlan;
+ prev_explain_validate_options_hook = explain_validate_options_hook;
+ explain_validate_options_hook = postgresExplainValidateOptions;
+
}
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 06b52c65300..9b99a2386b3 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -43,6 +43,7 @@
#include "utils/builtins.h"
#include "utils/float.h"
#include "utils/guc.h"
+#include "utils/json.h"
#include "utils/lsyscache.h"
#include "utils/memutils.h"
#include "utils/rel.h"
@@ -133,6 +134,20 @@ enum FdwDirectModifyPrivateIndex
FdwDirectModifyPrivateSetProcessed,
};
+static const char *const explain_formats[] = {
+ [EXPLAIN_FORMAT_TEXT] = "TEXT",
+ [EXPLAIN_FORMAT_JSON] = "JSON",
+ [EXPLAIN_FORMAT_XML] = "XML",
+ [EXPLAIN_FORMAT_YAML] = "YAML",
+};
+
+/*
+ * Track the extension id in the backend.
+ */
+static int extension_id = -1;
+#define GET_EXTENSION_ID() ((extension_id == -1) ? \
+ GetExplainExtensionId("postgres_fdw"): extension_id)
+
/*
* Execution state of a foreign scan using postgres_fdw.
*/
@@ -2822,6 +2837,65 @@ postgresEndDirectModify(ForeignScanState *node)
/* MemoryContext will be deleted automatically. */
}
+static void
+postgresExplainStatement(int plan_node_id,
+ ExplainState *es,
+ PgFdwExplainState * pgfdw_explain_state,
+ PGconn *conn,
+ char *sql)
+{
+ PGresult *volatile res = NULL;
+ StringInfoData explain_sql;
+
+ PG_TRY();
+ {
+ int numrows,
+ i;
+ PgFdwExplainRemotePlans *explain = (PgFdwExplainRemotePlans *) palloc(sizeof(PgFdwExplainRemotePlans));
+
+ initStringInfo(&explain_sql);
+ initStringInfo(&explain->explain_plan);
+
+ appendStringInfo(&explain_sql, "EXPLAIN (\
+ GENERIC_PLAN 1, \
+ FORMAT %s, \
+ VERBOSE %d, \
+ COSTS %d, \
+ SETTINGS %d) \
+ %s",
+ explain_formats[es->format],
+ (es->verbose) ? 1 : 0,
+ (es->costs) ? 1 : 0,
+ (es->settings) ? 1 : 0,
+ sql);
+
+ /* Run the query and collect the remote plan */
+ res = pgfdw_exec_query(conn, explain_sql.data, NULL);
+ if (PQresultStatus(res) != PGRES_TUPLES_OK)
+ pgfdw_report_error(res, conn, explain_sql.data);
+
+ numrows = PQntuples(res);
+
+ for (i = 0; i < numrows; i++)
+ appendStringInfo(&explain->explain_plan, "%s\n", pstrdup(PQgetvalue(res, i, 0)));
+
+ if (explain->explain_plan.len > 0 && explain->explain_plan.data[explain->explain_plan.len - 1] == '\n')
+ explain->explain_plan.data[--explain->explain_plan.len] = '\0';
+
+ explain->plan_node_id = plan_node_id;
+ pgfdw_explain_state->all_remote_plans = lappend(pgfdw_explain_state->all_remote_plans, explain);
+ }
+ PG_FINALLY();
+ {
+ if (res)
+ PQclear(res);
+
+ if (explain_sql.data)
+ pfree(explain_sql.data);
+ }
+ PG_END_TRY();
+}
+
/*
* postgresExplainForeignScan
* Produce extra output for EXPLAIN of a ForeignScan on a foreign table
@@ -2831,6 +2905,9 @@ postgresExplainForeignScan(ForeignScanState *node, ExplainState *es)
{
ForeignScan *plan = castNode(ForeignScan, node->ss.ps.plan);
List *fdw_private = plan->fdw_private;
+ PgFdwExplainState *pgfdw_explain_state;
+ char *sql;
+ List *foreign_scan_table = NIL;
/*
* Identify foreign scans that are really joins or upper relations. The
@@ -2892,6 +2969,14 @@ postgresExplainForeignScan(ForeignScanState *node, ExplainState *es)
Assert(rte->rtekind == RTE_RELATION);
/* This logic should agree with explain.c's ExplainTargetRel */
relname = get_rel_name(rte->relid);
+
+ /*
+ * add one of the tables to foreign_scan_table to get the
+ * serverId for remote plans
+ */
+ if (list_length(foreign_scan_table) == 0)
+ foreign_scan_table = lappend_oid(foreign_scan_table, rte->relid);
+
if (es->verbose)
{
char *namespace;
@@ -2917,15 +3002,38 @@ postgresExplainForeignScan(ForeignScanState *node, ExplainState *es)
ExplainPropertyText("Relations", relations.data, es);
}
+ sql = strVal(list_nth(fdw_private, FdwScanPrivateSelectSql));
+
/*
* Add remote query, when VERBOSE option is specified.
*/
if (es->verbose)
+ ExplainPropertyText("Remote SQL", sql, es);
+
+ pgfdw_explain_state = GetExplainExtensionState(es, GET_EXTENSION_ID());
+
+ if (pgfdw_explain_state && pgfdw_explain_state->remote_plans)
{
- char *sql;
+ UserMapping *user = NULL;
+ PGconn *conn = NULL;
+ ForeignTable *table;
- sql = strVal(list_nth(fdw_private, FdwScanPrivateSelectSql));
- ExplainPropertyText("Remote SQL", sql, es);
+ if (node && !node->ss.ss_currentRelation &&
+ foreign_scan_table == NIL)
+ return;
+
+ if (node && node->ss.ss_currentRelation)
+ table = GetForeignTable(RelationGetRelid(node->ss.ss_currentRelation));
+ else
+ table = GetForeignTable(list_nth_oid(foreign_scan_table, 0));
+
+ Assert(table);
+
+ user = GetUserMapping(GetUserId(), table->serverid);
+ conn = GetConnection(user, false, NULL);
+
+ postgresExplainStatement(node->ss.ps.plan->plan_node_id, es, pgfdw_explain_state, conn, sql);
+ ReleaseConnection(conn);
}
}
@@ -2940,11 +3048,12 @@ postgresExplainForeignModify(ModifyTableState *mtstate,
int subplan_index,
ExplainState *es)
{
+ char *sql = strVal(list_nth(fdw_private,
+ FdwModifyPrivateUpdateSql));
+ PgFdwExplainState *pgfdw_explain_state;
+
if (es->verbose)
{
- char *sql = strVal(list_nth(fdw_private,
- FdwModifyPrivateUpdateSql));
-
ExplainPropertyText("Remote SQL", sql, es);
/*
@@ -2954,6 +3063,24 @@ postgresExplainForeignModify(ModifyTableState *mtstate,
if (rinfo->ri_BatchSize > 0)
ExplainPropertyInteger("Batch Size", NULL, rinfo->ri_BatchSize, es);
}
+
+ pgfdw_explain_state = GetExplainExtensionState(es, GET_EXTENSION_ID());
+ if (pgfdw_explain_state && pgfdw_explain_state->remote_plans)
+ {
+ UserMapping *user = NULL;
+ PGconn *conn = NULL;
+ ForeignTable *table;
+
+ table = GetForeignTable(rinfo->ri_RelationDesc->rd_rel->oid);
+
+ Assert(table);
+
+ user = GetUserMapping(GetUserId(), table->serverid);
+ conn = GetConnection(user, false, NULL);
+
+ postgresExplainStatement(mtstate->ps.plan->plan_node_id, es, pgfdw_explain_state, conn, sql);
+ ReleaseConnection(conn);
+ }
}
/*
@@ -2966,12 +3093,31 @@ postgresExplainDirectModify(ForeignScanState *node, ExplainState *es)
{
List *fdw_private;
char *sql;
+ PgFdwExplainState *pgfdw_explain_state;
+
+ fdw_private = ((ForeignScan *) node->ss.ps.plan)->fdw_private;
+ sql = strVal(list_nth(fdw_private, FdwDirectModifyPrivateUpdateSql));
if (es->verbose)
- {
- fdw_private = ((ForeignScan *) node->ss.ps.plan)->fdw_private;
- sql = strVal(list_nth(fdw_private, FdwDirectModifyPrivateUpdateSql));
ExplainPropertyText("Remote SQL", sql, es);
+
+ pgfdw_explain_state = GetExplainExtensionState(es, GET_EXTENSION_ID());
+
+ if (pgfdw_explain_state && pgfdw_explain_state->remote_plans)
+ {
+ UserMapping *user = NULL;
+ PGconn *conn = NULL;
+ ForeignTable *table;
+
+ table = GetForeignTable(RelationGetRelid(node->ss.ss_currentRelation));
+
+ Assert(table);
+
+ user = GetUserMapping(GetUserId(), table->serverid);
+ conn = GetConnection(user, false, NULL);
+
+ postgresExplainStatement(node->ss.ps.plan->plan_node_id, es, pgfdw_explain_state, conn, sql);
+ ReleaseConnection(conn);
}
}
@@ -7886,3 +8032,95 @@ get_batch_size_option(Relation rel)
return batch_size;
}
+
+void
+postgresExplainPerNode(PlanState *planstate, List *ancestors,
+ const char *relationship, const char *plan_name,
+ ExplainState *es)
+{
+ PgFdwExplainState *pgfdw_explain_state;
+
+ pgfdw_explain_state = GetExplainExtensionState(es, GET_EXTENSION_ID());
+
+ if (pgfdw_explain_state == NULL ||
+ !pgfdw_explain_state->remote_plans)
+ return;
+
+ if (pgfdw_explain_state && pgfdw_explain_state->remote_plans)
+ ExplainPropertyInteger("Plan Node ID", NULL, planstate->plan->plan_node_id, es);
+}
+
+static void
+pgfdwFormatRemotePlan(PgFdwExplainRemotePlans * explain,
+ ExplainState *es,
+ int plan_node_id)
+{
+ char *token;
+ StringInfoData remote_plan_name;
+
+ initStringInfo(&remote_plan_name);
+ appendStringInfo(&remote_plan_name, "Plan Node ID %d", plan_node_id);
+
+ ExplainOpenGroup(remote_plan_name.data, remote_plan_name.data, false, es);
+
+ if (es->format == EXPLAIN_FORMAT_TEXT)
+ {
+ appendStringInfo(es->str, "Plan Node ID %d:", plan_node_id);
+ appendStringInfoString(es->str, "\n");
+ }
+
+ while ((token = strsep(&explain->explain_plan.data, "\n")) != NULL)
+ {
+ if (es->format == EXPLAIN_FORMAT_JSON ||
+ es->format == EXPLAIN_FORMAT_YAML)
+ appendStringInfoString(es->str, "\n");
+
+ appendStringInfoSpaces(es->str, (es->indent == 0) ? 2 : es->indent * 2);
+ appendStringInfoString(es->str, token);
+
+ if (es->format == EXPLAIN_FORMAT_XML ||
+ es->format == EXPLAIN_FORMAT_TEXT)
+ appendStringInfoString(es->str, "\n");
+ }
+
+ ExplainCloseGroup(remote_plan_name.data, remote_plan_name.data, false, es);
+ pfree(remote_plan_name.data);
+}
+
+void
+postgresExplainPerPlan(PlannedStmt *plannedstmt,
+ IntoClause *into,
+ ExplainState *es,
+ const char *queryString,
+ ParamListInfo params,
+ QueryEnvironment *queryEnv)
+{
+ ListCell *lc;
+ PgFdwExplainState *pgfdw_explain_state;
+
+ pgfdw_explain_state = GetExplainExtensionState(es, GET_EXTENSION_ID());
+
+ if (pgfdw_explain_state == NULL ||
+ pgfdw_explain_state->all_remote_plans == NIL ||
+ !pgfdw_explain_state->remote_plans)
+ return;
+
+ ExplainOpenGroup("Remote Plans", "Remote Plans", true, es);
+ if (es->format == EXPLAIN_FORMAT_TEXT)
+ {
+ appendStringInfo(es->str, "Remote Plans:\n");
+ appendStringInfo(es->str, "-------------\n");
+ }
+
+ /* Process every remote plan captured */
+ foreach(lc, pgfdw_explain_state->all_remote_plans)
+ {
+ PgFdwExplainRemotePlans *explain = (PgFdwExplainRemotePlans *) lfirst(lc);
+
+ pgfdwFormatRemotePlan(explain,
+ es,
+ explain->plan_node_id);
+ }
+
+ ExplainCloseGroup("Remote Plans", "Remote Plans", true, es);
+}
diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h
index e69735298d7..b135664b933 100644
--- a/contrib/postgres_fdw/postgres_fdw.h
+++ b/contrib/postgres_fdw/postgres_fdw.h
@@ -13,6 +13,7 @@
#ifndef POSTGRES_FDW_H
#define POSTGRES_FDW_H
+#include "commands/explain_state.h"
#include "foreign/foreign.h"
#include "lib/stringinfo.h"
#include "libpq/libpq-be-fe.h"
@@ -151,6 +152,21 @@ typedef enum PgFdwSamplingMethod
ANALYZE_SAMPLE_BERNOULLI, /* TABLESAMPLE bernoulli */
} PgFdwSamplingMethod;
+typedef struct PgFdwExplainRemotePlans
+{
+ int plan_node_id;
+ StringInfoData explain_plan;
+
+} PgFdwExplainRemotePlans;
+
+typedef struct PgFdwExplainState
+{
+ List *all_remote_plans;
+
+ /* EXPLAIN options */
+ bool remote_plans;
+} PgFdwExplainState;
+
/* in postgres_fdw.c */
extern int set_transmission_modes(void);
extern void reset_transmission_modes(int nestlevel);
@@ -178,6 +194,16 @@ extern int ExtractConnectionOptions(List *defelems,
extern List *ExtractExtensionList(const char *extensionsString,
bool warnOnMissing);
extern char *process_pgfdw_appname(const char *appname);
+extern void postgresExplainPerNode(PlanState *planstate, List *ancestors,
+ const char *relationship,
+ const char *plan_name,
+ ExplainState *es);
+extern void postgresExplainPerPlan(PlannedStmt *plannedstmt,
+ IntoClause *into,
+ ExplainState *es,
+ const char *queryString,
+ ParamListInfo params,
+ QueryEnvironment *queryEnv);
extern char *pgfdw_application_name;
/* in deparse.c */
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 9a8f9e28135..25823a7ebe7 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -281,6 +281,11 @@ SELECT * FROM ft1 t1 WHERE t1.c3 = (SELECT MAX(c3) FROM ft2 t2) ORDER BY c1;
WITH t1 AS (SELECT * FROM ft1 WHERE c1 <= 10) SELECT t2.c1, t2.c2, t2.c3, t2.c4 FROM t1, ft2 t2 WHERE t1.c1 = t2.c1 ORDER BY t1.c1;
-- fixed values
SELECT 'fixed', NULL FROM ft1 t1 WHERE c1 = 1;
+-- with WHERE clause and remote_plans with different formats
+EXPLAIN (REMOTE_PLANS, FORMAT YAML, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+EXPLAIN (REMOTE_PLANS TRUE, FORMAT XML, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+EXPLAIN (REMOTE_PLANS, FORMAT JSON, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+EXPLAIN (REMOTE_PLANS TRUE, FORMAT TEXT, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
-- Test forcing the remote server to produce sorted data for a merge join.
SET enable_hashjoin TO false;
SET enable_nestloop TO false;
@@ -1489,6 +1494,33 @@ SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
ORDER BY ft1.c1 LIMIT 5;
+-- EXPLAIN remote_plans
+EXPLAIN (remote_plans, format text, costs off, analyze)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+EXPLAIN (remote_plans, format text, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+EXPLAIN (remote_plans, format xml, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+EXPLAIN (remote_plans, format json, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+EXPLAIN (remote_plans, format yaml, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+
-- ===================================================================
-- test writable foreign table stuff
-- ===================================================================
@@ -1538,6 +1570,10 @@ EXPLAIN (verbose, costs off)
DELETE FROM ft2 WHERE c1 = 1200 RETURNING tableoid::regclass; -- can be pushed down
DELETE FROM ft2 WHERE c1 = 1200 RETURNING tableoid::regclass;
+-- test write on foreign tables with remote_plans
+EXPLAIN (remote_plans, verbose, costs off)
+UPDATE ft2 SET c2 = c2 + 300 WHERE c1 % 10 = 3;
+
-- Test UPDATE/DELETE with RETURNING on a three-table join
INSERT INTO ft2 (c1,c2,c3)
SELECT id, id - 1200, to_char(id, 'FM00000') FROM generate_series(1201, 1300) id;
@@ -4138,6 +4174,9 @@ INSERT INTO insert_tbl (SELECT * FROM local_tbl UNION ALL SELECT * FROM remote_t
SELECT * FROM insert_tbl ORDER BY a;
+EXPLAIN (REMOTE_PLANS, VERBOSE, COSTS OFF)
+INSERT INTO insert_tbl (SELECT * FROM local_tbl UNION ALL SELECT * FROM remote_tbl);
+
-- Check with direct modify
EXPLAIN (VERBOSE, COSTS OFF)
WITH t AS (UPDATE remote_tbl SET c = c || c RETURNING *)
--
2.43.0
I have refactored the commit on the latest version of PG and added a few more tests.
Thanks for the update!
To simplify the roll out of this feature, I decided to work on analyze=false use case first.
I did not go through the entire patch yet, but a few things stood out
from my first pass.
1/
RegisterExtensionExplainOption is called during _PG_init, which is fine, but I
also wonder if we can call this during postgresExplainForeignScan as well?
The reason being is for _PG_init to be invoked, the user must load postgres_fdw
(LOAD, session_preload_libraries, shared_preload_libraries), which from my
experience is not very common in postgres_fdw. Users ordinarily just
"CREATE EXTENSION..."
So this needs to be documented [0]https://www.postgresql.org/docs/current/postgres-fdw.html
2/
Does this behave sanely with multiple fdw connections? Can we add
tests for this?
+
+ /*
+ * add one of the tables to
foreign_scan_table to get the
+ * serverId for remote plans
+ */
+ if (list_length(foreign_scan_table) == 0)
+ foreign_scan_table =
lappend_oid(foreign_scan_table, rte->relid);
+[0]: https://www.postgresql.org/docs/current/postgres-fdw.html
--
Sami Imseih
Amazon Web Services (AWS)
I forgot to mention this point earlier.
I noticed GENERIC_PLAN is hard-coded to 1 (true).
Is that an oversight, or is it required?
```
+ GENERIC_PLAN 1, \
```
--
Sami Imseih
Amazon Web Services (AWS)
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)
Attachments:
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 <samimseih@gmail.com> wrote:
Show quoted text
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)
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)