postgres_fdw.sql

\pset pager off

DROP DATABASE IF EXISTS db_remote;
CREATE DATABASE db_remote;
\c db_remote

CREATE TABLE a AS(
  SELECT
      x::INT AS id_a
    , (x % 100)::INT AS id_b
  FROM generate_series(1, 1000000) x
);

ALTER TABLE a ADD PRIMARY KEY(id_a);
ANALYZE a;


DROP DATABASE IF EXISTS db_local;
CREATE DATABASE db_local;
\c db_local

CREATE EXTENSION postgres_fdw;

CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS(host '10.0.200.10', port '5432', dbname 'db_remote');

CREATE USER MAPPING FOR postgres
SERVER foreign_server
OPTIONS(user 'postgres');

CREATE FOREIGN TABLE a_to_remote(
    id_a INT
  , id_b INT
) SERVER foreign_server OPTIONS(
    schema_name 'public'
  , table_name 'a'
  , fetch_size '1000'
);

ANALYZE a_to_remote;

CREATE VIEW va AS SELECT * FROM a_to_remote;

EXPLAIN (ANALYZE, VERBOSE)
SELECT *
FROM va
WHERE id_b = 1;

EXPLAIN (ANALYZE, VERBOSE)
SELECT *
FROM va
WHERE id_a IN (
  SELECT id_a
  FROM a_to_remote
  WHERE id_b = 1
);

EXPLAIN (ANALYZE, VERBOSE)
SELECT *
FROM va
WHERE id_a IN (
  SELECT id_a
  FROM a_to_remote
  WHERE id_b = 1
  LIMIT 1000
);