Re: explain plans for foreign servers

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