postrgres_fdw doesn't send query WHERE clauses to the foreign db if has function parameters in the query

Question

I want to make foreign db call in my custom function and put function input arguments into WHERE clause, but postgres does not send WHERE clause to foreign db and therefore does not use any indexes:

There is the function:

create or replace function public.testFDW(
    time_a timestamp,
    time_b timestamp
)
    returns table
            (
                user_uuid       varchar,
                user_email      varchar,
                user_created_at timestamptz
            )
as
$$
BEGIN
    RETURN QUERY
        SELECT u.user_uuid,
               u.email,
               up.created_date
        FROM foreign_ums.user_profiles up
                 left join foreign_ums.users u on u.id = up.user_id
        where up.created_date between time_a and time_b;
END
$$
    LANGUAGE 'plpgsql';

logs:

2022-06-10 17:46:27.440 UTC [670] [ums]LOG: execute : DECLARE c2 CURSOR FOR SELECT user_id, created_date FROM public.user_profiles

2022-06-10 17:46:27.498 UTC [670] [ums]LOG: execute : DECLARE c1 CURSOR FOR SELECT id, email, user_uuid FROM public.users

Execution Time: 101.274 ms

But if I replace the function input arguments with constants, then everything is fine.

edited function:

create or replace function public.testFDW(
    time_a timestamp,
    time_b timestamp
)
    returns table
            (
                user_uuid       varchar,
                user_email      varchar,
                user_created_at timestamptz
            )
as
$$
BEGIN
    RETURN QUERY
        SELECT u.user_uuid,
               u.email,
               up.created_date
        FROM foreign_ums.user_profiles up
                 left join foreign_ums.users u on u.id = up.user_id
        where up.created_date between '2022-05-01 14:00:43' and '2022-06-01 14:00:43';
END
$$
    LANGUAGE 'plpgsql';

logs:

2022-06-10 17:57:46.743 UTC [670] [ums]LOG: execute : DECLARE c1 CURSOR FOR SELECT r2.user_uuid, r2.email, r1.created_date FROM (public.user_profiles r1 LEFT JOIN public.users r2 ON (((r2.id = r1.user_id)))) WHERE ((r1.created_date >= '2022-05-01 14:00:43+00'::timestamp with time zone)) AND ((r1.created_date <= '2022-06-01 14:00:43+00'::timestamp with time zone))

Execution Time: 3.988 ms

Is there any way to use function input arguments in a WHERE clause with fdw so that postgres sends it to foreign db?

Answer 1

You might need a "dynamic" query inside your function, to push the WHERE condition to the remote server. Something like this (not tested):

create or replace function public.testFDW(
    time_a timestamp,
    time_b timestamp
)
    returns table
            (
                user_uuid       varchar,
                user_email      varchar,
                user_created_at timestamptz
            )
as
$$
DECLARE
    _sql    TEXT;
BEGIN
    _sql    := FORMAT('
        SELECT u.user_uuid,
               u.email,
               up.created_date
        FROM foreign_ums.user_profiles up
                 LEFT JOIN foreign_ums.users u ON u.id = up.user_id
        WHERE up.created_date BETWEEN %L AND %L;', time_a, time_b);
    EXECUTE _sql;     
END
$$
    LANGUAGE 'plpgsql';