Example: Join Push-down v2.6.0

Edit this page

The following example shows join push-down between two foreign tables 'warehouse' and 'sales_records':

Table on MySQL server:

CREATE TABLE warehouse
(
warehouse_id      INT PRIMARY KEY,
warehouse_name    TEXT,
warehouse_created TIMESTAMP
);

CREATE TABLE sales_records
(
warehouse_id      INT PRIMARY KEY,
qty           INT
);

Table on Postgres server:

CREATE EXTENSION mysql_fdw;
CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host '127.0.0.1', port '3306');
CREATE USER MAPPING FOR public SERVER mysql_server OPTIONS (username 'edb', password 'edb');

CREATE FOREIGN TABLE warehouse
(
warehouse_id      INT,
warehouse_name    TEXT,
warehouse_created TIMESTAMP
)
SERVER mysql_server OPTIONS (dbname 'edb', table_name 'warehouse');
INSERT INTO warehouse values (1, 'UPS', current_date);
INSERT INTO warehouse values (2, 'TV', current_date);
INSERT INTO warehouse values (3, 'Table', current_date);

CREATE FOREIGN TABLE sales_records
(
warehouse_id      INT,
qty           INT
)
SERVER mysql_server OPTIONS (dbname 'edb', table_name 'sales_records');
INSERT INTO sales_records values (1, 100);
INSERT INTO sales_records values (2, 75);
INSERT INTO sales_records values (3, 200);

The output:

--inner join
edb=# EXPLAIN VERBOSE SELECT t1.warehouse_name, t2.qty FROM warehouse t1 INNER JOIN sales_records t2 ON (t1.warehouse_id = t2.warehouse_id);
                                                                             QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan  (cost=15.00..35.00 rows=5000 width=36)
   Output: t1.warehouse_name, t2.qty
   Relations: (edb.warehouse t1) INNER JOIN (edb.sales_records t2)
   Local server startup cost: 10
   Remote query: SELECT r1.`warehouse_name`, r2.`qty` FROM (`edb`.`warehouse` r1 INNER JOIN `edb`.`sales_records` r2 ON (((r1.`warehouse_id` = r2.`warehouse_id`))))
(5 rows)

--left join
edb=# EXPLAIN VERBOSE SELECT t1.warehouse_name, t2.qty FROM warehouse t1 LEFT JOIN sales_records t2 ON (t1.warehouse_id = t2.warehouse_id);
                                                                             QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan  (cost=15.00..35.00 rows=5000 width=36)
   Output: t1.warehouse_name, t2.qty
   Relations: (edb.warehouse t1) LEFT JOIN (edb.sales_records t2)
   Local server startup cost: 10
   Remote query: SELECT r1.`warehouse_name`, r2.`qty` FROM (`edb`.`warehouse` r1 LEFT JOIN `edb`.`sales_records` r2 ON (((r1.`warehouse_id` = r2.`warehouse_id`))))
(5 rows)

--right join
edb=# EXPLAIN VERBOSE SELECT t1.warehouse_name, t2.qty FROM warehouse t1 RIGHT JOIN sales_records t2 ON (t1.warehouse_id = t2.warehouse_id);
                                                                             QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan  (cost=15.00..35.00 rows=5000 width=36)
   Output: t1.warehouse_name, t2.qty
   Relations: (edb.sales_records t2) LEFT JOIN (edb.warehouse t1)
   Local server startup cost: 10
   Remote query: SELECT r1.`warehouse_name`, r2.`qty` FROM (`edb`.`sales_records` r2 LEFT JOIN `edb`.`warehouse` r1 ON (((r1.`warehouse_id` = r2.`warehouse_id`))))
(5 rows)

--cross join
edb=# EXPLAIN VERBOSE SELECT t1.warehouse_name, t2.qty FROM warehouse t1 CROSS JOIN
sales_records t2;
                                                           QUERY PLAN                                                           
--------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan  (cost=15.00..35.00 rows=1000000 width=36)
   Output: t1.warehouse_name, t2.qty
   Relations: (edb.warehouse t1) INNER JOIN (edb.sales_records t2)
   Local server startup cost: 10
   Remote query: SELECT r1.`warehouse_name`, r2.`qty` FROM (`edb`.`warehouse` r1 INNER JOIN `edb`.`sales_records` r2 ON (TRUE))
(5 rows)