MIME-Version: 1.0
References: <CAP+B4TD=iy-C2EnsrJgjpwSc7_4pd3Xh-gFzA0bwsw3q8u860g@mail.gmail.com>
 <CAA5RZ0vXiOiodrNQ-Va4FCAkXMpGA=GZDeKjFBRgRvHGuW7s7Q@mail.gmail.com>
 <07147023882ac6c4424df946fd4b82b8e8c211a5.camel@j-davis.com>
 <341357.1741201933@sss.pgh.pa.us> <fc0cd0a77a2a5566f30a630580affb9fc3f43ce7.camel@j-davis.com>
 <CAP+B4TDxE3gbrixL9qR9F+CK_N_N0-HmXSL55QXVZNAorRHSHQ@mail.gmail.com>
 <CAA5RZ0skXF+d7SANSnMi3xMTs9zr6dZadyc5OEtPiMnw-rGizw@mail.gmail.com>
 <CAA5RZ0vFAhGgz-VwE0NwvesHNDN5PWAgWA1B=cECg0=5nt0kjQ@mail.gmail.com>
 <CAA5RZ0spw7Fmd5pGvFdTaG2GqkSm6HeXJABbBeqzUDkuN4uevQ@mail.gmail.com> <CAP+B4TDRPOth7EJcmFMNaA-XchbPpfZ1OPk+__0a1ZMhibJjrw@mail.gmail.com>
In-Reply-To: <CAP+B4TDRPOth7EJcmFMNaA-XchbPpfZ1OPk+__0a1ZMhibJjrw@mail.gmail.com>
From: Sami Imseih <samimseih@gmail.com>
Date: Fri, 9 Jan 2026 18:16:26 -0600
Message-ID: <CAA5RZ0taqqxv8m8RuZJkFXQ_ZxtaQNcHnga47LW5dKYqZLt4Rg@mail.gmail.com>
Subject: Re: explain plans for foreign servers
To: dinesh salve <cooltodinesh@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-hackers@postgresql.org, 
	Jeff Davis <pgsql@j-davis.com>
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAA5RZ0taqqxv8m8RuZJkFXQ_ZxtaQNcHnga47LW5dKYqZLt4Rg%40mail.gmail.com>
Precedence: bulk

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