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.
Dinesh Salve
SDE@AWS
