Join between foreign tables

[trourance]

Hi,
I'm using pg 9.5.7 and mysql_fdw 2.3.0.
When I try to join two foreign tables, it takes a very long time to execute because it doesn't use the indexes on the foreign tables columns.
For example, I run the following query:
SELECT DISTINCT(a.exa) as nb FROM table1 a WHERE site = 'ABC' AND date >= CURRENT_DATE - INTERVAL '30 DAY' AND NOT EXISTS (SELECT 1 FROM table2 b WHERE com = 1234 AND datetime >= CURRENT_DATE - INTERVAL '30 DAY' AND b.nb = a.exa) ORDER BY nb; Temps : 30334.196 ms

As you can see, it takes approximately 30 sec to run.
But if I add a join to get data from another table, I have to stop the query after 30 min, because I still don't get any result !
SELECT DISTINCT(a.exa) as nb, c.otherid FROM table1 a, table3 c WHERE a.col1 = c.col1 AND site = 'ABC' AND date >= CURRENT_DATE - INTERVAL '30 DAY' AND NOT EXISTS (SELECT 1 FROM table2 b WHERE com = 1234 AND datetime >= CURRENT_DATE - INTERVAL '30 DAY' AND b.nb = a.exa) ORDER BY nb

Both columns col1 on table a and c are indexed on mysql side.
| table3 | 0 | PRIMARY | 1 | col1 | A | 188428 | NULL | NULL | | BTREE | | |
| table1 | 1 | col1 | 1 | col1 | A | 369733 | NULL | NULL | | BTREE | | |

[trourance]

It seems that the same kind of issue is stucked since november 2015 (#72) !
Here's a simple test case with the explain verbose output:
explain verbose select * from tm_mmel tm, tm_patient tp where tm.patienti = tp.patienti limit 1;

-[ RECORD 1 ]-----------------------------------------------------------------------------------------------------
QUERY PLAN | Limit  (cost=50.00..52.01 rows=1 width=5114)
-[ RECORD 2 ]-----------------------------------------------------------------------------------------------------
QUERY PLAN |   Output: tm.eli, ...
-[ RECORD 3 ]-----------------------------------------------------------------------------------------------------
QUERY PLAN |   ->  Nested Loop  (cost=50.00..52.01 rows=1 width=5114)
-[ RECORD 4 ]-----------------------------------------------------------------------------------------------------
QUERY PLAN |         Output: tm.eli, ...
-[ RECORD 5 ]-----------------------------------------------------------------------------------------------------
QUERY PLAN |         Join Filter: (tm.patienti = tp.patienti)
-[ RECORD 6 ]-----------------------------------------------------------------------------------------------------
QUERY PLAN |         ->  Foreign Scan on myschema.tm_mmel tm  (cost=25.00..26.00 rows=1 width=4624)
-[ RECORD 7 ]-----------------------------------------------------------------------------------------------------
QUERY PLAN |               Output: tm.eli, ...
-[ RECORD 8 ]-----------------------------------------------------------------------------------------------------
QUERY PLAN |               Remote server startup cost: 25
-[ RECORD 9 ]-----------------------------------------------------------------------------------------------------
QUERY PLAN |               Remote query: SELECT `eli`, ...
-[ RECORD 10 ]----------------------------------------------------------------------------------------------------
QUERY PLAN |         ->  Foreign Scan on myschema.tm_patient tp  (cost=25.00..26.00 rows=1 width=490)
-[ RECORD 11 ]----------------------------------------------------------------------------------------------------
QUERY PLAN |               Output: tp.patienti, ...
-[ RECORD 12 ]----------------------------------------------------------------------------------------------------
QUERY PLAN |               Remote server startup cost: 25
-[ RECORD 13 ]----------------------------------------------------------------------------------------------------
QUERY PLAN |               Remote query: SELECT `patienti`, ...

Temps : 76859.054 ms

[eugeneYWang]

I am having a similar issue here. I guess when two tables join together, mysql_fdw is pulling two whole tables together?

Can anyone look at it please? Thanks

[ibrarahmad]

Join push-down is not implemented yet.

…

On Thu, Sep 27, 2018 at 12:00 AM Eugene Wang ***@***.***> wrote: I am having a similar issue here. I guess when two tables join together, mysql_fdw is pulling two whole tables together? Can anyone look at it please? Thanks — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#153 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ADbS8v5-20UevjgcD8W0J3pzlw0YSXLrks5ue860gaJpZM4QdcNi> .

-- Ibrar Ahmed

[eugeneYWang]

Join push-down is not implemented yet.
…

Thank you for answering. I suggested that Join push-down can be implemented only if source tables of two remote tables are in the same server.