Example: Join pushdown v2

This example shows join pushdown between the foreign tables of the same remote HIVE/SPARK server to that remote HIVE/SPARK server:

Tables on HIVE/SPARK server:

0: jdbc:hive2://localhost:10000> describe emp;
+-----------+------------+----------+--+
| col_name  | data_type  | comment  |
+-----------+------------+----------+--+
| empno     | int        | NULL     |
| ename     | string     | NULL     |
| job       | string     | NULL     |
| mgr       | int        | NULL     |
| hiredate  | date       | NULL     |
| sal       | int        | NULL     |
| comm      | int        | NULL     |
| deptno    | int        | NULL     |
+-----------+------------+----------+--+
8 rows selected (0.747 seconds)
0: jdbc:hive2://localhost:10000> describe dept;
+-----------+------------+----------+--+
| col_name  | data_type  | comment  |
+-----------+------------+----------+--+
| deptno    | int        | NULL     |
| dname     | string     | NULL     |
| loc       | string     | NULL     |
+-----------+------------+----------+--+
3 rows selected (0.067 seconds)

Tables on Postgres server:

CREATE EXTENSION hdfs_fdw;
CREATE SERVER hdfs_server FOREIGN DATA WRAPPER hdfs_fdw OPTIONS(host 'localhost', port '10000', client_type 'spark', auth_type 'LDAP');
CREATE USER MAPPING FOR public SERVER hdfs_server OPTIONS (username 'user1', password 'pwd123');

CREATE FOREIGN TABLE dept (
    deptno          INTEGER,
    dname           VARCHAR(14),
    loc             VARCHAR(13)
)
SERVER hdfs_server OPTIONS (dbname 'fdw_db', table_name 'dept');

CREATE FOREIGN TABLE emp (
    empno           INTEGER,
    ename           VARCHAR(10),
    job             VARCHAR(9),
    mgr             INTEGER,
    hiredate        DATE,
    sal             INTEGER,
    comm            INTEGER,
    deptno          INTEGER
)
SERVER hdfs_server OPTIONS (dbname 'fdw_db', table_name 'emp');

Queries with join pushdown:

--inner join
edb=# EXPLAIN VERBOSE SELECT t1.ename, t2.dname FROM emp t1 INNER JOIN dept t2 ON ( t1.deptno = t2.deptno );
                                                              QUERY PLAN                                                               
---------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan  (cost=15.00..35.00 rows=5000 width=84)
   Output: t1.ename, t2.dname
   Relations: (fdw_db.emp t1) INNER JOIN (fdw_db.dept t2)
   Remote SQL: SELECT r1.`ename`, r2.`dname` FROM (`fdw_db`.`emp` r1 INNER JOIN `fdw_db`.`dept` r2 ON (((r1.`deptno` = r2.`deptno`))))
(4 rows)

--left join
edb=# EXPLAIN VERBOSE SELECT t1.ename, t2.dname FROM emp t1 LEFT JOIN dept t2 ON ( t1.deptno = t2.deptno );
                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan  (cost=15.00..35.00 rows=5000 width=84)
   Output: t1.ename, t2.dname
   Relations: (fdw_db.emp t1) LEFT JOIN (fdw_db.dept t2)
   Remote SQL: SELECT r1.`ename`, r2.`dname` FROM (`fdw_db`.`emp` r1 LEFT JOIN `fdw_db`.`dept` r2 ON (((r1.`deptno` = r2.`deptno`))))
(4 rows)

--right join
edb=# EXPLAIN VERBOSE SELECT t1.ename, t2.dname FROM emp t1 RIGHT JOIN dept t2 ON ( t1.deptno = t2.deptno );
                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan  (cost=15.00..35.00 rows=5000 width=84)
   Output: t1.ename, t2.dname
   Relations: (fdw_db.dept t2) LEFT JOIN (fdw_db.emp t1)
   Remote SQL: SELECT r1.`ename`, r2.`dname` FROM (`fdw_db`.`dept` r2 LEFT JOIN `fdw_db`.`emp` r1 ON (((r1.`deptno` = r2.`deptno`))))
(4 rows)

--full join
edb=# EXPLAIN VERBOSE SELECT t1.ename, t2.dname FROM emp t1 FULL JOIN dept t2 ON ( t1.deptno = t2.deptno );
                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan  (cost=15.00..35.00 rows=5000 width=84)
   Output: t1.ename, t2.dname
   Relations: (fdw_db.emp t1) FULL JOIN (fdw_db.dept t2)
   Remote SQL: SELECT r1.`ename`, r2.`dname` FROM (`fdw_db`.`emp` r1 FULL JOIN `fdw_db`.`dept` r2 ON (((r1.`deptno` = r2.`deptno`))))
(4 rows)

--cross join
edb=# EXPLAIN VERBOSE SELECT t1.ename, t2.dname FROM emp t1 CROSS JOIN dept t2;
                                                  QUERY PLAN                                                  
--------------------------------------------------------------------------------------------------------------
 Foreign Scan  (cost=15.00..35.00 rows=1000000 width=84)
   Output: t1.ename, t2.dname
   Relations: (fdw_db.emp t1) INNER JOIN (fdw_db.dept t2)
   Remote SQL: SELECT r1.`ename`, r2.`dname` FROM (`fdw_db`.`emp` r1 INNER JOIN `fdw_db`.`dept` r2 ON (TRUE))
(4 rows)