I just noticed something surprising:
-- create a larger local table
CREATE TABLE llarge (id integer NOT NULL, val integer NOT NULL);
INSERT INTO llarge SELECT i, i%100 FROM generate_series(1, 10000) i;
ALTER TABLE llarge ADD PRIMARY KEY (id);
-- create a small local table
CREATE TABLE small (id integer PRIMARY KEY, val text NOT NULL);
INSERT INTO small VALUES (1, 'one');
-- create a foreign table based on llarge
CREATE EXTENSION postgres_fdw;
CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', port '5432', dbname 'test');
CREATE USER MAPPING FOR myself SERVER loopback OPTIONS (user 'myself', password 'mypassword');
CREATE FOREIGN TABLE rlarge (id integer NOT NULL, val integer NOT NULL) SERVER loopback OPTIONS (table_name 'llarge');
SET enable_hashjoin = off;
-- plan for a nested loop join with a local table
EXPLAIN (COSTS off) SELECT * FROM small JOIN llarge USING (id); QUERY PLAN
----------------------------------------------Nested Loop -> Seq Scan on small -> Index Scan using llarge_pkey on
llarge Index Cond: (id = small.id)
(4 rows)
-- plan for a nested loop join with a foreign table
EXPLAIN (COSTS off) SELECT * FROM small JOIN rlarge USING (id); QUERY PLAN
---------------------------------------Nested Loop Join Filter: (small.id = rlarge.id) -> Seq Scan on small ->
ForeignScan on rlarge
(4 rows)
Is there a fundamental reason why the join condition does not get pushed down into
the foreign scan or is that an omission that can easily be fixed?
Yours,
Laurenz Albe
