Re: [v9.5] Custom Plan API

> >> > * ForeignScan node that is not associated with a particular
> foreign-table.
> >> >   Once we try to apply ForeignScan node instead of Sort or
> >> > Aggregate,
> >> existing
> >> >   FDW implementation needs to be improved. These nodes scan on a
> >> materialized
> >> >   relation (generated on the fly), however, existing FDW code assumes
> >> >   ForeignScan node is always associated with a particular
> foreign-table.
> >> >   We need to eliminate this restriction.
> >>
> >> I don't think we need to do that, given the above.
> >>
> > It makes a problem if ForeignScan is chosen as alternative path of Join.
> >
> > The target-list of Join node are determined according to the query
> > form on the fly, so we cannot expect a particular TupleDesc to be
> > returned preliminary. Once we try to apply ForeignScan instead of Join
> > node, it has to have its TupleDesc depending on a set of joined relations.
> >
> > I think, it is more straightforward approach to allow ForeignScan that
> > is not associated to a particular (cataloged) relations.
> 
> From your description, my understanding is that you would like to stream
> data from 2 standard tables to the GPU, then perform a join on the GPU itself.
> 
> I have been told that is not likely to be useful because of the data transfer
> overheads.
> 
Here are two solutions. One is currently I'm working; in case when number
of rows in left- and right- tables are not balanced well, we can keep a hash
table in the GPU DRAM, then we transfer the data stream chunk-by-chunk from
the other side. Kernel execution and data transfer can be run asynchronously,
so it allows to hide data transfer cost as long as we have enough number of
chunks, like processor pipelining.
Other solution is "integrated" GPU that kills necessity of data transfer,
like Intel's Haswell, AMD's Kaveri or Nvidia's Tegra K1; all majors are
moving to same direction.

> Or did I misunderstand, and that this is intended to get around the current
> lack of join pushdown into FDWs?
> 
The logic above is obviously executed on the extension side, so it needs
ForeignScan node to perform like Join node; that reads two input relation
streams and output one joined relation stream.

It is quite similar to expected FDW join-pushdown design. It will consume
(remote) two relations and generates one output stream; looks like a scan
on a particular relation (but no catalog definition here).

Probably, it shall be visible to local backend as follows:
(it is a result of previous prototype based on custom-plan api)

postgres=# EXPLAIN VERBOSE SELECT count(*) FROM   pgbench1_branches b JOIN pgbench1_accounts a ON a.bid = b.bid WHERE
aid< 100;                                                                  QUERY PLAN
                                
 

-------------------------------------------------------------------------------------------------------------------------------------------------Aggregate
(cost=101.60..101.61 rows=1 width=0)  Output: count(*)  ->  Custom Scan (postgres-fdw)  (cost=100.00..101.43 rows=71
width=0)       Remote SQL: SELECT NULL FROM (public.pgbench_branches r1 JOIN public.pgbench_accounts r2 ON ((r1.bid =
r2.bid)))WHERE ((r2.aid < 100))
 
(4 rows)

The place of "Custom Scan" node will be ForeignScan, if Join pushdown got supported.
At that time, what relation should be scanned by this ForeignScan?
It is the reason why I proposed ForeignScan node without particular relation.

> Can you be specific about the actual architecture you wish for, so we can
> understand how to generalise that into an API?
> 
If we push the role of CustomPlan node into ForeignScan, I want to use this node
to acquire control during query planning/execution.

As I did in the custom-plan patch, first of all, I want extension to have
a chance to add alternative path towards particular scan/join.
If extension can take over the execution, it will generate a ForeignPath
(or CustomPath) node then call add_path(). As usual manner, planner decide
whether the alternative path is cheaper than other candidates.

In case when it replaced scan relation by ForeignScan, it is almost same as
existing API doing, except for the underlying relation is regular one, not
foreign table.

In case when it replaced join relations by ForeignScan, it will be almost
same as expected ForeignScan with join-pushed down. Unlike usual table scan,
it does not have actual relation definition on catalog, and its result
tuple-slot is determined on the fly.
One thing different from the remote-join is, this ForeignScan node may have
sub-plans locally, if FDW driver (e.g GPU execution) may have capability on
Join only, but no relation scan portion.
So, unlike its naming, I want ForeignScan to support to have sub-plans if
FDW driver supports the capability.

Does it make you clear? Or, makes you more confused??

Thanks,
--
NEC OSS Promotion Center / PG-Strom Project
KaiGai Kohei <kaigai@ak.jp.nec.com>