Re: Function scan FDW pushdown

Hi Alexander,

On Thu, May 20, 2021 at 11:13 PM Alexander Pyhalov
<a(dot)pyhalov(at)postgrespro(dot)ru> wrote:
>
> Hi.
>
> The attached patch allows pushing joins with function RTEs to PostgreSQL
> data sources.
> This makes executing queries like this
>
> create foreign table f_pgbench_accounts (aid int, bid int, abalance int,
> filler char(84)) SERVER local_srv OPTIONS (table_name
> 'pgbench_accounts');
> select * from f_pgbench_accounts join unnest(array[1,2,3]) ON unnest =
> aid;
>

It will be good to provide some practical examples where this is useful.

> more efficient.
>
> with patch:
>
>
> So far I don't know how to visualize actual function expression used in
> function RTE, as in postgresExplainForeignScan() es->rtable comes from
> queryDesc->plannedstmt->rtable, and rte->functions is already 0.

The actual function expression will be part of the Remote SQL of
ForeignScan node so no need to visualize it separately.

The patch will have problems when there are multiple foreign tables
all on different servers or use different FDWs. In such a case the
function scan's RelOptInfo will get the fpinfo based on the first
foreign table the function scan is paired with during join planning.
But that may not be the best foreign table to join. We should be able
to plan all the possible joins. Current infra to add one fpinfo per
RelOptInfo won't help there. We need something better.

The patch targets only postgres FDW, how do you see this working with
other FDWs?

If we come up with the right approach we could use it for 1. pushing
down queries with IN () clause 2. joining a small local table with a
large foreign table by sending the local table rows down to the
foreign server.

--
Best Wishes,
Ashutosh Bapat