Subject: Re: explain plans for foreign servers

From: dinesh salve <[email-masked]>
Date: Wed, 7 Jan 2026 15:30:25 +0530

Thanks Sami for feedback. Few points I wanted to call out and need your
inputs on -

Supporting remote_plans options for inserts:
Only "explain insert" are executed with bind variables (verified by logging
all sqls while running make check) and while executing that on remote is
erroring out with error "there is no parameter $1". We can either NOT
support remote plans for insert statements or always use generic_plan
option on remote sql. Using "generic_plan" on remote comes with an
additional check if remote supports this option or not in case remote shard
is older postgres.
I prefer not supporting remote_plans for inserts as there is nothing much
that goes in insert statement plans unless its "insert into..select". User
can always run explain on that select separately. Appreciate your inputs on
this.

About decision which explain options we should forward to remote shard:
This is because local and remote postgres could be different and we still
need to address what all options we send in remote sql as remote shard
might not even support them. We can forward only limited options to remote
which are widely supported (pg >= 9) i.e. verbose, costs, buffers, format
only.
If we need to support all possible options, we need to query the version of
remote postgres and then prepare remote sql. Thoughts?

Regard,
Dinesh (AWS)

On Wed, Dec 10, 2025 at 2:38 AM Sami Imseih <[email-masked]> wrote:

> Hi,
>
> I spent more time reviewing this patch. Here are additional comments.
>
> 1/ Remove unnecessary includes
>
> #include "commands/explain_state.h" from option.c.
> #include "utils/json.h" from postgres_fdw.c.
>
> 2/ Add new EXPLAIN options
>
> MEMORY and SUMMARY flags added to the remote EXPLAIN query formatting.
> These do not require ANALYZE to be used.
>
> A larger question is how would we want to ensure that new core EXPLAIN
> options can be automatically set?
>
> This is not quite common, but perhaps it is a good thing to add a comment
> near ExplainState explaining that if a new option is added, make sure
> that postgre_fdw remote_plans are updated.
>
> ```
> typedef struct ExplainState
> {
>   StringInfo  str;      /* output buffer */
>   /* options */
>   bool    verbose;    /* be verbose */
>   bool    analyze;    /* print actual times */
>   bool    costs;      /* print estimated co
>
> ```
>
> 3/ Removed unnecessary pstrdup() when appending remote plan rows to
> explain_plan.
>
> Removed unnecessary pstrdup() when appending remote plan rows to
> explain_plan.
>
> ```
> +                       appendStringInfo(&explain->explain_plan,
> "%s\n", pstrdup(PQgetvalue(res, i, 0)));
> ```
>
>
> 4/ Simplify foreign table OID handling in postgresExplainForeignScan
>
> I am not sure why we need a list that can only hold a single value.
> Can we just use an Oid variable to store this?
>
>
> 5/ Encapsulates getting the connection, executing the remote EXPLAIN,
> and releasing the connection.
>
> Replaces repeated code in postgresExplainForeignScan,
> postgresExplainForeignModify, and postgresExplainDirectModify.
>
> For #4 and #5, attached is my attempt to simplify these routines. What
> do you think?
>
> 6/ Updated typedefs.list
>
> ... to include PgFdwExplainRemotePlans and PgFdwExplainState.
>
>
> 7/ Tests
>
> I quickly skimmed the tests, but I did not see a join push-down test.
> We should add
> that.
>
>
> --
> Sami Imseih
> Amazon Web Services (AWS)

---

From: Sami Imseih <[email-masked]>
Date: Fri, 9 Jan 2026 18:16:26 -0600

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

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

---

From: dinesh salve <[email-masked]>
Date: Sat, 20 Jun 2026 00:34:43 +0530

> Maybe others have a better way?

I agree, I found use of generic_plan convenient here. Attached patch 2
considers this. I have added an error message if the remote postgres server
is not compatible with options being sent. Also updated documentation with
limitations.
Thanks a lot for feedback, Sami. Please review the attached patch and share
feedback.

---

From: Sami Imseih <[email-masked]>
Date: Fri, 3 Jul 2026 13:35:53 -0500

Hi Dinesh,

Thanks for the patch. v2 needed rebasing, but I also went ahead and applied
my comments to v3.

1/ Forward all user options instead of hardcoding ExplainState booleans

The current approach reads individual ExplainState fields to construct the
remote EXPLAIN:

```
appendStringInfo(&explain_sql, ", VERBOSE %s", es->verbose ? "TRUE" : "FALSE");
...
.....
```

After thinking about this a bit, this creates a maintenance problem.
Any new non-ANALYZE EXPLAIN option added to core requires someone to
remember to update postgres_fdw. I previously suggested a comment
in explain_state.h to remind developers, but I think we should do
something better.

The explain_validate_options_hook already receives the user's
original option list. We save it in PgFdwExplainState and use
to construct the remote EXPLAIN SQL, skipping only remote_plans
and generic_plan.

New core EXPLAIN options will automatically be forwarded to the
remote when the user specifies them, with no postgres_fdw code
change needed. If the remote doesn't recognize an option, it errors
clearly rather than silently ignoring it.

I tested this against a PG 16 remote. EXPLAIN (REMOTE_PLANS, MEMORY)
correctly errors with "unrecognized EXPLAIN option 'memory'" since
PG 16 does not have MEMORY. This seems better than silent omission.

2/ I also made some comment improvements. Some were just too verbose.

3/ Documentation improvements

The generic plan limitation only matters when the remote SQL contains
parameter placeholders. For queries with only literals (e.g.,
WHERE id = 42), the generic plan is identical to a custom plan. The docs
should clarify this rather than making it seem like a general limitation.

I also added some concrete examples showing both cases where a literal
is used vs a parameter.

Attached is my attempt at the above. What do you think?

---

From: dinesh salve <[email-masked]>
Date: Sat, 4 Jul 2026 22:37:19 +0530

Just took a look and it looks great, thanks Sami for trimming down the
documentation and avoiding maintenance of version and their options. I have
moved the patch to the current commitfest PG20-2.

---

From: dinesh salve <[email-masked]>
Date: Wed, 8 Jul 2026 23:42:36 +0530

As discussed offline, also added a regression test that joins two foreign
tables living on different foreign servers (loopback and loopback2), which
exercises REMOTE_PLANS over multiple fdw connections.