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
-> Foreign Scan 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
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
