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
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 |