Re: explain plans for foreign servers

Hi,

On Mon, Jul 27, 2026 at 11:27 PM Dinesh Salve <cooltodinesh(at)gmail(dot)com> wrote:
>
> A minor update in patch after observing cfbot failures.
>
> Root cause was - _PG_init saved the previous explain* hooks but never called them, so when postgres_fdw.so was lazily loaded during planning it clobbered pg_plan_advice's per-plan hook and its "Generated Plan Advice" output vanished — breaking contrib/pg_plan_advice/t/001_foreign_scan.pl on every platform.

Thanks for working on this, Dinesh!

Thanks, Sami, for pointing me to this!

It seems useful especially in distributed/sharded PostgreSQL database
architectures that rely on postgres_fdw. The use of explain plans
needs no introduction in production query performance analysis. In
distributed databases, many times there can be tens of remote
databases, and analyzing query performance issues across all the
remote databases may not always be possible (simply that one needs to
hop across remote databases and one may not have permissions to run
queries at all, etc.), and even if one is able to run them, it's hard
to figure out and get it exactly right to collect the plans from
different remote databases. (Again, others who have seen these
problems firsthand can explain this better than me.)

I haven't read the whole thread, but I remember the first time we
talked off-list about this problem. With that context, I just happened
to look at the v6 patch and it seems the tests and expected outputs
are too verbose, making the patch huge and hard to review. IMO, we
don't need to print all the flavors of explain plans (JSON/XML/YAML).
How about running a simple join or aggregate on a couple of remote
tables joining with a local table and just printing the explain plan
(no JSON/XML/YAML) like any other explain test? Is there any problem
with this?

Another point, why are we not supporting explain analyze?

Here are some code-level comments on the patch:

1/
+static int
+get_extension_id(void)
+{
+ if (extension_id == -1)
+ extension_id = GetExplainExtensionId("postgres_fdw");
+ return extension_id;

Why not get the extension id at once in _PG_init and use the static
variable directly, instead of spreading the get function calls
everywhere?

2/
+ <literal>GENERIC_PLAN</literal>) are forwarded to the remote server.
+ If the remote server is running an older version that does not recognize

+ /*
+ * GENERIC_PLAN required because deparsed SQL may contain $n placeholders;
+ * only available from PG 16.
+ */

It's worth mentioning that otherwise it causes an ERROR for
parameterized queries.

3/ Add some error cases where the remote explain query gives an error,
to see how it is handled in postgresExplainStatement's PG try-catch
block.

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com