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

On 2016/07/28 22:11, Kouhei Kaigai wrote:

I wrote:
>> That may be so, but my point is that the target relations involved in
>> the foreign join (ie, ft1 and ft2) should be printed somewhere in the
>> EXPLAIN output by core, as in EXPLAIN for a simple foreign table scan.

> Why? According to your rule, Hash Join should take "on t0,t1,t2".
>
> postgres=# explain select id from t0 natural join t1 natural join t2;
> QUERY PLAN
> -----------------------------------------------------------------------------
> Hash Join (cost=6370.00..4560826.24 rows=98784048 width=4)
> Hash Cond: (t0.aid = t1.aid)
> -> Hash Join (cost=3185.00..3199360.58 rows=98784048 width=8)
> Hash Cond: (t0.bid = t2.bid)
> -> Seq Scan on t0 (cost=0.00..1833334.80 rows=100000080 width=12)
> -> Hash (cost=1935.00..1935.00 rows=100000 width=4)
> -> Seq Scan on t2 (cost=0.00..1935.00 rows=100000 width=4)
> -> Hash (cost=1935.00..1935.00 rows=100000 width=4)
> -> Seq Scan on t1 (cost=0.00..1935.00 rows=100000 width=4)
> (9 rows)

I don't think it needs "on t0,t1,t2", because we can see joining
relations from inner/outer plans in that case. In a foreign-join case,
however, we can't see such relations from the EXPLAIN printed *by core*.
postgres_fdw avoids this issue by adding such relations to the EXPLAIN
using ExplainForeignScan as shown in the below example, but since such
relations are essential, I think that information should be shown by
core itself.

postgres=# explain select * from (select ft1.a from ft1 left join ft2 on
ft1.a = ft2.a where ft1.b = 1) ss1(a) full join (select ft3.a from ft3
left join ft4 on ft3.a = ft4.a where ft3.b = 1) ss2(a) on ss1.a = ss2.a;
QUERY PLAN
----------------------------------------------------------------
Hash Full Join (cost=202.06..204.12 rows=1 width=8)
Hash Cond: (ft1.a = ft3.a)
-> Foreign Scan (cost=100.00..102.05 rows=1 width=4)
Relations: (public.ft1) LEFT JOIN (public.ft2)
-> Hash (cost=102.05..102.05 rows=1 width=4)
-> Foreign Scan (cost=100.00..102.05 rows=1 width=4)
Relations: (public.ft3) LEFT JOIN (public.ft4)
(7 rows)

From the Relations line shown by postgres_fdw, we can see which foreign
join joins which foreign tables, but if no such lines, we couldn't.

I wrote:
>> Probably something like this:
>>
>> Foreign Processing
>> Remote Operations: ...
>>
>> In the Remote Operations line, the FDW/extension could print any info
>> about remote operations, eg, "Scan/Join + Aggregate".

> "Foreign" implies this node is processed by FDW, but "Procesing" gives us
> no extra information; seems to me redundant.

I intentionally chose that word and thought we could leave detailed
descriptions about remote operations to the FDW/extension; a broader
word like "Processing" seems to work well because we allow various kinds
of operations to the remote side, in addition to scans/joins, to be
performed in that one Foreign Scan node indicated by "Foreign
Processing", such as aggregation, window functions, distinct, order by,
row locking, table modification, or combinations of them.

> Prior to the new invention, please explain why you don't want to by my
> suggestion first? Annoying is a feel of you, but not a logic to persuade
> others.

I'm not saying that the idea I proposed is better than your suggestion.
Just brain storming. I want to know what options we have and the pros
and cons of each approach.

>>> postgres=# explain select id from t0 natural join t1 natural join t2;
>>> QUERY PLAN
>>> ---------------------------------------------------------------------------
>>> Custom Scan (GpuJoin) (cost=12385.67..291245.35 rows=9815465 width=4)
>>> GPU Projection: t0.id
>>> Depth 1: GpuHashJoin, HashKeys: (t0.bid)
>>> JoinQuals: (t0.bid = t2.bid)
>>> Nrows (in/out: 98.15%), KDS-Hash (size: 13.47MB, nbatches: 1)
>>> Depth 2: GpuHashJoin, HashKeys: (t0.aid)
>>> JoinQuals: (t0.aid = t1.aid)
>>> Nrows (in/out: 100.00%), KDS-Hash (size: 13.47MB, nbatches: 1)
>>> -> Seq Scan on t0 (cost=0.00..183333.96 rows=9999996 width=12)
>>> -> Seq Scan on t2 (cost=0.00..1935.00 rows=100000 width=4)
>>> -> Seq Scan on t1 (cost=0.00..1935.00 rows=100000 width=4)
>>> (11 rows)

> My largest concern for you proposition is, ForeignScan/CustomScan node is
> enforced to print name of underlying relations, regardless of its actual
> behavior. The above GpuJoin never scans tables at least, thus, it mislead
> users if we have no choice to print underlying relation names.

OK, I understand we would need special handling for such custom joins.

Best regards,
Etsuro Fujita