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

On 2016/08/01 20:31, Ashutosh Bapat wrote:
> 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.

Please look at the Output line for the second Foreign Scan in the
EXPLAIN. (The reason for that is because postgres_fdw gets table
reference names directly from rte->eref->aliasname, while EXPLAIN gets
those through select_rtable_names_for_explain.)

> 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.

We can see the remote names of the foreign tables from the catalogs, so
we would easily identify the local names of foreign tables in the remote
SQL and thus the join type (or join tree structure) from the SQL.

Best regards,
Etsuro Fujita