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

On 2016/08/10 5:19, Robert Haas wrote:
> On Mon, Aug 8, 2016 at 12:22 AM, Etsuro Fujita
> <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>> One thing we need to do to leave that as is would be to fix a bug that I
>> pointed out upthred. Let me explain about that again. The EXPLAIN command
>> selects relation aliases to be used in printing a query so that each
>> selected alias is unique, but postgres_fdw wouldn't consider the uniqueness.
>> 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 relation aliases in the Relations line in the second Foreign Scan, t1
>> and t2 for ft1 and ft2, are not unique; they should be t1_1 and t2_1
>> (compare the aliases in the Relations line with ones in the Output line
>> directly above that, created by core). The reason for that is because
>> postgres_fdw has created the Relations info by using rte->eref->aliasname as
>> relation aliases as is at path-creation time. Probably it would be a little
>> bit too early for postgers_fdw to do that. Couldn't postgres_fdw create
>> that info after query planning, for example, during ExplainForeignScan?

> Yes, it seems what we are doing now is not consistent with what
> happens for plain tables; that should probably be fixed.

OK, I think we should fix the issue that postgres_fdw produces incorrect
aliases for joining relations shown in the Relations line in EXPLAIN for
a join pushdown query like the above) in advance of the 9.6 release, so
I'll add this to the 9.6 open items.

Best regards,
Etsuro Fujita