Re: explain plans for foreign servers

dinesh salve <cooltodinesh(at)gmail(dot)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