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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
