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

> I thought about the Relations line a bit more and noticed that there are
> cases where the table reference names for joining relations in the
> Relations line are printed incorrectly. Here is an example:
>
> postgres=# explain verbose select * from (select t1.a, t2.a from ft1 t1,
> ft2 t2 where t1.a = t2.a union select t1.a, t2.a from ft1 t1, ft2 t2 where
> t1.a = t2.a) as t(t1a, t2a);
> QUERY PLAN
>
> --------------------------------------------------------------------------------------------------------------------
> Unique (cost=204.12..204.13 rows=2 width=8)
> Output: t1.a, t2.a
> -> Sort (cost=204.12..204.12 rows=2 width=8)
> Output: t1.a, t2.a
> Sort Key: t1.a, t2.a
> -> Append (cost=100.00..204.11 rows=2 width=8)
> -> Foreign Scan (cost=100.00..102.04 rows=1 width=8)
> Output: t1.a, t2.a
> Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
> Remote SQL: SELECT r1.a, r2.a FROM (public.t1 r1
> INNER JOIN public.t2 r2 ON (((r1.a = r2.a))))
> -> Foreign Scan (cost=100.00..102.04 rows=1 width=8)
> Output: t1_1.a, t2_1.a
> Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
> Remote SQL: SELECT r1.a, r2.a FROM (public.t1 r1
> INNER JOIN public.t2 r2 ON (((r1.a = r2.a))))
> (14 rows)
>
> The table reference names for ft1 and ft2 in the Relations line for the
> second Foreign Scan should be t1_1 and t2_1 respectively.
>

Relations line prints the names of foreign tables that are being joined and
the type of join. I find t1_1 and t2_1 more confusing since the query that
user has provided does not mention t1_1 and t2_1.

>
>
> Would we really need the Relations line? If joining relations are printed
> by core like "Foreign Join on public.ft1 t1_1, public.ft2 t2_1" as proposed
> upthread, we can see those relations from that, not the Relations line.

The join type is missing in that description.

> Also we can see the join tree structure from the deparsed query in the
> Remote SQL line.

The remote SQL has the names of the table on the foreign server. It does
not help to identify the local names.

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company