MongoDB Foreign Data Wrapper supports pushdown for inner joins, left joins, and right joins. For example:
Postgres data set:
-- load extension first time after install CREATE EXTENSION mongo_fdw; -- create server object CREATE SERVER mongo_server FOREIGN DATA WRAPPER mongo_fdw OPTIONS (address 'localhost', port '27017'); -- create user mapping CREATE USER MAPPING FOR public SERVER mongo_server OPTIONS (username 'edb', password 'edb'); -- create foreign table CREATE FOREIGN TABLE emp (_id NAME, eid INTEGER, ename TEXT, deptid INTEGER) SERVER mongo_server OPTIONS (database 'edb', collection 'emp'); -- insert into table INSERT INTO emp VALUES (0, 100, 'John', 10); INSERT INTO emp VALUES (0, 110, 'Mark', 10); INSERT INTO emp VALUES (0, 120, 'Smith', 20); INSERT INTO emp VALUES (0, 130, 'Ed', 30); -- create foreign table CREATE FOREIGN TABLE dept (_id NAME, deptid INTEGER, dname TEXT) SERVER mongo_server OPTIONS (database 'edb', collection 'dept'); -- insert into table INSERT INTO dept VALUES (0, 10, 'SALES'); INSERT INTO dept VALUES (0, 20, 'ENGG'); INSERT INTO dept VALUES (0, 30, 'IT');
Enable/disable GUC for JOIN pushdown queries at the session level, table level, or server level:
-- Session level edb=# SET mongo_fdw.enable_join_pushdown to true; SET -- Table level edb=# ALTER FOREIGN TABLE emp OPTIONS (ADD enable_join_pushdown 'true'); Table altered -- Server level edb=# ALTER SERVER mongo_server OPTIONS (ADD enable_join_pushdown 'true'); altered
Query with JOIN pushdown:
--inner join edb=# EXPLAIN VERBOSE SELECT e.ename, d.dname FROM emp e INNER JOIN dept d ON (e.deptid = d.deptid);
QUERY PLAN ---------------------------------------------------------- Foreign Scan (cost=15.00..35.00 rows=5000 width=64) Output: e.ename, d.dname Foreign Namespace: (edb.emp e) INNER JOIN (edb.dept d) (3 rows)
--left join edb=# EXPLAIN VERBOSE SELECT e.ename, d.dname FROM emp e LEFT JOIN dept d ON (e.deptid = d.deptid);
QUERY PLAN --------------------------------------------------------- Foreign Scan (cost=15.00..35.00 rows=5000 width=64) Output: e.ename, d.dname Foreign Namespace: (edb.emp e) LEFT JOIN (edb.dept d) (3 rows)
--right join edb=# EXPLAIN VERBOSE SELECT e.ename, d.dname FROM emp e RIGHT JOIN dept d ON (e.deptid = d.deptid);
QUERY PLAN --------------------------------------------------------- Foreign Scan (cost=15.00..35.00 rows=5000 width=64) Output: e.ename, d.dname Foreign Namespace: (edb.dept d) LEFT JOIN (edb.emp e) (3 rows)
Example: End-to-end
MongoDB Foreign Data Wrapper
Example: Aggregate function pushdown