[Feature request] Support Subquery pushdown

[smilingthax]

Motivation

Examples:

1. Simple case:

   SELECT 
   id,
   (SELECT COUNT(*) FROM tbl2 WHERE tbl2.parent_id = tbl1.id) 
   FROM tbl1;  -- + WHERE, ...

   The query plain currently looks somewhat like this:

   Foreign Scan on tbl1
   Output: tbl1.id, (SubPlan 1)
   Remote query: SELECT `id` FROM `s`.`tbl1`   -- + WHERE, ORDER BY, ...
   SubPlan 1
    ->  Foreign Scan
          Output: (count(tbl2.parent_id))
          Relations: Aggregate on (s.tbl2)
          Remote query: SELECT count(`id`) FROM `s`.`tbl2` WHERE ((`parent_id` = ?))

   I.e. for each row of tbl1 a separate query is send to the remote database.

2. Similar case (worse performance):

   SELECT 
   id,
   EXISTS (SELECT FROM tbl2 WHERE tbl2.parent_id = tbl1.id) 
   FROM tbl1;

   Query Plan:

   Foreign Scan on tbl1
   Output: tbl1.id, (hashed SubPlan 2)
   Remote query: SELECT `id` FROM `s`.`tbl1`   -- + WHERE, ORDER BY, ...
   SubPlan 2
    ->  Foreign Scan on s.tbl2
          Output: tbl2.parent_id
          Remote query: SELECT `parent_id` FROM `s`.`tbl2`

   This unfortunately tries to download the complete tbl2!

3. Another version (might be more difficult to implement?):

   SELECT 
   id,
   r1.*
   FROM tbl1,
   LATERAL (SELECT COUNT(*) FROM tbl2 WHERE tbl2.parent_id = tbl1.id) r1;

   Query Plan:

   Nested Loop
   Output: tbl1.id, (count(tbl2.parent_id))
   ->  Foreign Scan on s.tbl1
        Output: tbl1.id, ... more fields...
        Remote query: SELECT `id` FROM `s`.`tbl1`   -- + WHERE, ORDER BY, ...
   ->  Foreign Scan
        Output: (count(*))
        Relations: Aggregate on ("s".tbl2)
        Remote query: SELECT count(*) FROM `s`.`tbl2` WHERE ((`parent_id` = ?))

   This is basically the same as 1.

Partial workaround:

SELECT
  tbl1.id,
  COUNT(tbl2.id)
FROM tbl1
LEFT JOIN tbl2 ON tbl2.parent_id = tbl1.id
GROUP BY 1

Query Plan: Single Foreign Scan, everything is pushed down

However, this no longer works when additional fields, e.g. tbl1.*, should be retrieved.

One might try this:

WITH t0 AS ( -- NOT MATERIALIZED does not help
  SELECT
    tbl1.id,
    COUNT(tbl2.id)
  FROM table
  LEFT JOIN tbl2 ON tbl2.parent_id = tbl1.id
  GROUP BY 1
  ORDER BY 1
)
SELECT
  tbl1.*,
  count
FROM t0
JOIN tbl1 ON tbl1.id = t0.id

But the resulting query plan (Merge Join between Foreign Scan of tbl1 and Materialize of Foreign Scan from t0) is not feasible for bigger tables, although SELECT * FROM t0 and SELECT * FROM tbl1 are quite fast by themselves...

Expected Result

The aggregation is evaluated on the remote mysql server without requiring a separate query for each row of tbl1.
Also, EXISTS should probably keep the WHERE, instead of downloading the whole table.