Re: explain plans for foreign servers - Mailing list pgsql-hackers

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