postgres_fdw: possible to push data to foreign server for join?

Question

suppose I have a query like

select * from remote_table
   join local_table using(common_key)

where remote_table is a FOREIGN TABLE with postgres_fdw and local_table is a regular table.

local_table is small (100 rows) and remote_table is large (millions of rows).

It looks like the remote table is pulled in its entirety and joined locally, when it would be more efficient to ship the smaller table to the remote server and join remotely.

Is there a way to get postgres_fdw to do that?

Usia

A CTE might allow the push-down? I didn't test: WITH cte AS (SELECT common_key FROM local_table) SELECT * FROM remote_table WHERE common_key IN (TABLE cte); On second though: I doubt that can fly ... – Poussin

There was a related question for another FDW, and I came up with an idea that did not work. Might work for postgres_fdw (and Postgres 12?) though. You might want to have a look: https://mcmap.net/q/1209167/-how-to-force-evaluation-of-subquery-before-joining-pushing-down-to-foreign-server – Poussin

Answer 1

You cannot do that with a join, since joins between tables on different servers are always executed locally.

What you could try is something like:

SELECT *
FROM (SELECT *
      FROM remote_table
      WHERE common_key IN (SELECT common_key FROM local_table)
     ) a
  JOIN local_table USING (common_key);

I did not test it, so I am not sure if it will work, but the idea is to use a condition for the foreign table scan that can be pushed down and reduces the amount of data fetched as much as possible.

Into

No, this will have the same issue as a join. WHERE clauses will only be pushed down if they can be executed remotely. The only way this would work is if you pushed the contents of local_table itself to the remote DB. – Usia

Well, think of a different solution along the lines of my answer, e.g. constructing an IN list from the result of SELECT common_key FROM local_table. – Into

Answer 2

Have you tried deploying the local data into a temp table on the foreign server then joining it into the foreign table? Not sure of your process or if this would be efficient for you or not.

Collaboration