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
