Re: explain plans for foreign servers

On Tue, Nov 12, 2024 at 4:16 PM Ashutosh Bapat <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com>
wrote:

> On Mon, Nov 11, 2024 at 9:12 PM dinesh salve <cooltodinesh(at)gmail(dot)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(at)AWS