Re: explain plans for foreign servers

> So "generic_plan" as a mandatory option may be the best way to proceed,
> and only make the remote_plans option available to remote versions that
> support this option.
> Maybe others have a better way?

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

On Sat, Jan 10, 2026 at 5:46 AM Sami Imseih <samimseih(at)gmail(dot)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)
>