Thread

1. explain plans for foreign servers

   dinesh salve <cooltodinesh@gmail.com> — 2024-11-11T15:42:20Z

   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
   

2. Re: explain plans for foreign servers

   Andy Fan <zhihuifan1213@163.com> — 2024-11-12T00:33:33Z

   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
   
   
   
   
   

3. Re: explain plans for foreign servers

   Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> — 2024-11-12T10:46:32Z

   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
   
   
   
   

4. Re: explain plans for foreign servers

   dinesh salve <cooltodinesh@gmail.com> — 2024-11-22T21:02:26Z

   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
   

5. Re: explain plans for foreign servers

   Anton Shmigirilov <a.shmigirilov@postgrespro.ru> — 2024-11-25T16:53:36Z

   > 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
   
   
   

6. Re: explain plans for foreign servers

   dinesh salve <cooltodinesh@gmail.com> — 2024-12-14T12:49:37Z

   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
   

7. Re: explain plans for foreign servers

   Sami Imseih <samimseih@gmail.com> — 2025-02-26T19:13:30Z

   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)
   
   
   
   

8. Re: explain plans for foreign servers

   Jeff Davis <pgsql@j-davis.com> — 2025-03-05T19:00:43Z

   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] 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]
   https://www.postgresql.org/message-id/CA%2BTgmoYSzg58hPuBmei46o8D3SKX%2BSZoO4K_aGQGwiRzvRApLg%40mail.gmail.com
   
   
   
   
   
   

9. Re: explain plans for foreign servers

   Tom Lane <tgl@sss.pgh.pa.us> — 2025-03-05T19:12:13Z

   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
   
   
   
   

10. Re: explain plans for foreign servers

    Sami Imseih <samimseih@gmail.com> — 2025-03-05T19:26:15Z

    >> 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] and planned on doing the investigation.
    
    [0] https://www.postgresql.org/message-id/CAA5RZ0tLrNOw-OgPkv49kbNmZS4nFn9vzpN5HXX_xvOaM9%3D5ww%40mail.gmail.com
    
    
    --
    Sami Imseih
    
    
    
    

11. Re: explain plans for foreign servers

    Jeff Davis <pgsql@j-davis.com> — 2025-03-05T23:24:31Z

    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
    
    
    
    
    

12. Re: explain plans for foreign servers

    dinesh salve <cooltodinesh@gmail.com> — 2025-11-18T03:25:39Z

    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
    

13. Re: explain plans for foreign servers

    Sami Imseih <samimseih@gmail.com> — 2025-12-03T22:40:26Z

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

14. Re: explain plans for foreign servers

    Sami Imseih <samimseih@gmail.com> — 2025-12-04T00:34:23Z

    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)
    
    
    
    

15. Re: explain plans for foreign servers

    Sami Imseih <samimseih@gmail.com> — 2025-12-09T21:08:08Z

    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)
    

16. Re: explain plans for foreign servers

    dinesh salve <cooltodinesh@gmail.com> — 2026-01-07T10:00:25Z

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

17. Re: explain plans for foreign servers

    Sami Imseih <samimseih@gmail.com> — 2026-01-10T00:16:26Z

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

18. Re: explain plans for foreign servers

    dinesh salve <cooltodinesh@gmail.com> — 2026-06-19T19:04:43Z

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

19. Re: explain plans for foreign servers

    Sami Imseih <samimseih@gmail.com> — 2026-07-03T18:35:53Z

    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?
    
    --
    Sami Imseih
    Amazon Web Services (AWS)
    

20. Re: explain plans for foreign servers

    dinesh salve <cooltodinesh@gmail.com> — 2026-07-04T17:07:19Z

    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.