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  (Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>)
Список 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 по дате отправления:

Предыдущее
От: Kuntal Ghosh
Дата:
Сообщение: Re: WAL consistency check facility
Следующее
От: Ashutosh Bapat
Дата:
Сообщение: Re: Nested loop join condition does not get pushed down to foreign scan