Nested loop join condition does not get pushed down to foreign scan

From: Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>
To: "'pgsql-hackers(at)postgresql(dot)org'" <pgsql-hackers(at)postgresql(dot)org>
Subject: Nested loop join condition does not get pushed down to foreign scan
Date: 2016-09-13 10:35:01
Message-ID: A737B7A37273E048B164557ADEF4A58B5389E3C4@ntex2010i.host.magwien.gv.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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
-> 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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Ashutosh Bapat 2016-09-13 10:42:01 Re: Nested loop join condition does not get pushed down to foreign scan
Previous Message Kuntal Ghosh 2016-09-13 09:07:09 Re: WAL consistency check facility