Example: Join pushdown v2

Suggest edits

This example shows join pushdown 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);
Output
                                                                             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., r2. FROM (. r1 INNER JOIN . r2 ON (((r1. = r2.))))
(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);
Output
                                                                             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., r2. FROM (. r1 LEFT JOIN . r2 ON (((r1. = r2.))))
(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);
Output
                                                                             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., r2. FROM (. r2 LEFT JOIN . r1 ON (((r1. = r2.))))
(5 rows)
--cross join
edb=# EXPLAIN VERBOSE SELECT t1.warehouse_name, t2.qty FROM warehouse t1 CROSS JOIN
sales_records t2;
Output
                                                           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., r2. FROM (. r1 INNER JOIN . r2 ON (TRUE))
(5 rows)