How to force evaluation of subquery before joining / pushing down to foreign server

Suppose I want to query a big table with a few WHERE filters. I am using Postgres 11 and a foreign table; foreign data wrapper (FDW) is clickhouse_fdw. But I am also interested in a general solution.

I can do so as follows:

SELECT id,c1,c2,c3 from big_table where id=3 and c1=2

My FDW is able to do the filtering on the remote foreign data source, ensuring that the above query is quick and doesn't pull down too much data.

The above works the same if I write:

SELECT id,c1,c2,c3 from big_table where id IN (3,4,5) and c1=2

I.e all of the filtering is sent downstream.

However, if the filtering I'm trying to do is slightly more complex:

SELECT bt.id,bt.c1,bt.c2,bt.c3
from big_table bt
join lookup_table l on bt.id=l.id
where c1=2 and l.x=5

then the query planner decides to filter on c1=2 remotely but apply the other filter locally.

In my use case, calculating which ids have l.x=5 first and then sending those off to be filtered remotely will be much quicker, so I tried to write it the following way:

SELECT id,c1,c2,c3
from big_table
where c1=2
and id IN (select id from lookup_table where x=5)

However, the query planner still decides to perform the second filter locally on ALL of the results from big_table that satisfy c1=2, which is very slow.

Is there some way I can "force" (select id from lookup_table where x=5) to be pre-calculated and sent as part of a remote filter?

Foreign data wrapper

Typically, joins or any derived tables from subqueries or CTEs are not available on the foreign server and have to be executed locally. I.e., all rows remaining after the simple WHERE clause in your example have to be retrieved and processed locally like you observed.

If all else fails you can execute the subquery SELECT id FROM lookup_table WHERE x = 5 and concatenate results into the query string.

More conveniently, you can automate this with dynamic SQL and EXECUTE in a PL/pgSQL function. Like:

CREATE OR REPLACE FUNCTION my_func(_c1 int, _l_id int)
  RETURNS TABLE(id int, c1 int, c2 int, c3 int)
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE
     'SELECT id,c1,c2,c3 FROM big_table
      WHERE  c1 = $1
      AND    id = ANY ($2)'
   USING _c1, ARRAY(SELECT l.id FROM lookup_table l WHERE l.x = _l_id);
END
$func$;

Related:

• Table name as a PostgreSQL function parameter

Or try this search on SO.

Or you might use the meta-command \gexec in psql. See:

• Filter column names from existing table for SQL DDL statement

Related question concerning postgres_fdw:

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

General technique in SQL

That's a different story. Just use a CTE. But I don't expect that to help with the FDW.

WITH cte AS (SELECT id FROM lookup_table WHERE x = 5)
SELECT id,c1,c2,c3
FROM   big_table b
JOIN   cte USING (id)
WHERE  b.c1 = 2;

Postgres 12 changed (improved) behavior, so that CTEs can be inlined like subqueries, given some preconditions. But, quoting the manual:

You can override that decision by specifying MATERIALIZED to force separate calculation of the WITH query

So:

WITH cte AS MATERIALIZED (SELECT id FROM lookup_table WHERE x = 5)
...

Typically, none of this should be necessary if your DB server is configured properly and column statistics are up to date. But there are corner cases with uneven data distribution ...