Can Postgresql FDW avoid n+1 when planning joins?

Question

Does the PostgreSQL executor / planner have the ability to avoid n+1 querying of FDW tables? If so, what conditions have to be in place for this to happen, e.g. does the FDW need to emit a specific kind of path to the query planner?

As a more concrete example, suppose I have:

CREATE TABLE local (local_id integer primary key, foreign_id integer, selective_col text ...);
CREATE FOREIGN TABLE foreign (foreign_id integer primary key, ...)

And my query is something like:

SELECT foreign.*
FROM local
JOIN foreign USING (foreign_id)
WHERE local.selective_col = "happybirthday"

Statistics are such that SELECT foreign_id FROM local WHERE selective_col = "happybirthday" is highly selective and gets scanned first. Now the question is to join the foreign table onto those rows.

Can Postgres plan a join that would batch calls to the foreign table, e.g. SELECT * FROM foreign WHERE foreign_id IN (...), or would it degenerate down to one foreign query per tuple from local?

Looking at the merge types - it looks like you could do this sort of batching on a nested loop or merge join, but does the planner actually have the ability to do so?

Answer 1

After asking around and digging into the PostgreSQL code base it seems there is nothing that does this out of the box.

If you wanted to implement this, I believe you need to write a custom scan provider. This is how you can add custom path types which get turned into custom execution plans that do the batching. It is possible to write this as an extension although I'd imagine that upstream would be interested in the functionality as well.