Pushing down complex query to foreign server in PostgreSQL

Question

My PostgreSQL 12 database is set up with a postgres_fdw foreign data wrapper to another read-only PostgreSQL 12 database.I have a need to retrieve results of a complex query from the foreign server.

For simplicity, I'll derive an example from the schema in the PostgreSQL window functions tutorial, which has a similar form to my case:

CREATE MATERIALIZED VIEW topsals AS
  SELECT depname, empno, salary, enroll_date
  FROM
    (SELECT depname, empno, salary, enroll_date,
            rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos
      FROM ext_schema.empsalary
    ) AS ss
  WHERE pos < 3
;

The source table ext_schema.empsalary in the foreign server is large, but the set of results returned is much smaller, so it is desirable to perform all of the query in externally.

When I try doing this as-is, the query plan performs a full table scan of ext_schema.empsalary and then evaluates the window function and filtering steps locally, meaning all of the rows from the source table have to be sent from the external database. Is there a way to make this situation more efficient?

Answer 1

Window functions can't be pushed through postgres_fdw. See the code:

/* Ignore stages we don't support; and skip any duplicate calls. */
if ((stage != UPPERREL_GROUP_AGG &&
     stage != UPPERREL_ORDERED &&
     stage != UPPERREL_FINAL) ||
    output_rel->fdw_private)
    return;

So it looks like UPPERREL_WINDOW will get bypassed and not pushed.

To force the issue, you would need to create the view on the foreign side and then locally create a foreign table which maps directly onto that view, rather than onto the underlying tables.

If you can't do this (because whoever is charge of the foreign server won't cooperate with you in creating the view) then the remaining option would be to use dblink rather than postgres_fdw to send the query text directly to the foreign server. This is not pleasant but at least you could wrap the dblink function call into a view to abstract things a bit.