trourance
commented
6 years ago 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
ibrarahmad
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 msAs 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 nbBoth 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 | | |