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

> 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