explain plans for foreign servers

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=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

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=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

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

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

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)

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:

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)