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?

  • 1
    Did you ever figure this out? Running into the same thing, selecting ~8500 pretty selective rows out of 1.5M, seems my options are either retrieving the entire dataset and running the filter locally, or having 8500 seperate queries.
    – Koen Faro
    Mar 22 at 11:49
  • I did some more research after writing this question. It is possible to write a solution but right now, the answer is no.
    – ldrg
    Mar 23 at 12:40

1 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.