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.
