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

On 2016/08/10 5:19, Robert Haas wrote:
> On Mon, Aug 8, 2016 at 12:22 AM, Etsuro Fujita
> <fujita.etsuro@lab.ntt.co.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