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?
CodePudding user response:
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';