I noticed that currently the core doesn't show any information on the target relations involved in a foreign/custom join in EXPLAIN, by itself. Here is an example:
-- join two tables EXPLAIN (COSTS false, VERBOSE) SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10; QUERY PLAN \
-------------------------------------------------------------------------------------------------------------------------------------------------------\ ----------------------------------- Limit Output: t1.c1, t2.c1, t1.c3 -> Foreign Scan Output: t1.c1, t2.c1, t1.c3 Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2) Remote SQL: SELECT r1."C 1", r1.c3, r2."C 1" FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")))) ORDER BY r1.c3 ASC N\ ULLS LAST, r1."C 1" ASC NULLS LAST (6 rows)
postgres_fdw shows the target relations in the Relations line, as shown above, but I think that the core should show such information independently of FDWs; in the above example replace "Foreign Scan" with "Foreign Join on public.ft1 t1, public.ft2 t2". Please find attached a patch for that. Comments welcome!
The patch always prints ForeignJoin when scanrelid <= 0, which would be odd considering that FDWs can now push down post-join operations. We need to device a better way to convey post-join operations. May be something like
Foreign Grouping, aggregation on ...
Foreign Join on ...
But then the question is a foreign scan node can be pushing down many operations together e.g. join, aggregation, sort OR join aggregation and windowing OR join and insert. How would we clearly convey this? May be we say
Foreign Scan
operations: join on ..., aggregation, ...
That wouldn't be so great and might be clumsy for many operations. Any better idea?
--
Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company
