Re: Oddity in EXPLAIN for foreign/custom join pushdown plans

On Wed, Jul 27, 2016 at 8:50 AM, Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp>
wrote:

> Hi,
>
> 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