Hi Bharath, > Having the remote EXPLAIN support first makes sense. However, having a > high-level design for EXPLAIN ANALYZE support would make the feature > more concrete.
In this thread, a possible high level design for EXPLAIN ANALYZE support was discussed and here is the one of approach. Idea is to capture the plan of the remote statement that postgres_fdw already executes, rather than issuing a separate remote EXPLAIN ANALYZE. The remote backend could use an auto_explain style facility to retain the completed plan, including runtime statistics, at ExecutorEnd or when the FDW cursor is closed. After completing the remote query, postgres_fdw would either - 1. issue a separate statement to retrieve that stored plan and associate it with the corresponding local plan-node ID. 2. Or a new guc set on remote connection can request remote Postgresql to transport the plan through NOTICE. Transporting the plan through NOTICE is fragile, while returning an additional result set conflicts with the extended query protocol, so a dedicated retrieval function from approach 1 or separate protocol mechanism for this would be preferable. This design would require explicit capability detection on remote PostgreSQL server. GENERIC_PLAN is only an additional constraint for queries containing placeholders: it need not be combined with ANALYZE as here we capture the actual plan using the bound parameter values from the original remote execution. I am proposing we expose the remote_plans option now as that feature itself would make our user's life a bit easy and in parallel I will groom this design approach on top of other suggestions if any. Updated the patch to add `GENERIC_PLAN` only when the remote SQL contains parameter placeholders. Placeholder detection uses fdw_exprs as suggested. Thank, Dinesh
v8-0001-postgres_fdw-show-remote-EXPLAIN-plans-via-REMOTE.patch
Description: Binary data
