When a fdw table participates in query planning and finds itself as
part of a join it can output a parameterized path. If chosen, Postgres
will dutifully call the fdw over and over via IterateForeignScan to
fetch matching tuples. Many fdw extensions do network traffic, though,
and it would be beneficial to reduce the total number of queries done
or network connections established.
Is there some path that can be emitted by the fdw, or some other
technique, to get the query planner and everything else to handle
batching the tuples returned by the outer relation? For example, once
batched the fdw extension could send the equivalent of a WHERE row IN
(a, b, c), or maybe even WHERE row BETWEEN a AND c to the foreign
system, and either the fdw callback or a subplan does the rechecking
to match up the returned foreign tuples with the local ones.
One thought is that it might be possible to abuse the async support
for fdws to accomplish this. Your fdw could accept async requests, sit
on them until some threshold is crossed, do the actual query and feed
them back into the executor when the results are back. However, from
what I can tell the async interface has no way to tell the ForeignScan
that it won't get any more async requests, so there's no way to force
flush the final batch of queries.