Sample Header Ad - 728x90

Can Postgresql FDW avoid n+1 when planning joins?

0 votes
1 answer
91 views
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?
Asked by ldrg (709 rep)
Dec 9, 2022, 09:02 PM
Last activity: Mar 23, 2023, 12:48 PM