Example: Aggregate pushdown v2

Suggest edits

MySQL Foreign Data Wrapper supports pushdown for the following aggregate functions:

  • AVG - Calculates the average of a set of values.
  • COUNT - Counts rows in a specified table or view.
  • MIN - Gets the minimum value in a set of values.
  • MAX - Gets the maximum value in a set of values.
  • SUM - Calculates the sum of values.

Table on MySQL server:

CREATE FOREIGN 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 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:

edb=# EXPLAIN VERBOSE SELECT avg(qty) FROM sales_records;
Output
QUERY PLAN                          
--------------------------------------------------------------
 Foreign Scan  (cost=15.00..25.00 rows=1 width=32)
   Output: (avg(qty))
   Relations: Aggregate on (edb.sales_records)
   Local server startup cost: 10
   Remote query: SELECT avg() FROM .
(5 rows)
edb=# EXPLAIN VERBOSE SELECT COUNT(qty) FROM sales_records;
Output
QUERY PLAN                           
----------------------------------------------------------------
 Foreign Scan  (cost=15.00..25.00 rows=1 width=8)
   Output: (count(qty))
   Relations: Aggregate on (edb.sales_records)
   Local server startup cost: 10
   Remote query: SELECT count() FROM .
(5 rows)
edb=# EXPLAIN VERBOSE SELECT MIN(qty) FROM sales_records;
Output
QUERY PLAN                          
--------------------------------------------------------------
 Foreign Scan  (cost=15.00..25.00 rows=1 width=4)
   Output: (min(qty))
   Relations: Aggregate on (edb.sales_records)
   Local server startup cost: 10
   Remote query: SELECT min() FROM .
(5 rows)
edb=# EXPLAIN VERBOSE SELECT MAX(qty) FROM sales_records;
Output
QUERY PLAN                          
--------------------------------------------------------------
 Foreign Scan  (cost=15.00..25.00 rows=1 width=4)
   Output: (max(qty))
   Relations: Aggregate on (edb.sales_records)
   Local server startup cost: 10
   Remote query: SELECT max() FROM .
(5 rows)
edb=# EXPLAIN VERBOSE SELECT SUM(qty) FROM sales_records;
Output
QUERY PLAN                          
--------------------------------------------------------------
 Foreign Scan  (cost=15.00..25.00 rows=1 width=8)
   Output: (sum(qty))
   Relations: Aggregate on (edb.sales_records)
   Local server startup cost: 10
   Remote query: SELECT sum() FROM .
(5 rows)
edb=# EXPLAIN VERBOSE SELECT SUM(qty) FROM sales_records GROUP BY warehouse_id HAVING SUM(qty) = 75;
Output
QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 Foreign Scan  (cost=15.00..25.00 rows=200 width=12)
   Output: (sum(qty)), warehouse_id
   Relations: Aggregate on (edb.sales_records)
   Local server startup cost: 10
   Remote query: SELECT sum(),  FROM . GROUP BY 2 HAVING ((sum() = 75))
(5 rows)