Postgres FDW含函数参数时未下推WHERE子句至外部库问题咨询

PL/pgSQL函数默认采用通用执行计划缓存机制：函数第一次执行时生成可复用的通用计划，后续调用直接复用该计划，不会将每次传入的实际参数值合并到执行计划中做优化判断。postgres_fdw生成远程查询语句时拿不到具体参数值，为了保证计划的通用性，不会将参数化的WHERE条件推送到远程库执行，只能拉取全表数据到本地后再做过滤，因此无法命中远程库索引，性能较差。而常量过滤条件在计划生成阶段就能确定具体值，因此可以正常下推。

方案1：改用SQL语言函数，设置正确的稳定性级别 #

SQL语言函数对FDW谓词下推的支持远优于PL/pgSQL，只要将函数稳定性设置为STABLE（同一调用上下文下，相同输入返回固定结果，符合时间范围过滤的场景特征），优化器就可以正常将参数带入计划，生成带WHERE条件的远程查询。
改后示例：

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
$$
    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;
$$
    LANGUAGE sql STABLE;

该方案侵入性最低，不需要保留PL/pgSQL的块结构，性能最优。

方案2：强制PL/pgSQL生成自定义执行计划 #

如果函数内存在其他必须用PL/pgSQL实现的逻辑（如条件判断、循环、中间变量计算），可以在函数内通过SET LOCAL临时修改计划缓存模式，强制每次调用根据传入的实际参数生成自定义计划，让FDW拿到具体参数值完成谓词下推。
改后示例：

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
    -- 仅在当前函数调用的事务上下文生效，不影响全局/会话配置
    SET LOCAL plan_cache_mode = 'force_custom_plan';
    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;

方案3：动态SQL执行查询 #

如果使用的是PostgreSQL 12以下的老版本，前两种方案不生效时，可以通过EXECUTE执行动态SQL，借助USING子句传参，让执行计划在运行时拿到实际参数值，完成条件下推。
改后示例：

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 EXECUTE
        '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 $1 and $2'
    USING time_a, time_b;
END
$$
    LANGUAGE plpgsql;

注意必须通过USING子句传参，不要直接拼接参数值到SQL字符串，避免SQL注入风险。

注意：无论采用哪种方案，都不要将函数稳定性设置为VOLATILE，否则优化器会判定函数存在副作用，不会主动将过滤条件下推到远程库执行。

修改完成后，在本地库执行EXPLAIN VERBOSE select * from public.testFDW('2022-05-01','2022-06-01')，如果返回的执行计划中Foreign Scan节点的Remote SQL字段包含对应的WHERE过滤条件，就说明下推配置生效。

内容的提问来源于stack exchange，提问作者Arseniy Mukhin