Is it possible to speed up JOINs between (large r/o) Oracle table and small PG one? about oracle_fdw HOT 3 CLOSED

I cannot think of any way to improve oracle_fdw so that it handles that more efficiently.

But why do you need to create a CSV file? I would write a PL/pgSQL function that first queries the Oracle table for the list of IDs, and store that in a temporary table or an array. Then the function eliminates the IDs you don't want, and finally it constructs a dynamic SQL statement with the literal numbers in it.

Something like

CREATE FUNCTION xyz() RETURNS SETOF foreign_table_b
  LANGUAGE plpgsql AS
$$DECLARE
   ids bigint[];
BEGIN
   SELECT array_agg(a.id) INTO ids FROM foreign_table_a AS a
   WHERE /* condition */
   AND NOT EXISTS (SELECT FROM local_table AS l
                   WHERE l.id = a.id);

   RETURN QUERY EXECUTE
      format(
         'SELECT FROM foreign_table_b WHERE id = ANY (%s)', ids;
END;$$;

from oracle_fdw.

Can we close the issue?

from oracle_fdw.

Can we close the issue?

Sure. I'll see if I can work with the function, thanks a lot for considering the case and sketching the idea!

(as regards export to csv it is just that the whole queries are part of task flow app and I might need to rerun tasks from previous results).

from oracle_fdw.