Thread: explain plans for foreign servers

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

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
theEXPLAIN command. 
> I think this will help end users understand query execution plans used by remote servers. Sample output for table
peoplewhere 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
controlthis behaviour using a guc defined in postgres_fdw extension.      I am more inclined towards guc as this
featureis 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
sendover 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_fdwwould read this guc and send back used query plan as a NOTICE (similar to auto_explain extension does) with
customheader which postgres_fdw extension understands. . We also have an opportunity to introduce a new message type in
theprotocol 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 Hackers,
>
> I am working on a feature in postgres_fdw extension to show plans used by remote postgresql servers in the output of
theEXPLAIN command. 
> I think this will help end users understand query execution plans used by remote servers. Sample output for table
peoplewhere 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
controlthis behaviour using a guc defined in postgres_fdw extension.      I am more inclined towards guc as this
featureis 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
sendover 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_fdwwould read this guc and send back used query plan as a NOTICE (similar to auto_explain extension does) with
customheader which postgres_fdw extension understands. . We also have an opportunity to introduce a new message type in
theprotocol 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.
Youcan simply set it to off before most tests that involve plan checking, while leaving the rest unchanged. This leads
toa 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
sendEXPLAIN with ANALYZE set to OFF, regardless of the initial ANALYZE state. This approach will prevent the
re-executionof the remote query (the SQL part of the ForeignScan node), which could potentially lead to side effects.
It'ssafe to send ANALYZE ON during remote EXPLAIN only if your remote SQL is idempotent, i.e. doesn't change anything.
Thatway 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,
valuessuch as actual time, planning time, execution time, and similar metrics cannot be exposed because they relates to
eventsthat 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
SUMMARYOFF 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
statementswith placeholders (i.e. $1, $2, etc) instead of actual parameter values. It's syntactically incorrect SQL,
whichwill 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
correctSQL. 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