Re: Oddity in EXPLAIN for foreign/custom join pushdown plans - Mailing list pgsql-hackers

On 2016/08/01 20:15, Etsuro Fujita 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.
>
> Another concern about the Relations line is, that represents just an
> internal representation of a pushed-down join, so that would not
> necessarily match a deparsed query shown in the Remote SQL line.  Here
> is an example, which I found when working on supporting pushing down
> full outer join a lot more, by improving the deparsing logic so that
> postgres_fdw can build a remote query that involves subqueries [1],
> which I'll work on for 10.0:
>
> + -- full outer join with restrictions on the joining relations
> + EXPLAIN (COSTS false, VERBOSE)
> + SELECT t1.c1, t2.c1 FROM (SELECT c1 FROM ft4 WHERE c1 BETWEEN 50 AND
> 60) t1 FULL JOIN (SELECT c1 FROM ft5 WHERE c1 BETWEEN 50 AND 60) t2 ON
> (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1;
> +                                                                 QUERY
> PLAN
> +
>
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> +  Foreign Scan
> +    Output: ft4.c1, ft5.c1
> +    Relations: (public.ft4) FULL JOIN (public.ft5)
> +    Remote SQL: SELECT ss1.c1, ss2.c1 FROM ((SELECT c1 FROM "S 1"."T 3"
> WHERE ((c1 >= 50)) AND ((c1 <= 60))) ss1(c1) FULL JOIN (SELECT c1 FROM
> "S 1"."T 4" WHERE ((c1 >= 50)) AND ((c1 <= 60))) ss2(c1) ON (((ss1.c1 =
> ss2.c1)))) ORDER BY ss1.c1 ASC NULLS LAST, ss2.c1 ASC NULLS LAST
> + (4 rows)
>
> "(public.ft4) FULL JOIN (public.ft5)" in the Relations line does not
> exactly match the deparsed query in the Remote SQL line, which I think
> would be rather confusing for users.  (We may be able to print more
> exact information in the Relations line so as to match the depaserd
> query, but I think that that would make the Relations line redundant.)
>
> 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.  Also we can see the join tree structure from the
> deparsed query in the Remote SQL line.  The Relations line seems to be
> not that useful anymore, then.  What do you think about that?

I removed the Relations line.  Here is an updated version of the patch.

* As I said upthread, I left the upper-relation handling for another
patch.  Currently, the patch prints "Foreign Scan" with no relations in
that case.

* I kept custom joins as-is.  We would need discussions about how to
choose relations we print in EXPLAIN, so I'd also like to leave that for
yet another patch.

Best regards,
Etsuro Fujita