Nested loop join condition does not get pushed down to foreign scan
От | Albe Laurenz |
---|---|
Тема | Nested loop join condition does not get pushed down to foreign scan |
Дата | |
Msg-id | A737B7A37273E048B164557ADEF4A58B5389E3C4@ntex2010i.host.magwien.gv.at обсуждение исходный текст |
Ответы |
Re: Nested loop join condition does not get pushed down to
foreign scan
|
Список | pgsql-hackers |
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
В списке pgsql-hackers по дате отправления: